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. 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, Rp = PreTax Debt interest rate 7.50% Tax Rate 35,00% of EBT Financial Statements Historical How to Project PPE Roll Forward Eas Item EOY-1 20X-1 EOYO 20X0 Project as % 20X1 PPER (BOP) - Deprec + Capx = PPE (EOP) 73.913 88.957 (7.39) (8.70) 20.435 21.739 88.957 100.000 Solve for this 0.100 2 100.000 1 12.000 Income Statement Sales COGS Depreciation SGA EBIT Y-1 YO 20X-1 20x0 Project as 66.957 100.000 Sales Growth 60.000 70.000 8.182 8.696 15.000 17.000 % Sales 3.775 4.304 1.350 1.500 To BOP Debt 0.849 0.982 1.576 1.823 % 0.150 3 5 0.170 20X1 115.000 4 6 19.550 Interest Tax NI 0.075 7 1.500 8 EOY-1 20X-1 Project as % Equity Roll Forward Eqs Item + NI - Div + PIC-PC bytok - Eeor EOYO 20X0 58.820 1.823 0.000 9.360 70.003 20X1 70.003 2.243 10 0.000 0.000 0.000 58.820 Set to 0 (Policy) 9 0.000 11 Balance Sheets Cash AR Inventory Other CA Total CA PPE(net) Total Assets 20X1 13 23.000 15 11.500 EOY-1 EOYO 20X-1 20X0 Project as % 4.773 5.000 12 18.000 20.000 Sales 0.200 25.000 30.000 14 9.000 10.000 '% Sales 0.100 56.77365.000 86.957 100.000 PPE roll forward ag 143.729 165.000 25.909 30.000 % Sales 0.300 22.000 25.000 16 17.000 20.000 % Sales 0.200 64.909 75.000 20.000 20.000 Solve for this 84.909 95.000 58.820 70.003 Equity Roll Forward 58.820 70.000 0.000 0.000 34.500 AP Accrued Liabilities Other CL TotalCL 17 23.000 18 Debt Total Liabilities Equity from Roll Forward Equity from E = A-L Difference (Should be zero) 9 What is the value of item 9? 10 What is the value of item 10? Hint: this is a negative number, as it represents a reduction to Equity. 11 What is the value of item 11? 12 What is the value of item 12? 13 What is the value of item 13? 14 What is the value of item 14? 15 What is the value of item 15? 16 What is the value of item 16? 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. 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, Rp = PreTax Debt interest rate 7.50% Tax Rate 35,00% of EBT Financial Statements Historical How to Project PPE Roll Forward Eas Item EOY-1 20X-1 EOYO 20X0 Project as % 20X1 PPER (BOP) - Deprec + Capx = PPE (EOP) 73.913 88.957 (7.39) (8.70) 20.435 21.739 88.957 100.000 Solve for this 0.100 2 100.000 1 12.000 Income Statement Sales COGS Depreciation SGA EBIT Y-1 YO 20X-1 20x0 Project as 66.957 100.000 Sales Growth 60.000 70.000 8.182 8.696 15.000 17.000 % Sales 3.775 4.304 1.350 1.500 To BOP Debt 0.849 0.982 1.576 1.823 % 0.150 3 5 0.170 20X1 115.000 4 6 19.550 Interest Tax NI 0.075 7 1.500 8 EOY-1 20X-1 Project as % Equity Roll Forward Eqs Item + NI - Div + PIC-PC bytok - Eeor EOYO 20X0 58.820 1.823 0.000 9.360 70.003 20X1 70.003 2.243 10 0.000 0.000 0.000 58.820 Set to 0 (Policy) 9 0.000 11 Balance Sheets Cash AR Inventory Other CA Total CA PPE(net) Total Assets 20X1 13 23.000 15 11.500 EOY-1 EOYO 20X-1 20X0 Project as % 4.773 5.000 12 18.000 20.000 Sales 0.200 25.000 30.000 14 9.000 10.000 '% Sales 0.100 56.77365.000 86.957 100.000 PPE roll forward ag 143.729 165.000 25.909 30.000 % Sales 0.300 22.000 25.000 16 17.000 20.000 % Sales 0.200 64.909 75.000 20.000 20.000 Solve for this 84.909 95.000 58.820 70.003 Equity Roll Forward 58.820 70.000 0.000 0.000 34.500 AP Accrued Liabilities Other CL TotalCL 17 23.000 18 Debt Total Liabilities Equity from Roll Forward Equity from E = A-L Difference (Should be zero) 9 What is the value of item 9? 10 What is the value of item 10? Hint: this is a negative number, as it represents a reduction to Equity. 11 What is the value of item 11? 12 What is the value of item 12? 13 What is the value of item 13? 14 What is the value of item 14? 15 What is the value of item 15? 16 What is the value of item 16