Please complete tge customer numbers, income statment, balance sheet and statement of cash flows with tge provided information

Exercise: This is a common situation in the real world. Assume that you are an analyst covering a "recurring revenue" type firm. Recurring revenue firms typically earn revenues from customers over multiple periods. For instance, cable companies pay to acquire new customers, and the customers pay for services over a recurring time period. You will notice that this format is cost-driven, meaning that certain costs drive revenue! As an analyst, you want to know a) the financing needs for the firm, and b) how much the firm is worth. "Note that we would typically build this model on a monthly basis, but this exercise gets to the concept without you using Excel. As a "star analyst," you have used your skills to observe the financial metrics of the firm's competitors, industry trends and macroeconomic trends. Also, you have listened to and critiqued the firm's latest quarterly conference call. You incorporated all of this information to generate the following assumptions: 500 Assumptions 2020 2021 2022 ARPU - Average Revenue per User (annual, beginning customers) $ 1,200 $ 1,500 $ 1,800 Customer Assumptions Customer Acquisition Expense (per new customer) 400 $ 450 S 450 Customer Churn (% of beginning customers who quit service) 10% 20% 20% Cost of Goods Sold per User (use beginning customers) 400 $ 500 $ 600 SG&A expense: Total Customer Acquisition Marketing Expense $ 120,000 $ 150,000 $ 180,000 Other SG&A expenses $ 80,000 $ 100,000 $ 100,000 Capital Expenditures (Capex) "Success-Based" Capex (capital expenditures / new customer) $ 800 S 600 S "Maintenance" Capex Equals Depreciation Expense Depreciation (% of lagged fixed assets) 10% 10% Interest Expense (% of lagged NP and LTD) 10% 10% Average Tax Rate 25% 25% Required Cash Equal to Accounts Payable Days Sales in Inventory Days Sales in Receivables 30 Days Payables Outstanding Capital Structure Decisions 10% Dividend Payout Ratio Planned LTD (year end) 500,000 600,000 700,000 2,750,000 Equity Account (Par+APIC) 2,750,000 2,600,000 0% 25% A) Use the "Total Customer Acquisition Marketing Expense" and the per new customer acquisition cost to build the number of customers per year. Use the information above to complete the table below for gross additional customers and customer churn (beginning customers who quit service). I provided the ending customers of 2022 for you to check your work! (Note, it is acceptable to have decimals for expected customer numbers). Projected 2021 2022 2020 650.00 Customer Numbers Beginning Customers Gross Additions Churn Ending Customers Actual 2019 500.00 200.00 (50.00) 650.00 1,233.07 B) Build the Income Statement projections. Projected 2021 2020 2022 Income Statement Revenue Cost of Sales Gross Profits Actual 2019 500,000 (150,000) 350,000 Operating Expenses Depreciation SG&A Total Opex Interest Expense (180,000) (180,000) (360,000)| (25,000) (35,000) 8,750 (26,250)| Earnings Before Tax Tax Expense Net Income 61,140 265,775 496,063 C) Build the Balance Sheet projections. Actual 2019 Projected 2021 2020 2022 12,329 30,000 54,795 97,123 2,000,000 2,097,123 Balance Sheet Assets Current Assets Required Cash Inventory Accounts Receivable Total Current Assets Equipment, net Total Assets Liabilities & Owners' Equity Current Liabilities Notes Payable Accounts Payable Total Current Liabilities Long Term Debt Total Liabilities Common Stock Par and APIC Retained Earnings Total Stockholders' Equity Total Liabilities & Owners' Equity 84,795 12,329 97,123 300,000 397,123 500,000 600,000 700,000 2,750,000 2,750,000 2,600,000 2,700,000 (1,000,000) 1,700,000 L 2,097,123 D) Build the Statement of Cash Flows projections. Make certain that your ending cash balance matches your Balance Sheet projections! Here, think about sources and uses of cash. I provided the ending cash balance for you to check your work! Projected 2021 2020 2022 Statement of Cash Flows Cash Flow from Operating Activities Net income Adjustments to Net Income Depreciation & Amortization Accounts receivable Inventory Accounts payable Net Cash from Operating Activities Cash Flow from Investing Activities Additions to equipment Net Cash from Investing Activities Cash Flow from Financing Activities Proceeds from notes payable Proceeds from Common stock Proceeds from long-term debt Dividends paid Net Cash from Financing Activities Net Increase in Cash Cash at the Beginning of the Period Cash at the End of the Period 12,329 51,353 E) Given the above assumptions, calculate the Cash Flow from Assets (CFFA) and the Cash Flow to Equity (CFE) for 2020, 2021 and 2022 F) We want to know the value of this projection plan to shareholders. Compute the Present Value of the Cash Flows to Equity during the Projected Period (assuming that they come at year's end). Assume that the required rate of return for equity holders is 20%. Hint: You should only have three cash flows here! G) If your management team believes that the enterprise value of the target firm will be 8 times the 2022 EBITDA value at the end of 2022, calculate the total equity value of this opportunity. Recall that EV = Market Value of Equity + Book Value of Debt - Excess Cash. Here, you can assume that you have no Excess Cash (all cash is required). Therefore, you simply multiply the 2022 EBITDA by 8, and then subtract the Book Value of Notes Payable and LTD to calculate the Market Value of Equity. (Assume the same 20% discount rate, and don't forget to include the present value of the projected period calculated in part F). 1) What is the projected Depreciation & Amortization Expense for 2022? a) 200,000 b) 244,000 c) 220,000 2) What is the projected Interest Expense for 2021? a) 56,634 b) 52,416 c) 50,000 3) What is the projected Retained Earnings for 2022? a) -327,615 b) 177,022 c) -1,822,978 4) What is the projected Equipment, net account in 2021? a) 2,001,400 b) 2,864,000 c) 2,440,000 5) What is the projected Accounts Payable account in 2022? a) 154,060 b) 51,353 c) 52,220 6) What is the projected proceeds from Common Stock in 2022 (difference between 2021 to 2022)? a) 150,000 b) -150,000 c) 2,600,000 7) What is the projected Dividends paid in 2021? a) 239,197 b) 265,775 c) 26,577 8) What is the projected Capex in 2022? a) 444,000 b) 2,640,000 c) 200,000 9) What is the projected investment in Inventory between 2021 and 2022 (hint: look at the change)? a) 624,800 b) 124.960 c) 36,460 10) What is the total equity value of this opportunity your answer for part G above)? a) 4,205,465 b) 135,364 - c) 7,033,134