Assessment (1 sheet)
Budgeting and Forecasting Boston Fill out the areas in buff
eige. Do not use other cells, we may miss it in grading!
Summer 2021 Your name:
Prof. La
y Louie
The Summative Assessment is based on Air Liquide. The questions in this assessment are partially based on facts, and partially fictitious. Its purpose is to evaluate your understanding
of the course topics.
Score Q# Pts
1 2 You have joined the FP&A department of Air Liquide. The CFO in 2020 was Fabienne Lecorvaisier. She has since moved to take an Executive post for the Company in Sustainability.
She is looking to add you to his already strong FP&A team. This is your interview with the cu
ent FP&A team. The first question is "why is knowledge of managerial accounting as important as financial accounting for an FP&A analyst? To receive full credit, give one example of managerial accounting that is different than financial accounting that is important for FP&A.
Financial accounting needs to be supported by managerial acouting. Since financcial acounting relates important informations for stakeholders, it doesn't show the company aciheved the outcome. Management acounting can review and take actions to achievethe final results. Lead measures are taking in consideration in management whereas financial accounting just focus on lag measures. For example, a financial report can indicates that DSO have increased . Management tell us what where the causes of this increase. Managerial accouting take co
ecive actions to avchieve the best result.
2 2 The accounting department and the FP&A departments are both very busy with monthly reporting. Contrast the outcomes of the work for the accounting department compared to the work by FP&A.
To receive full credit, provide specific examples.
Managers can take information about the performance from reports. The accounting department is in charge of managerial accounting reports . Additionally the company can forecasts for the future; Sales and revenue are included in the reports. E.G Budget forecasting and variance explanations, inventory and manufacturing .FP&A reports shows the historical accounts and financial to the users. The report includes the following sections. income statement, cash flow statements for e.g
3 4 Look at the 2020 Universal Registration Document (refe
ed to as the "Annual Report" for the rest of this Summative Assessment), pages 17 and 18. Pick two of the items and explain their actions based on the Growth-Share Model. Be specific on the Air Liquid move and the rationale in Growth Share terminology (i.e. the associated quadrants).
Note: See the actual Annual Report to read the two pages (17 and 18)
2016 2017 2018 2019 2020
Revenues 18,134.8 20,349.3 21,011.1 21,920.1 20,485.2
Purchases (6,692.8) (7,720.8) (8,276.4) (8,153.9) (7,197.7)
Personnel (3,659.4) (4,138.3) (4,145.8) (4,410.9) (4,239.8)
Other (3,171.4) (3,348.5) (3,374.1) (3,423.8) (3,120.2)
Depreciation and amortization (1,587.3) (1,777.9) (1,766.3) (2,137.7) (2,137.9)
Operating Income Recu
ing 3,023.9 3,363.8 3,448.5 3,793.8 3,789.6
Non-recu
ing income (expenses) 35.6 (343.5) (161.8) (187.5) (139.5)
Earnings before interest and taxes 3,059.5 3,020.3 3,286.7 3,606.3 3,650.1
Net finance cost (interest) (389.1) (421.9) (303.4) (361.6) (352.8)
Other financial expenses (14.0) (67.5) (49.3) (106.1) (87.1)
Income taxes (747.4) (207.3) (730.7) (801.7) (678.2)
Other (profit from associates, minority interest and discontinued operations) (65.0) (124.0) (89.9) (95.4) (96.9)
Net Income 1,844.0 2,199.6 2,113.4 2,241.5 2,435.1
- 0
Balance Sheet (note: this is converted to US GAAP for purposes of this Summative Assessment)
Cash and equivalents 1,523.0 1,656.1 1,725.6 1,025.7 1,791.4
Trade receivables 3,115.0 2,900.0 2,500.4 2,477.9 2,205.8
Inventories 1,323.1 1,333.7 1,460.1 1,531.5 1,405.9
Other cu
ent assets 1,028.1 1,101.4 1,076.9 932.3 872.2 4 4 a. (1 pt) What are the five types of data captured in a Growth-Share Matrix?
Total cu
ent assets 6,989.2 6,991.2 6,763.0 5,967.4 6,275.3 b. (1 pt) What are the names of the four quadrants?
c. (2pts) If Air Liquide wants to sustain growth, which quadrant would be the most fertile quadrant to find the most promising new businesses? Why?
Property, plant and equipment 20,115.7 18,525.9 19,248.2 21,117.8 20,002.9
other non-cu
ent assets * 1,236.0 1,058.7 1,025.7 1,083.3 1,213.7
Intangible assets 15,776.9 14,451.5 14,943.7 15,498.0 14,485.2
Total non-cu
ent assets 37,128.6 34,036.1 35,217.6 37,699.1 35,701.8
Total Assets 44,117.8 41,027.3 41,980.6 43,666.5 41,977.1
Provisions, pensions, and other employee benefits 279.5 332.7 325.1 268.4 316.1
Trade payables 2,485.9 2,446.4 2,714.5 2,566.6 2,437.9
Other cu
ent liabilities (including taxes and derivatives) 1,680.6 1,861.0 1,838.1 1,896.2 2,083.4
Cu
ent bo
owings 2,001.0 2,504.6 2,550.9 1,831.8 2,180.5
Cu
ent lease liabilities - 0 - 0 243.6 218.2
Total Cu
ent liabilities 6,447.0 7,144.7 7,428.6 6,806.6 7,236.1
5 3 Air Liquide challenges its employees and managers with the NEOS objectives (see page 36 of the Annual Report). Create a SMART goal that is based on one of the NEOS objectives, but is NOT the same as the NEOS Goal. This should be a goal for a department or division that would contribute to the NEOS goal. Remember to specify why it meets the SMART criteria. Note: you should create your own SMART goal. You do not need to read the entire 400 page Annual Report.
Provisions, pensions, and other employee benefits 2,592.4 2,593.3 2,410.7 2,521.2 2,418.3 NEOS goal:
Defe
ed Tax Liabilities 2,378.2 1,807.7 1,955.9 2,051.9 1,871.5
Non-cu
ent bo
owings 14,890.1 12,522.4 11,709.6 11,567.2 10,220.2 Your SMART goal
Non-cu
ent lease liabilities - 0 - 0 - 0 1,087.8 969.4
Other non-cu
ent liabilities* 685.1 240.8 268.4 307.4 257.0
Total non-cu
ent liabilities* 20,545.8 17,164.2 16,344.6 17,535.5 15,736.4
Minority interests 383.2 400.5 424.3 454.0 462.3 6 Calculate the ratio for five years (data on the left) and interpret
2016 2017 2018 2019 2020
Share capital 2,138.8 2,356.2 2,361.8 2,602.1 2,605.1 1 Cu
ent Ratio = cu
ent assets/cu
ent liabilities
Additional paid in capital 3,103.3 2,821.3 2,884.5 2,572.9 2,608.1 1 interpretation of the above
Retained earnings 11,611.40 11,276.90 12,657.80 13,824.20 13,468.90
Treasury shares (111.7) (136.5) (121.0) (128.8) (139.8) 1 Total Asset Turnover = sales/total assets
Total equity 17,125.0 16,718.4 18,207.4 19,324.4 19,004.6 1 interpretation of the above
Total Equity and Liabilities 44,117.8 41,027.3 41,980.6 43,666.5 41,977.1 1 Total Liabilities/ Asset Ratio
1 interpretation of the above
1 Net Profit Margin = net income/sales
1 interpretation of the above
1 Times Interest Earned (hint: you may ignore "other financial expenses")
Statement of Cash Flows 1 interpretation of the above
2 What is your overall impression of the financial performance, based solely on the ratios above (no extra work needed)
3 Review the statement of cash flows for XXXXXXXXXXa) did they generate or use cash from operating activities? b) what did they invest in? c) what were major cash used for debt and equity?
7 2 "We often create spreadsheets for our operating divisions' use. What are two examples of Excel "hygiene" that will make the spreadsheets more easily useable by division personnel
and less likely to have e
ors made by those users?
8 6 "Here is a hypothetical model for a new product launch. Create a sensitivity analysis for Price (+/- 10%) and Create a Scenario Analysis Worst Case with a 5% lower price, 10% lower unit sales, and 20% higher product costs per unit.
Price 4.00
units sold 10,000
product costs 1.50
selling and marketing costs 5,000
Revenues 40,000
product costs 15,000
selling and marketing costs 5,000
profit 20,000
9 4 What is the difference between the steps in a scenario analysis and a simulation? Be specific for full credit.
Day # Units Sold 10 4 Refer to the data for customer sales on the left (assume this is a sample of the data). Compute the indicated statistical measures
1 3,800 Mean
2 8,500 Median
3 2,100 Std. Deviation
4 10,000
5 14,000 Sales co
esponding to the top quartile of customers
6 700
7 1,800
8 19,000
9 5,000
10 18,000
11 4,120
12 13,300
13 9,000 "We have been asked to assist the warehouse on a problem they are having with on time deliveries. They would like ideas on how to improve. Of the following techniques, which is most
14 2,000 11 3 appropriate to address this as quickly as possible? Fish bone, Kaizen, Lean Manufacturing, Six Sigma, Total Quality Management. And explain why (added)
15 18,700
16 8,000
17 13,700
18 4,000
19 3,600
20 1,600
21 1,200 12 8 The Production Engineering department has an annual expense of $4 million, most of it in salaries
22 30,000 Cu
ently, it is charged to divisions based on total products manufactured. Look at the data below and apply the ABC concepts to recommend an allocation of the
23 450 production engineering costs to the four products Explain your choice of the basis for allocation.
24 20,000 Total Production Engineering costs 4,000,000
25 2,500 # of units produced # of direct labor hours used Hours of Production Engineering hours Total Manufacturing Costs
26 1,500 Product
27 10,500 Product A 20,000 500 5,000 5,000,000
28 900 Product B 100,000 500 750 20,000,000
29 750 Product C 300,000 1,200 5,000 40,000,000
30 11,500 Product D 900,000 900 4,000 80,000,000
31 16,500
13 2 "We use an On Demand Business Outlook approach. What is the difference between this approach and the more traditional way of budgeting? Why is it better than traditional budgeting?
14 5 You are asked to explain to a new manager the difference between incremental budgeting and zero based budgets?
Which do you recommend and why?
15 8 One of our main contributions is our work on capital investments. Analyze the projects below and
compute the NPV, IRR, PI and Payback for both. The required rate of return is 14% for each project.
in $000's 0 1 2 3 4 5
Project A -6500 1900 2500 3000 3000 1500
Project B -400 100 200 200 250 250
A B
NPV
IRR
PI
Payback
16 3 If you could only select one project, which would you choose? If you had unlimited capital, what would you do?
17 3 Assume there is another high risk, high return alternative investment, Project C. If the total investment amount is $5 million, how can you use real options to make this project more attractive? For full credit be specific to show why your approach is effective.
3. Forecasted Financial Statements 18 10 Use the information to the left and determine the value of equity and the price per share (caution: this is a time consuming and challenging question!)
dollars in millions Actual Forecast additional information:
cu
ent 1 2 3 4 cost of capital 15%
Cash 35.0 40.0 45.0 55.0 70.0 long term growth% 3%
Marketable sec 105.0 150.0 200.0 300.0 400.0 tax rate = 35%
Accounts receivable 330.0 450.0 500.0 520.0 550.0 shares outstanding 500
Inventories 200.0 230.0 240.0 250.0 270.0
Prepaid expenses 38.0 44.0 49.0 55.0 60.0
Total Cu
ent Assets 708.0 914.0 1,034.0 1,180.0 1,350.0
Net fixed assets 490.0 550.0 600.0 650.0 700.0
Goodwill 120.0 150.0 150.0 300.0 300.0
Total Assets 1,318.0 1,614.0 1,784.0 2,130.0 2,350.0
Accounts payable and accrued liabilities 200.0 250.0 290.0 325.0 375.0
Short