Excel 4 - Pro Forma Financial Statements for CoolTreat Popsicles. Seallocat Popsicles is a small Southern Californian manufacturer of gourmet popsicles. The company's products have become increasing popular over time so the firm is considering borrowing money to expand capacity. Your assignment is to put together a simple pro forma financial statement to evaluate borrowing needs and to provide information about the financial feasibility of this expansion. Submit a one-page memo providing your conclusions along with your Excel worksheet Financial Facts for 2018 - Before the Expansion I Annual Revenue = $20 million COGS = 60% of revenue Fixed Expenses = $4 million Assets = $20 million; all financed through equity Corporate Tax Rate = 25% All earnings paid as dividends The Expansion in 2019 At the start of 2019, the firm borrows $10 million to buy a new factory. Assume that the factory is available for production right away. The loan charges 8% simple interest. Revenue for 2019 jumps to $30 million and will increase by 15% in each subsequent year. COGS stay at the same percentage. Fixed costs increase to $4.5m and remain at that level. Part of the loan will be paid off each year from retained earnings. For the purposes of this exercise, treat the interest payments as being determined by the end-of-year liabilities and earnings in the current year apply towards end-of-year liabilities. Question 1: If equity investors want a dividend of 80% of earnings, how long will it take for the loan to be paid off? Question 2: If we want the loan paid off by 2022, what is the highest payout rate we can have? Things to think about (but don't include in your memo) a) If it was a fixed-term amortizing loan, what would you be looking for in terms of financial viability? b) How would we evaluate whether the expansion is a good idea or not? c) If we weren't sure about how big of an increase we'll get lo revenue in 2019 (maybe more, maybe leeel hou mine the fort our decision? Excel 4 - Pro Forma Financial Statements for CoolTreat Popsicles. Seallocat Popsicles is a small Southern Californian manufacturer of gourmet popsicles. The company's products have become increasing popular over time so the firm is considering borrowing money to expand capacity. Your assignment is to put together a simple pro forma financial statement to evaluate borrowing needs and to provide information about the financial feasibility of this expansion. Submit a one-page memo providing your conclusions along with your Excel worksheet Financial Facts for 2018 - Before the Expansion I Annual Revenue = $20 million COGS = 60% of revenue Fixed Expenses = $4 million Assets = $20 million; all financed through equity Corporate Tax Rate = 25% All earnings paid as dividends The Expansion in 2019 At the start of 2019, the firm borrows $10 million to buy a new factory. Assume that the factory is available for production right away. The loan charges 8% simple interest. Revenue for 2019 jumps to $30 million and will increase by 15% in each subsequent year. COGS stay at the same percentage. Fixed costs increase to $4.5m and remain at that level. Part of the loan will be paid off each year from retained earnings. For the purposes of this exercise, treat the interest payments as being determined by the end-of-year liabilities and earnings in the current year apply towards end-of-year liabilities. Question 1: If equity investors want a dividend of 80% of earnings, how long will it take for the loan to be paid off? Question 2: If we want the loan paid off by 2022, what is the highest payout rate we can have? Things to think about (but don't include in your memo) a) If it was a fixed-term amortizing loan, what would you be looking for in terms of financial viability? b) How would we evaluate whether the expansion is a good idea or not? c) If we weren't sure about how big of an increase we'll get lo revenue in 2019 (maybe more, maybe leeel hou mine the fort our decision
