Pro-Forma Projections Excellent eBikes (EEB) Historical financial statements and ratio analyses for Excellent eBikes (EEB) are given below. For the upcoming year (20X1), the board of directors has dictated a dividend of 10% of Net Income. The chief technology officer has said that CAPX should be targeted at 120% of 20X1 Depreciation. The CFO believes that the firm's EOY cash level should be 5% of 20X1 Sales. Other parameters for 20X1 are as listed below. . Given all this information, the CFO believes that the firm can actually reduce its debt. Find out if she is correct by completing the projection analysis that she has started. Hint: Solve for Debt at EOY 20X1. 1 Compute and report numbers for cells that look like this: To complete your work you also must compute numbers for cells that look like this: but you do not need to report these numbers. Parameters for Pro-Forma Projections (Irrespective of History) Sales Growth 15.00% Dividends set at 10% of NI Average Depreciable life of Assets 10.00 Years --> Deprec = 10% of BOP netPPE CAPX set at 120.00% of Depreciation Safe cash level 5% of TTM Sales No stock issuance or buyback. Ro = PreTax Debt interest rate 7.50% Tax Rate 35.00% of EBT Financial Statements Historical How to Project PPE Roll Forward Eqs Item % 20X1 PPER (BOP) - Deprec + Capx = PPEnet (EOP) EOY-1 20X-1 20x0 Project as 73.913 86.957 (7.39) (8.70) 20.435 21.739 86.957 100.000 Solve for this 0.100 2 100.000 1 12.000 20X1 Income Statement Sales COGS Depreciation SGA EBIT Y-1 YO 20X-1 20x0 Project as 86.957 100.000 Sales Growth 70.000 8.696 17.000 % Sales 4.304 1.500 * BOP Debt 0.982 1.576 1.823 % 0.150 115.000 3 4 5 6 0.170 19.550 Interest Tax NI 0.075 7 1.500 8 EOY -1 20X-1 Project as % Equity Roll Forward Eqs Item EBOP + NI - Div + PIC (- PIC for buyback) 20x0 58.820 1.823 0.000 9.360 70.003 20X1 70.003 2.243 10 0.000 11 0.000 0.000 58.820 Set to 0 (Policy) 9 0.000 = EEOP Project as EOY -1 20X-1 4.773 18.000 25.000 9.000 56.773 Balance Sheets Cash AR Inventory Other CA Total CA EOYO 20x0 5.000 20.000 30.000 10.000 65.000 % Sales % 12 0.200 14 0.100 20X1 13 23.000 15 11.500 % Sales 311 PPE roll forward eq PPE(net) Total Assets 86.957 143.729 100.000 165.000 % Sales AP Accrued Liabilities Other CL Total CL 25.909 22.000 17.000 64.909 30.000 25.000 20.000 75.000 0.300 16 0.200 34.500 17 23.000 % Sales Debt 20.000 20.000 Solve for this 18 Total Liabilities 84.909 95.000 58.820 70.003 Equity Roll Forward Equity from Roll Forward Equity from E = A-L Difference (Should be zero) 58.820 70.000 0.000 0.000 Question 9 What is the value of item 9? Question 10 What is the value of item 10? Hint: this is a negative number, as it represents a reduction to Equity. Question 11 What is the value of item 11? Question 12 What is the value of item 12? Question 13 What is the value of item 13? Question 14 What is the value of item 14? Question 15 What is the value of item 15? Question 16 What is the value of item 16? Question 17 What is the value of item 17? Question 18 How much Debt do you project the company will hold at EOY 20X1? Question 19 1 point Is the CFO correct that, if the projections pan out, the firm's debt level will be reduced by EOY 20X1? Answer Yes or No and provide the one best supporting reason for your answer. Yes Because cash expenses are rising in 20X1 and the Dividend increases the need for additional cash. Because Sales are growing rapidly. O No Because the Dividend projected for 20X1 is less than the projected Net Income Because the Debt projected on the 20X1 Balance Sheet is less than the Debt shown on the 20X0 Balance Sheet Because the Debt projected on the 20x1 Balance Sheet is more than the Debt shown on the 20x0 Balance Sheet Question 20 1 point Which item number corresponds to the projected 20X1 Dividend? O 18 O 10 013 12 Pro-Forma Projections Excellent eBikes (EEB) Historical financial statements and ratio analyses for Excellent eBikes (EEB) are given below. For the upcoming year (20X1), the board of directors has dictated a dividend of 10% of Net Income. The chief technology officer has said that CAPX should be targeted at 120% of 20X1 Depreciation. The CFO believes that the firm's EOY cash level should be 5% of 20X1 Sales. Other parameters for 20X1 are as listed below. . Given all this information, the CFO believes that the firm can actually reduce its debt. Find out if she is correct by completing the projection analysis that she has started. Hint: Solve for Debt at EOY 20X1. 1 Compute and report numbers for cells that look like this: To complete your work you also must compute numbers for cells that look like this: but you do not need to report these numbers. Parameters for Pro-Forma Projections (Irrespective of History) Sales Growth 15.00% Dividends set at 10% of NI Average Depreciable life of Assets 10.00 Years --> Deprec = 10% of BOP netPPE CAPX set at 120.00% of Depreciation Safe cash level 5% of TTM Sales No stock issuance or buyback. Ro = PreTax Debt interest rate 7.50% Tax Rate 35.00% of EBT Financial Statements Historical How to Project PPE Roll Forward Eqs Item % 20X1 PPER (BOP) - Deprec + Capx = PPEnet (EOP) EOY-1 20X-1 20x0 Project as 73.913 86.957 (7.39) (8.70) 20.435 21.739 86.957 100.000 Solve for this 0.100 2 100.000 1 12.000 20X1 Income Statement Sales COGS Depreciation SGA EBIT Y-1 YO 20X-1 20x0 Project as 86.957 100.000 Sales Growth 70.000 8.696 17.000 % Sales 4.304 1.500 * BOP Debt 0.982 1.576 1.823 % 0.150 115.000 3 4 5 6 0.170 19.550 Interest Tax NI 0.075 7 1.500 8 EOY -1 20X-1 Project as % Equity Roll Forward Eqs Item EBOP + NI - Div + PIC (- PIC for buyback) 20x0 58.820 1.823 0.000 9.360 70.003 20X1 70.003 2.243 10 0.000 11 0.000 0.000 58.820 Set to 0 (Policy) 9 0.000 = EEOP Project as EOY -1 20X-1 4.773 18.000 25.000 9.000 56.773 Balance Sheets Cash AR Inventory Other CA Total CA EOYO 20x0 5.000 20.000 30.000 10.000 65.000 % Sales % 12 0.200 14 0.100 20X1 13 23.000 15 11.500 % Sales 311 PPE roll forward eq PPE(net) Total Assets 86.957 143.729 100.000 165.000 % Sales AP Accrued Liabilities Other CL Total CL 25.909 22.000 17.000 64.909 30.000 25.000 20.000 75.000 0.300 16 0.200 34.500 17 23.000 % Sales Debt 20.000 20.000 Solve for this 18 Total Liabilities 84.909 95.000 58.820 70.003 Equity Roll Forward Equity from Roll Forward Equity from E = A-L Difference (Should be zero) 58.820 70.000 0.000 0.000 Question 9 What is the value of item 9? Question 10 What is the value of item 10? Hint: this is a negative number, as it represents a reduction to Equity. Question 11 What is the value of item 11? Question 12 What is the value of item 12? Question 13 What is the value of item 13? Question 14 What is the value of item 14? Question 15 What is the value of item 15? Question 16 What is the value of item 16? Question 17 What is the value of item 17? Question 18 How much Debt do you project the company will hold at EOY 20X1? Question 19 1 point Is the CFO correct that, if the projections pan out, the firm's debt level will be reduced by EOY 20X1? Answer Yes or No and provide the one best supporting reason for your answer. Yes Because cash expenses are rising in 20X1 and the Dividend increases the need for additional cash. Because Sales are growing rapidly. O No Because the Dividend projected for 20X1 is less than the projected Net Income Because the Debt projected on the 20X1 Balance Sheet is less than the Debt shown on the 20X0 Balance Sheet Because the Debt projected on the 20x1 Balance Sheet is more than the Debt shown on the 20x0 Balance Sheet Question 20 1 point Which item number corresponds to the projected 20X1 Dividend? O 18 O 10 013 12