What is the value of item 1-17?

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) 15.00% 10% of NI 10.00 Years Sales Growth Dividends set at Average Depreciable life of Assets --> Deprec = 10% of BOP netPPE CAPX set at Safe cash level No stock issuance or buyback. Rp = Pre Tax Debt interest rate Tax Rate 120.00% of Depreciation 5% of TTM Sales 7.50% 35.00% of EBT Financial Statements Historical How to Project PPE Roll Forward Eqs Item EOY -1 20X-1 EOY O 20XO Project as % 20X1 PPEnet (BOP) - Deprec + Capx = PPEnet (EOP) 73.913 (7.39) 20.435 86.957 86.957 (8.70) 21.739 100.000 0.100 2 100.000 1 12.000 Solve for this Project as Sales Growth Income Statement Sales COGS Depreciation SGA EBIT Y-1 20X-1 86.957 60.000 8.182 15.000 3.775 YO 200 100.000 70.000 8.696 17.000 4.304 % 0.150 3 5 0.170 20X1 115.000 4 6 19.550 % Sales ro * BOP Debt 1.500 Interest Tax NI 1.350 0.849 1.576 1.500 0.982 1.823 0.075 7 8 EOY -1 20X-1 Project as % Equity Roll Forward Eqs Item EBOP + NI - Div + PIC (- PIC for buyback) = EEOP EOY O 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 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 EOY O 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 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 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) 15.00% 10% of NI 10.00 Years Sales Growth Dividends set at Average Depreciable life of Assets --> Deprec = 10% of BOP netPPE CAPX set at Safe cash level No stock issuance or buyback. Rp = Pre Tax Debt interest rate Tax Rate 120.00% of Depreciation 5% of TTM Sales 7.50% 35.00% of EBT Financial Statements Historical How to Project PPE Roll Forward Eqs Item EOY -1 20X-1 EOY O 20XO Project as % 20X1 PPEnet (BOP) - Deprec + Capx = PPEnet (EOP) 73.913 (7.39) 20.435 86.957 86.957 (8.70) 21.739 100.000 0.100 2 100.000 1 12.000 Solve for this Project as Sales Growth Income Statement Sales COGS Depreciation SGA EBIT Y-1 20X-1 86.957 60.000 8.182 15.000 3.775 YO 200 100.000 70.000 8.696 17.000 4.304 % 0.150 3 5 0.170 20X1 115.000 4 6 19.550 % Sales ro * BOP Debt 1.500 Interest Tax NI 1.350 0.849 1.576 1.500 0.982 1.823 0.075 7 8 EOY -1 20X-1 Project as % Equity Roll Forward Eqs Item EBOP + NI - Div + PIC (- PIC for buyback) = EEOP EOY O 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 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 EOY O 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 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