Please show the excel functions that you use to make the calculations
B D E F H 1 K L 2019 Balance Sheet Items Cash Receivables Inventories Total Current Assets Net Fixed Assets $3.5 $26.0 $58.0 $87.5 $35.0 A/P N/P LOC Accruals Total Current Liabilities Mortgage Loan Common Stock Retained Earnings Total Liabilities and Equity $9.0 $18.0 $0.0 $8.5 $35.5 $6.0 $15.0 $66,0 $122.5 Total Assets $122.5 Sales (2019) Expected Sales (2020) Assets (2019) Spon Liabilities (2019) Profit Margin Payout Ratio $350.0 $420.0 $122.5 $17.5 3% 40% Change in Sales $70.0 Problem 4: Forecasted Statements and Ratios Kaprielyan Computers makes bulk purchases of generic office computers, stocks them in conveninetly located warehouses, ships them to its chain of retail stores, and has a staff to advise customers and help them set up their new computers. Their year-end 2019 balance sheet is on the left (in millions $). Sales for 2019 were $350mm and net income for the year was $10.5mm. Upton paid dividends of $4.2 million to common stockholders. Its tax rate was 40%, and it operated at full capacity. Assume that all ratios will remain constant in 2020. a) Suppose that sales are projected to increase by $70mm during 2020. What is Upton's projected external capital requirements? In other words, find AFN. b) What is Kaprielyan's self-supporting growth rate? c) Forecast Kaprielyan's balance sheet for year-end 2020 using a percent of sales method. Assume that all additional external capital is raised as a line of credit at the end of the year and no interest is paid during 2020. Assume that their profit margins and payout ratios are constant. Hint: You don't have to construct the entire income statement, since you aren't paying interest on the LOC during 2020. Therefore, margins will remain the same. This is a good example of only forecasting what we need. You'll need to use the information at the top to set up your own 2019 below, find each item as a percentage of sales and then create a pro-forma 2020 balance sheet for the same items before and after financing. Note that, before you take into account financing needs, your 2020 balance sheet will not balance! This is the surplus or deficit. By taking into account your funding needs, you'll get things to balance. B D E F H 1 K L 2019 Balance Sheet Items Cash Receivables Inventories Total Current Assets Net Fixed Assets $3.5 $26.0 $58.0 $87.5 $35.0 A/P N/P LOC Accruals Total Current Liabilities Mortgage Loan Common Stock Retained Earnings Total Liabilities and Equity $9.0 $18.0 $0.0 $8.5 $35.5 $6.0 $15.0 $66,0 $122.5 Total Assets $122.5 Sales (2019) Expected Sales (2020) Assets (2019) Spon Liabilities (2019) Profit Margin Payout Ratio $350.0 $420.0 $122.5 $17.5 3% 40% Change in Sales $70.0 Problem 4: Forecasted Statements and Ratios Kaprielyan Computers makes bulk purchases of generic office computers, stocks them in conveninetly located warehouses, ships them to its chain of retail stores, and has a staff to advise customers and help them set up their new computers. Their year-end 2019 balance sheet is on the left (in millions $). Sales for 2019 were $350mm and net income for the year was $10.5mm. Upton paid dividends of $4.2 million to common stockholders. Its tax rate was 40%, and it operated at full capacity. Assume that all ratios will remain constant in 2020. a) Suppose that sales are projected to increase by $70mm during 2020. What is Upton's projected external capital requirements? In other words, find AFN. b) What is Kaprielyan's self-supporting growth rate? c) Forecast Kaprielyan's balance sheet for year-end 2020 using a percent of sales method. Assume that all additional external capital is raised as a line of credit at the end of the year and no interest is paid during 2020. Assume that their profit margins and payout ratios are constant. Hint: You don't have to construct the entire income statement, since you aren't paying interest on the LOC during 2020. Therefore, margins will remain the same. This is a good example of only forecasting what we need. You'll need to use the information at the top to set up your own 2019 below, find each item as a percentage of sales and then create a pro-forma 2020 balance sheet for the same items before and after financing. Note that, before you take into account financing needs, your 2020 balance sheet will not balance! This is the surplus or deficit. By taking into account your funding needs, you'll get things to balance
