Boston College Budgeting and Forecasting Worksheet The Company I need to write about is the AT&T11111111111111111111111111111111111111111111 Ques Budgeting

Boston College Budgeting and Forecasting Worksheet The Company I need to write about is the AT&T11111111111111111111111111111111111111111111 Ques
Budgeting and Forecasting
Scenario
You are graduating from Hult and you have an interview with
1
Office Interview at
FP&A vs. other dept
2
3
4
5
0
0
6
7
8
11
12
Revenues
COGS
Gross Profit
SG&A
Net Income
13
1,200.00
500.00
700.00
300.00
1,000.00
Gross Profit
SG&A expenses
Operating Income
#
18,000.00
16,000.00
2,000.00
Units Sold
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
5,530
5,918
3,407
7,596
36,521
587
3,128
12,879
3,927
49521
3,979
22,462
6,314
3,244
15,284
5,614
25,964
5,547
2,059
1,653
681
75,884
384
55,821
3,699
1,102
8,962
870
290
11,526
21
22
23
24
25
dollars in millions
Cash
Marketable sec
Accounts receivable
Inventories
Prepaid expenses
Total Current Assets
Net fixed assets
Goodwill
Total Assets
Accounts payable and accrued
Short term loans
Total current liabilities
Bonds payable
Other operating non-current
total long term liabilities
total liabilities
Actual
current
20.0
125.0
350.0
170.0
37.0
702.0
500.0
90.0
1,292.0
165.0
25.0
190.0
200.0
30.0
230.0
420.0
3. Forecasted Financial Statements
Forecast
1
2
22.0
23.5
150.0
200.0
385.0
412.0
187.0
200.1
44.0
47.1
788.0
882.7
550.0
588.5
99.0
105.9
1,437.0
1,577.1
176.0
25.0
201.0
200.0
88.0
288.0
489.0
188.3
25.0
213.3
200.0
94.2
294.2
507.5
Common stock (300mil
Retained Earnings
Total shareholders’ equity
Total liabilities and equity
NOPAT
26
27
4.0
50.0
822.0
872.0
1,292
50.0
898.0
948.0
1,437
679
50.0
1,019.6
1,069.6
1,577
778
Fill out the areas in buff/beige. Do not use other
cells, we may miss it in grading!
Your name:
Your Company
Ques
Pts
1
2 “Thank you for coming In to meet with us today. What is the difference
between Financial Planning and Analysis and on of our other departments:
2
2 Why does a strong FP&A professional need to know more than finance and accounting
3
2 FP&A professionals may be assigned to work with line managers. Why would you
be able to help a line manager of a functional areas , for example:
4
2 You may be asked to assist our Corporate Development in their investigation, negotiat
and integration plans for an acquisition. What are examples of what value you can add
5 2.5 We believe in setting SMART goals to support key goals and objectives for the compan
Our board and senior management have just determined that for
we need to provide some SMART goals shared with inside and outside people to
Give an example of a SMART goal and how it qualifies under S, M, A, R, and T
6
2 In addition to technical financial skills, our FP&A staff need to be well versed in the are
of project management and leadership. Explain the value of the
for effective project management
7
3 We set goals, create measures, track actual results, then do “after action” reports
and meetings. If you were in charge of one such meeting with the key participants,
what would be three topics for your discussion?
8
3 Every company has its own unique portfolio of business. Pick one of the major busines
for our company and first describe if it is a cash cow, star, question mark or dog, and w
Then give a plausible strategy given that business unit’s position and your overall
assessment of the corporate portfolio.
9
What is the difference between financial and managerial accounting. Give an example
2 would see in managerial accounting that would not be seen in financial reports.
10 7.5 Fill in the information for your selected company below. Then compute and interpret
ratios for the most recent 2 years
Total Revenues (aka Sales)
Operating income
Net Income
Accounts receivable
Inventory
Total assets
Total shareholders’ equity
Operating Margin = operating income/total revenues
Your interpretation
Return on Assets = net income/sales
Your interpretation
Days Sales Outstanding = ( Accounts Receivable)/(Sales/365)
Your interpretation
11
12
13
2 During the construction of the model, there are inevitable variables that are not certai
vague. Describe what you can do when you build your model and as you proceed with
finalizing and presenting your model to senior management. For full credit, specify tw
after you make your assumption
Excel Hygiene – Review the Excel Worksheet on the left with errors and violations of “b
4 a corrected Excel sheet below
4 Sensitivity analysis
Use the model (on the left), and create a sensitivity analysis based on the gross margi
Copy the model to the area below, then calculate and present your sensitivity analysis
14
10 Refer to the data for daily units sold on the left. Compute the indicated statistical mea
Mean
Median
Standard deviation
number of days corresponding to 80% of sales
Total sales for the 80%
Percentage of days to reach 80%
Does this correspond to the “80/20″ rule?
15
3 Our company is striving for excellence throughout our company. I would expect you to
in our training, measurements, and implementation of the technique
What is this and what benefits should we see? Provide one metric than we can use as
and after” so we can demonstrate the improvements.
16
5 Our human resources department is currently allocating based on headcount.
Most of the costs of the department are the people in HR, who support all department
We asked HR for some measurements based on their tracking for a month. These are
What do you think of the allocation method used now? Suggest an alternative and wh
total cost of HR
Ops
Sales
Mktg
Admin
headcount payroll processed by department
2,000
170,000,000
150
18,000,000
10
110,000
50
4,500,000
comment on current method
Alternative
why this is better
17
3 Everyone in our sales department has difficulty estimating sales, we did some analysis
then asked the sales managers for opinions. Here is an example of our approach.
Do you analysis and tell me what you expect sales will be this year.
Sales
Base
900
High
1200
Low
400
18
5 We have a traditional reporting process. I hear you are an expert in On Demand Busin
Can you compare the Traditional with DBO?
Traditional
Budget vs Actual
Reporting – frequency
Re-forecasting
How many months
ahead do you see, as
the year progresses
Advantages
Disadvantages
19
4 I volunteer as a board member for the Albany School District. Look at the reporting lin
the Director of Student Services, Carrie Nerheim. Describe the idea of budgeting levels
to prepare a budget, who approves. And once the year begins, who gets monthly repo
responsible for explaining variances. For full credit explain Carrie’s line below her and
no any other parts of the organization!
20
3 When we meet with Carrie, we should explain incremental budgeting and zero based b
How would you describe those two approaches? Explain which she should use and wh
21
4 One of our main contributions is our work on capital investments. Analyze the project
compute the NPV, IRR, PI and Payback for both. The required rate of return is 15% for
HINT: analyze A first, then you may be able to copy and paste to get almost all of B do
0
1
2
Project A
-200
100
80
-200
10
20
NPV
IRR
PI
Payback
Project B
NPV
IRR
PI
Payback
22
3 Give the pros and cons of Project A and B. Which would you recommend and why?
Pros
Project A
Project B
Recommendation and why:
Forecast
1,708.9
4
26.0
400.0
428.2
194.6
51.9
1,100.7
622.9
116.8
1,840.4
197.7
25.0
222.7
200.0
98.9
298.9
521.6
207.6
25.0
232.6
200.0
103.8
303.8
536.4
3
24.7
300.0
420.2
197.7
49.4
992.1
605.6
111.2
23
2 In a different set of assessments, on Project C and D, you find that Project C is riskier t
how can you modify your approach analytically?
24
2 Describe how you can use Real Options to improve the expected returns and manage r
in a project.
25
10 Use the information to the left and determine the value of equity and the price per sha
additional information:
cost of capital
12%
long term growth%
3%
tax rate =
40%
shares outstanding
300
50.0
1,137.3
1,187.3
1,709
865
50.0
1,253.9
1,303.9
1,840
961
26
4 Integrated Reporting
a) How does it differ from traditional financial reporting (10-K, annual reports)?
b) What are the 6 capitals?
c) Who is the audience for this reporting?
d) Why are reasons it has not been more widely adopted?
27
4 For your company, define 8 KPIs that are in the spirit of Balanced Scorecard, Net Impac
You answer will be graded based on its breadth (goals measured) and depth (quality of
KPI
how would you determine this
100 total points possible
Do not use other
at is the difference
r other departments:
Controller
more than finance and accounting?
managers. Why would you
or example:
Sales
nt in their investigation, negotiation
amples of what value you can add?
ls and objectives for the company
0
side and outside people to increase sales
under S, M, A, R, and T
need to be well versed in the area
GANTT chart
en do “after action” reports
ting with the key participants,
ss. Pick one of the major business units
tar, question mark or dog, and why.
‘s position and your overall
rial accounting. Give an example of something we
e seen in financial reports.
w. Then compute and interpret the specified
current yr
prior yr
able variables that are not certain and
r model and as you proceed with your
ement. For full credit, specify two actions
ft with errors and violations of “best practices” and put
nalysis based on the gross margin
present your sensitivity analysis (+/- 30%)
pute the indicated statistical measures
company. I would expect you to help
Six Sigma
e one metric than we can use as a: before
ng based on headcount.
HR, who support all departments and people.
tracking for a month. These are their results
w? Suggest an alternative and why it is better
5,325,841
Hrs from timesheets
280.00
247.00
196.00
77.00
ating sales, we did some analysis,
n example of our approach.
be this year.
% probability
50.0%
30.0%
20.0%
e an expert in On Demand Business Outlooks.
District. Look at the reporting line for
cribe the idea of budgeting levels – i.e. who has
ar begins, who gets monthly reports and who is
plain Carrie’s line below her and who she reports to,
DBO
ental budgeting and zero based budgeting.
ain which she should use and why.
nvestments. Analyze the projects below and
equired rate of return is 15% for each project.
nd paste to get almost all of B done.
3
4
5
65
29
30
40
100
232
uld you recommend and why?
Cons
you find that Project C is riskier than Project D
e expected returns and manage risk
ue of equity and the price per share (caution: this is a time consuming and challenging question!)
ng (10-K, annual reports)?
of Balanced Scorecard, Net Impact, and/or Integrated Reporting
measured) and depth (quality of the KPI).
Goal/Objective Measured
Category (Bal. Scorecard, 3Ps, IR)

Purchase answer to see full
attachment