| | | Budgeting and Forecasting | | | | | | | | | | | | Fill out the areas in buff/beige. Do not use other cells, we may miss it in grading! |
| | | Summer 2021 | | | | | | | | | | | | Your name: |
| | | Prof. Larry Louie |
| | | | | | | | | | | | | | | The Summative Assessment is based on The Clorox Company. 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 |
| | | | in millions |
| | Balance Sheet | | | | | | | | | | | 1 | 2 | You have joined the FP&A department at The Clorox Company. The CFO, Kevin Jacobsen, has risen through the ranks since 1995. His last position prior to promotion to CFO was as VP of FP&A. |
| | Report Date | 06/30/2016 | 06/30/2017 | 06/30/2018 | 06/30/2019 | 06/30/2020 | | | | | | | | He 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 "What is the difference in the role of FP&A and the Controller"? |
| | Cash & Equivalents | 401.00 | 418.00 | 131.00 | 111.00 | 871.00 |
| | Cash & Equivs & ST Investments | 401.00 | 418.00 | 131.00 | 111.00 | 871.00 |
| | Receivables (ST) | 569.00 | 565.00 | 600.00 | 631.00 | 648.00 |
| | Inventories | 443.00 | 459.00 | 506.00 | 512.00 | 454.00 | | | | | | 2 | 2 | "Our CFO Kevin spent 8 years in FP&A prior to his CFO promotion. What is the role of FP&A in working with the CEO, CFO and Board of Directors? Give two examples of work that FP&A does |
| | Prepayments (ST) | 70.00 | - 0 | - 0 | - 0 | - 0 | | | | | | | | to contribute to executive level decisions" |
| | Other Current Assets | 2.00 | 72.00 | 74.00 | 51.00 | 47.00 |
| | Total Current Assets | 1,485.00 | 1,514.00 | 1,311.00 | 1,305.00 | 2,020.00 |
| | Gross Property Plant & Equip | 2,817.00 | 2,932.00 | 3,057.00 | 3,184.00 | 3,327.00 |
| | Accumulated Depreciation | 1,911.00 | 2,001.00 | 2,061.00 | 2,150.00 | 2,224.00 | | | | | | 3 | 4 | Here are two examples of products in our wide ranging portfolio of consumer products. From the standpoint of the Growth-Share Matrix, a) which quadrant would you find the |
| | Net Property Plant & Equip | 906.00 | 931.00 | 996.00 | 1,034.00 | 1,103.00 | | | | | | | | Glad to be Green and Clorox Bleach products? And b) what would be the investment strategy for the two product groups and why? |
| | Intangible Assets | 1,932.00 | 1,918.00 | 2,531.00 | 2,503.00 | 2,471.00 |
| | Other Assets | 195.00 | 210.00 | 222.00 | 274.00 | 619.00 |
| | Total Assets | 4,518.00 | 4,573.00 | 5,060.00 | 5,116.00 | 6,213.00 |
| | Accounts Payable & Accrued Exps | 1,035.00 | 1,005.00 | 1,001.00 | 1,035.00 | 1,329.00 |
| | Accounts Payable | 490.00 | 501.00 | 627.00 | 623.00 | 731.00 |
| | Accrued Expenses | 545.00 | 504.00 | 374.00 | 412.00 | 598.00 |
| | Current Debt | 523.00 | 804.00 | 199.00 | 396.00 | - 0 |
| | Other Current Liabilities | - 0 | - 0 | - 0 | 9.00 | 89.00 |
| | Total Current Liabilities | 1,558.00 | 1,809.00 | 1,200.00 | 1,440.00 | 1,418.00 |
| | LT Debt & Leases | 1,797.00 | 1,391.00 | 2,284.00 | 2,287.00 | 2,780.00 |
| | Pensions & OPEB | 335.00 | 298.00 | 283.00 | 280.00 | 294.00 |
| | Deferred LT Liabilities | 82.00 | 61.00 | 72.00 | 50.00 | 62.00 |
| | Minority Interests | - 0 | - 0 | - 0 | - 0 | - 0 |
| | Other Liabilities | 449.00 | 472.00 | 495.00 | 500.00 | 751.00 |
| | Total Liabilities | 4,221.00 | 4,031.00 | 4,334.00 | 4,557.00 | 5,305.00 |
| | Common Share Capital | 159.00 | 159.00 | 159.00 | 159.00 | 159.00 |
| | Additional Paid-In Capital | 868.00 | 928.00 | 975.00 | 1,046.00 | 1,137.00 |
| | Retained Earnings | 2,163.00 | 2,440.00 | 2,797.00 | 3,150.00 | 3,567.00 |
| | Accum Other Comprehensive Income | (570.00) | (543.00) | (547.00) | (602.00) | (640.00) |
| | Treasury Stock | 2,323.00 | 2,442.00 | 2,658.00 | 3,194.00 | 3,315.00 |
| | For Curr Trans (BS) | (353.00) | (356.00) | (384.00) | (414.00) | - 0 |
| | Other Equity | 353.00 | 356.00 | 384.00 | 414.00 | - 0 |
| | Total Equity | 297.00 | 542.00 | 726.00 | 559.00 | 908.00 | | | | | | 4 | 4 | What are the five types of data captured in a Growth-Share Matrix? What are the names of the four quadrants? If Clorox wants to sustain growth, which quadrant would be the most |
| | Total Liabilities & Equity | 4,518.00 | 4,573.00 | 5,060.00 | 5,116.00 | 6,213.00 | | | | | | | | fertile quadrant to find the most promising new businesses? Why? |
| | Income Statement |
| | Report Date | 06/30/2016 | 06/30/2017 | 06/30/2018 | 06/30/2019 | 06/30/2020 |
| | Sales Revenue | 5,761.00 | 5,973.00 | 6,124.00 | 6,214.00 | 6,721.00 |
| | Total Revenue | 5,761.00 | 5,973.00 | 6,124.00 | 6,214.00 | 6,721.00 |
| | Direct Costs | 3,163.00 | 3,302.00 | 3,449.00 | 3,486.00 | 3,658.00 |
| | Gross Profit | 2,598.00 | 2,671.00 | 2,675.00 | 2,728.00 | 3,063.00 |
| | Selling General & Admin | 1,393.00 | 1,409.00 | 1,407.00 | 1,468.00 | 1,644.00 |
| | Depreciation & Amortization | 8.00 | 10.00 | 11.00 | 17.00 | 13.00 |
| | Research & Development | 141.00 | 135.00 | 132.00 | 136.00 | 145.00 |
| | Restructure, Remediation & Impair | 9.00 | 23.00 | 1.00 | - 0 | 2.00 | | | | | | 5 | 3 | You have been asked to create a SMART goal for the Glad to be Green" product. Give one SMART goals for 2022, and explain how it meets the SMART objectives. |
| | Other Operating Expense | (4.00) | 7.00 | (1.00) | (10.00) | 2.00 |
| | Total Indirect Operating Costs | 1,547.00 | 1,584.00 | 1,550.00 | 1,611.00 | 1,806.00 |
| | Operating Income | 1,051.00 | 1,087.00 | 1,125.00 | 1,117.00 | 1,257.00 |
| | Interest Income | (83.00) | (84.00) | (79.00) | (94.00) | (97.00) |
| | Gains on Sale of Assets | - 0 | 11.00 | (4.00) | - 0 | - 0 |
| | Other Non-Operating Income | - 0 | - 0 | - 0 | (14.00) | 5.00 |
| | Total Non-Operating Income | (83.00) | (73.00) | (83.00) | (108.00) | (92.00) | | | | | | 6 | | Calculate the ratio for five years (data on the left) and interpret |
| | Earnings Before Tax | 968.00 | 1,014.00 | 1,042.00 | 1,009.00 | 1,165.00 | | | | | | | | | | | | | 2016 | 2017 | 2018 | 2019 | 2020 |
| | | | | | | | | | | | | | 1 | Current Ratio = current assets/current liabilities |
| | Taxation | 335.00 | 330.00 | 231.00 | 204.00 | 246.00 | | | | | | | 1 | | interpretation of the above |
| | Equity Earnings | 15.00 | 19.00 | 12.00 | 15.00 | 20.00 |
| | Discontinued Operations | - 0 | (2.00) | - 0 | - 0 | - 0 | | | | | | | 1 | Total Asset Turnover = sales/total assets |
| | Net Income | 648.00 | 701.00 | 823.00 | 820.00 | 939.00 | | | | | | | 1 | | interpretation of the above |
| | Average Shares Basic | 129.47 | 128.95 | 129.29 | 127.73 | 125.83 | | | | | | | 1 | Total Liabilities/ Asset Ratio |
| | EPS Net Basic | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | | | | | | | 1 | | interpretation of the above |
| | EPS Continuing Basic | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| | Average Shares Diluted | 131.72 | 131.57 | 131.58 | 129.79 | 127.67 | | | | | | | 1 | Net Profit Margin = net income/sales |
| | EPS Net Diluted | 0.00 | 0.01 | 0.01 | 0.01 | 0.01 | | | | | | | 1 | | interpretation of the above |
| | EPS Continuing Diluted | 0.00 | 0.01 | 0.01 | 0.01 | 0.01 |
| | Shares Outstanding | 129.36 | 129.01 | 127.98 | 125.69 | 126.20 | | | | | | | 1 | Research and Development as % of sales = r&d expenses/sale |
| | | | | | | | | | | | | | 1 | | interpretation of the above |
| | Cash Flow Statement |
| | Report Date | 06/30/2016 | 06/30/2017 | 06/30/2018 | 06/30/2019 | 06/30/2020 | | | | | | | 2 | What is your overall impression of the financial performance, based solely on the ratios above (no extra work needed) |
| | Net Income | 648.00 | 701.00 | 823.00 | 820.00 | 939.00 |
| | Adjustments from Inc to Cash | 216.00 | 217.00 | 239.00 | 174.00 | 258.00 |
| | Change in Working Capital | (96.00) | (47.00) | (88.00) | (28.00) | 316.00 |
| | Other Operating Cash Flows | 10.00 | (3.00) | - 0 | 26.00 | 33.00 | | | | | | | 3 | Review the statement of cash flows. 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? |
| | Cash Flow from Operations | 778.00 | 868.00 | 974.00 | 992.00 | 1,546.00 |
| | | - 0 | - 0 | - 0 | - 0 | - 0 |
| | Purchase of Pty Plant & Equip | (172.00) | (231.00) | (194.00) | (206.00) | (254.00) |
| | Acquisitions | (290.00) | - 0 | (681.00) | - 0 | - 0 |
| | Other Investing Cash Flows | 32.00 | 26.00 | 16.00 | 10.00 | 2.00 |
| | Cash Flow from Investing | (430.00) | (205.00) | (859.00) | (196.00) | (252.00) | | | | | | 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 |
| | | - 0 | - 0 | - 0 | - 0 | - 0 | | | | | | | | and less likely to have errors made by those users? |
| | Change in ST Debt | 426.00 | (125.00) | (214.00) | 189.00 | (396.00) |
| | Change in LT Debt | (300.00) | - 0 | 491.00 | - 0 | 492.00 |
| | Treasury stock purchased | (254.00) | (183.00) | (271.00) | (661.00) | (248.00) |
| | Payment of Dividends | (398.00) | (412.00) | (450.00) | (490.00) | (533.00) |
| | Issuance of common stock for employee stock options | 210.00 | 75.00 | 45.00 | 147.00 | 162.00 |
| | Cash Flow from Financing | (316.00) | (645.00) | (399.00) | (815.00) | (523.00) |
| | Change in Cash | 19.00 | 17.00 | (287.00) | (21.00) | 766.00 | | | | | | 8 | 8 | "Here is a hypothetical model for a new product launch. Create one example of a sensitivity analysis and one example of a scenario analysis. For full credit, explain what you are testing with the sensitivity and scenario, do the calculation, and interpret the results. |
| | | - 0 | - 0 | - 0 | - 0 | - 0 |
| | Opening Cash | 382.00 | 401.00 | 418.00 | 134.00 | 113.00 |
| | Closing Cash | 401.00 | 418.00 | 131.00 | 113.00 | 879.00 | | | | | | | | Price | | 4.00 |
| | | - 0 | - 0 | - 0 | - 0 | - 0 | | | | | | | | units sold | | 10,000 |
| | Depn & Amortn (CF) | 165.00 | 163.00 | 166.00 | 180.00 | 180.00 | | | | | | | | product costs | | 1.50 |
| | Net Purch of Pty Plant & Equip | (172.00) | (231.00) | (194.00) | (206.00) | (254.00) | | | | | | | | 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. Compute the indicated statistical measures |
| | | | | | | | | 1 | 3,800 | | | | | Mean |
| | | | | | | | | 2 | 8,500 | | | | | Median |
| | | | | | | | | 3 | 2,100 | | | | | Standard deviation |
| | | | | | | | | 4 | 10,000 |
| | | | | | | | | 5 | 14,000 | | | | | Sales corresponding to the top decile of customers |
| | | | | | | | | 6 | 700 |
| | | | | | | | | 7 | 1,800 |
| | | | | | | | | 8 | 12,000 |
| | | | | | | | | 9 | 3,000 |
| | | | | | | | | 10 | 18,000 |
| | | | | | | | | 11 | 3,100 |
| | | | | | | | | 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, Kaisen, Lean Manufacturing, Six Sigma, Total Quality Management |
| | | | | | | | | 15 | 13,000 |
| | | | | | | | | 16 | 8,000 |
| | | | | | | | | 17 | 13,700 |
| | | | | | | | | 18 | 4,000 |
| | | | | | | | | 19 | 1,700 |
| | | | | | | | | 20 | 1,600 |
| | | | | | | | | 21 | 800 | | | 12 | 8 | The Production Engineering department has an annual expense of $4 million, most of it in salaries |
| | | | | | | | | 22 | 30,000 | | | | | Currently, 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 | 600 | | | | | 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,400 | | | | | | | # 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 | 250 | 5,000 | 5,000,000 |
| | | | | | | | | 28 | 900 | | | | | Product B | | 100,000 | 500 | 7,000 | 20,000,000 |
| | | | | | | | | 29 | 400 | | | | | Product C | | 300,000 | 1,200 | 4,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 | 6 | 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 12% for each project. |
| | | | | | | | | | | | | | | in $000's | 0 | 1 | 2 | 3 | 4 | 5 |
| | | | | | | | | | | | | | | Project A | -5000 | 1000 | 2000 | 2000 | 2000 | 1500 |
| | | | | | | | | | | | | | | Project B | -1000 | 100 | 300 | 500 | 600 | 700 |
| | | | | | | | | | | | | 16 | 3 | If you could only select one project, which would you choose? If you had unlimited capital, what would you do? |
| | | | | | | | | | | | | | | I would recommend Project B since it has the highest Profitability Index. This means for every dollar of initial capital investments, it generates the most value (NPV of the future cash flows). If there is excess funds, we can consider also funding Project A or other projects we have not seen here. |
| | | | | | | | | | | | | 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: |
| | | | | | | current | | 1 | 2 | 3 | 4 | | | cost of capital | | 11% |
| | | Cash | | | | 30.0 | | 40.0 | 45.0 | 55.0 | 70.0 | | | long term growth% | | 2% |
| | | Marketable sec | | | | 100.0 | | 150.0 | 200.0 | 300.0 | 400.0 | | | tax rate = | | 35% |
| | | Accounts receivable | | | | 300.0 | | 450.0 | 500.0 | 520.0 | 550.0 | | | shares outstanding | | 400 |
| | | Inventories | | | | 190.0 | | 230.0 | 240.0 | 250.0 | 270.0 |
| | | Prepaid expenses | | | | 40.0 | | 44.0 | 49.0 | 55.0 | 60.0 |
| | | Total Current Assets | | | | 660.0 | | 914.0 | 1,034.0 | 1,180.0 | 1,350.0 |
| | | Net fixed assets | | | | 450.0 | | 550.0 | 600.0 | 650.0 | 700.0 |
| | | Goodwill | | | | 100.0 | | 150.0 | 150.0 | 300.0 | 300.0 |
| | | Total Assets | | | | 1,210.0 | | 1,614.0 | 1,784.0 | 2,130.0 | 2,350.0 |
| | | Accounts payable and accrued liabilities | | | | 200.0 | | 270.0 | 290.0 | 325.0 | 375.0 |
| | | Short term loans | | | | 40.0 | | 40.0 | 50.0 | 55.0 | 60.0 |
| | | Total current liabilities | | | | 240.0 | | 310.0 | 340.0 | 380.0 | 435.0 |
| | | Bonds payable | | | | 150.0 | | 150.0 | 150.0 | 150.0 | 150.0 |
| | | Other operating non-current liabilities | | | | 35.0 | | 50.0 | 60.0 | 70.0 | 80.0 |
| | | total long term liabilities | | | | 185.0 | | 200.0 | 210.0 | 220.0 | 230.0 |
| | | total liabilities | | | | 425.0 | | 510.0 | 550.0 | 600.0 | 665.0 |
| | | Common stock (300mil shares outstanding) | | | | 60.0 | | 60.0 | 60.0 | 60.0 | 60.0 |
| | | Retained Earnings | | | | 725.0 | | 1,044.0 | 1,174.0 | 1,470.0 | 1,625.0 |
| | | Total shareholders' equity | | | | 785.0 | | 1,104.0 | 1,234.0 | 1,530.0 | 1,685.0 |
| | | Total liabilities and equity | | | | 1,210 | | 1,614 | 1,784 | 2,130 | 2,350 |
| | | | | NOPAT | | | | 600 | 650 | 750 | 900 |
| | | | | | | | | | | | | 19 | 8 | The data for 2010 TO 2019 for a Clorox division revenues is to the left. Analyze using two approaches: a) Compute and graph the 4 year moving average and b) Use the quadratic regression technique to determine a formula for sales in year 11. For full credit, show the formula, the sales estimate, and your assessment of the accuracy of the formula. |
| | | | | | | | | | | | | | | YEAR | SALES |
| | | | | | | | | | | | | | | 1 | 44,000 |
| | | | | | | | | | | | | | | 2 | 54,000 |
| | | | | | | | | | | | | | | 3 | 55,000 |
| | | | | | | | | | | | | | | 4 | 52,700 |
| | | | | | | | | | | | | | | 5 | 56,000 |
| | | | | | | | | | | | | | | 6 | 57,500 |
| | | | | | | | | | | | | | | 7 | 59,000 |
| | | | | | | | | | | | | | | 8 | 63,000 |
| | | | | | | | | | | | | | | 9 | 71,000 |
| | | | | | | | | | | | | | | 10 | 72,000 |
| | | | | | | | | | | | | 20 | 4 | Performance Reporting |
| | | | | | | | | | | | | | | Clorox is moving toward a more comprehensive reporting approach. |
| | | | | | | | | | | | | | | What would be one example of a metric that shows their commitment to sustainability? |
| | | | | | | | | | | | | | | What would be one example of a metric that shows their commitment to the community? |
| | | | | | | | | | | | | | 100 | total points possible |
| 0.0 |
isha1108
summativeassessment2021-forstudents.xlsx
