Budegeting and Forecasting with excel :Download the excel file, complete it and save it;You will need the following link to the Company website with the 2020 Annual Report. Go to the "2020" (not 2021) and download the annual report (called the Universal Reference Document).
https://www.airliquide.com/investors/documents-presentations(Links to an external site.)
Assessment (1 sheet) Budgeting and Forecasting BostonFill out the areas in buff/beige. Do not use other cells, we may miss it in grading! Summer 2021Your name: Prof. Larry 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. ScoreQ#Pts 12You 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 current 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 correcive actions to avchieve the best result. 22The 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 34Look at the 2020 Universal Registration Document (referred 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) 20162017201820192020 Revenues18,134.820,349.321,011.121,920.120,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 Recurring3,023.93,363.83,448.53,793.83,789.6 Non-recurring income (expenses)35.6(343.5)(161.8)(187.5)(139.5) Earnings before interest and taxes3,059.53,020.33,286.73,606.33,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 Income1,844.02,199.62,113.42,241.52,435.1 - 0 Balance Sheet (note: this is converted to US GAAP for purposes of this Summative Assessment) Cash and equivalents1,523.01,656.11,725.61,025.71,791.4 Trade receivables3,115.02,900.02,500.42,477.92,205.8 Inventories1,323.11,333.71,460.11,531.51,405.9 Other current assets1,028.11,101.41,076.9932.3872.244a. (1 pt) What are the five types of data captured in a Growth-Share Matrix? Total current assets6,989.26,991.26,763.05,967.46,275.3b. (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 equipment20,115.718,525.919,248.221,117.820,002.9 other non-current assets *1,236.01,058.71,025.71,083.31,213.7 Intangible assets15,776.914,451.514,943.715,498.014,485.2 Total non-current assets37,128.634,036.135,217.637,699.135,701.8 Total Assets44,117.841,027.341,980.643,666.541,977.1 Provisions, pensions, and other employee benefits279.5332.7325.1268.4316.1 Trade payables2,485.92,446.42,714.52,566.62,437.9 Other current liabilities (including taxes and derivatives)1,680.61,861.01,838.11,896.22,083.4 Current borrowings2,001.02,504.62,550.91,831.82,180.5 Current lease liabilities- 0- 0243.6218.2 Total Current liabilities6,447.07,144.77,428.66,806.67,236.1 53Air 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 benefits2,592.42,593.32,410.72,521.22,418.3NEOS goal: Deferred Tax Liabilities2,378.21,807.71,955.92,051.91,871.5 Non-current borrowings14,890.112,522.411,709.611,567.210,220.2Your SMART goal Non-current lease liabilities- 0- 0- 01,087.8969.4 Other non-current liabilities*685.1240.8268.4307.4257.0 Total non-current liabilities*20,545.817,164.216,344.617,535.515,736.4 Minority interests383.2400.5424.3454.0462.36Calculate the ratio for five years (data on the left) and interpret 20162017201820192020 Share capital2,138.82,356.22,361.82,602.12,605.11Current Ratio = current assets/current liabilities Additional paid in capital3,103.32,821.32,884.52,572.92,608.11interpretation of the above Retained earnings11,611.4011,276.9012,657.8013,824.2013,468.90 Treasury shares(111.7)(136.5)(121.0)(128.8)(139.8)1Total Asset Turnover = sales/total assets Total equity17,125.016,718.418,207.419,324.419,004.61interpretation of the above Total Equity and Liabilities44,117.841,027.341,980.643,666.541,977.11Total Liabilities/ Asset Ratio 1interpretation of the above 1Net Profit Margin = net income/sales 1interpretation of the above 1Times Interest Earned (hint: you may ignore "other financial expenses") Statement of Cash Flows1interpretation of the above 2What is your overall impression of the financial performance, based solely on the ratios above (no extra work needed) 3Review the statement of cash flows for 2020. a) 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? 72"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 errors made by those users? 86"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. Price4.00 units sold10,000 product costs1.50 selling and marketing costs5,000 Revenues40,000 product costs15,000 selling and marketing costs5,000 profit20,000 94What is the difference between the steps in a scenario analysis and a simulation? Be specific for full credit. Day #Units Sold104Refer to the data for customer sales on the left (assume this is a sample of the data). Compute the indicated statistical measures 13,800Mean 28,500Median 32,100Std. Deviation 410,000 514,000Sales corresponding to the top quartile of customers 6700 71,800 819,000 95,000 1018,000 114,120 1213,300 139,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 142,000113appropriate to address this as quickly as possible? Fish bone, Kaizen, Lean Manufacturing, Six Sigma, Total Quality Management. And explain why (added) 1518,700 168,000 1713,700 184,000 193,600 201,600 211,200128The Production Engineering department has an annual expense of $4 million, most of it in salaries 2230,000Currently, 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 23450production engineering costs to the four products Explain your choice of the basis for allocation. 2420,000Total Production Engineering costs4,000,000 252,500# of units produced# of direct labor hours usedHours of Production Engineering hoursTotal Manufacturing Costs 261,500Product 2710,500Product A20,0005005,0005,000,000 28900Product B100,00050075020,000,000 29750Product C300,0001,2005,00040,000,000 3011,500Product D900,0009004,00080,000,000 3116,500 132"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? 145You are asked to explain to a new manager the difference between incremental budgeting and zero based budgets? Which do you recommend and why? 158One 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's012345 Project A-650019002500300030001500 Project B-400100200200250250 AB NPV IRR PI Payback 163If you could only select one project, which would you choose? If you had unlimited capital, what would you do? 173Assume 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 Statements1810Use 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 millionsActualForecastadditional information: current1234 cost of capital15% Cash35.040.045.055.070.0 long term growth%3% Marketable sec105.0150.0200.0300.0400.0 tax rate =35% Accounts receivable330.0450.0500.0520.0550.0 shares outstanding500 Inventories200.0230.0240.0250.0270.0 Prepaid expenses38.044.049.055.060.0 Total Current Assets708.0914.01,034.01,180.01,350.0 Net fixed assets490.0550.0600.0650.0700.0 Goodwill120.0150.0150.0300.0300.0 Total Assets1,318.01,614.01,784.02,130.02,350.0 Accounts payable and accrued liabilities200.0250.0290.0325.0375.0 Short