8 Using the percent of sales method, and assuming 20 growth in sales, no change in interest expense or debt levels, a 35 tax rate, and a 65 payout ratio, calculate the following Using the designated cells on the provided Excel sheet and the Prob 7 tab, calculate the ratios for each income statement and balance sheet item with the percent of sales method ( ) Show all calculations in the provided cells on the Excel sheet (3pts) Use the provided Excel sheet to undertake pro forma analysis to determine each income statement and balance sheet item for 2021 with the percent of sales method Show all calculations in the provided cells on the Excel sheet (4pts) Use the provided Excel sheet to calculate in the Other Data the Dividends and Retained Earnings Show all calculations in the provided cells on the Excel sheet (3pts) Adjust the calculation for stockholders equity as needed on the provided Excel template (2pts) In solving for the 2021 shareholders equity, what components do you use to get your pro forma stockholders equity number and why do any adjustments need or not need to be made Type your explanation as to why you made those calculations below (2pts) Use the provided Excel sheet to calculate the Net New Financing in Other Data Show all calculations in the provided cells on the Excel sheet (2pts) Interpret the Net New Financing number you calculate What does this mean for the company Will they need to get additional financing or not Type out your answer directly below (2pts) Problem 7 Billy's Burgers Figures in $ millions Income Statement 2020 Ratios 2021 Balance Sheet 2020 Ratios 2021 Net Sales 246 0 Assets 187 0 8 0 21 0 Costs exc Dep EBITDA Depreciation EBIT Interest 59 0 17 2 Cash Accts Rec Inventories Total Current Assets Net PP E Total Assets 23 0 52 0 41 8 12 0 145 0 Pretax Income 29 8 197 0 Taxes 10 4 Net Income 19 4 18 0 Other Data 82 0 Dividends 100 0 Liabilities and Equity Accts Payable Long Term Debt Total Liabilities Total Stockholders' Equity Total Liabilities and Equity Retained Earnings 97 0 Net New Financing 197 0 Problem 7 Billy's Burgers Figures in $ millions Income Statement 2020 Ratios 2021 Balance Sheet 2020 Ratios 2021 Net Sales 246 0 Assets 187 0 8 0 21 0 Costs exc Dep EBITDA Depreciation EBIT Interest 59 0 17 2 Cash Accts Rec Inventories Total Current Assets Net PP E Total Assets 23 0 52 0 41 8 12 0 145 0 Pretax Income 29 8 197 0 Taxes 10 4 Net Income 19 4 18 0 Other Data 82 0 Dividends 100 0 Liabilities and Equity Accts Payable Long Term Debt Total Liabilities Total Stockholders' Equity Total Liabilities and Equity Retained Earnings 97 0 Net New Financing 197 0

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 13, 2024

8. Using the percent of sales method, and assuming 20% growth in sales, no change in interest expense or debt levels, a 35% tax rate,

8. Using the percent of sales method, and assuming 20% growth in sales, no change in interest expense or debt levels, a 35% tax rate, and a 65% payout ratio, calculate the following:

image text in transcribed

Using the designated cells on the provided Excel sheet and the Prob 7 tab, calculate the ratios for each income statement and balance sheet item with the percent of sales method (%). Show all calculations in the provided cells on the Excel sheet. (3pts)

Use the provided Excel sheet to undertake pro forma analysis to determine each income statement and balance sheet item for 2021 with the percent of sales method. Show all calculations in the provided cells on the Excel sheet. (4pts)

Use the provided Excel sheet to calculate in the Other Data the Dividends and Retained Earnings. Show all calculations in the provided cells on the Excel sheet. (3pts)

Adjust the calculation for stockholders equity as needed on the provided Excel template. (2pts)

In solving for the 2021 shareholders equity, what components do you use to get your pro forma stockholders equity number and why do any adjustments need or not need to be made? Type your explanation as to why you made those calculations below. (2pts)

Use the provided Excel sheet to calculate the Net New Financing in Other Data. Show all calculations in the provided cells on the Excel sheet. (2pts)

Interpret the Net New Financing number you calculate. What does this mean for the company? Will they need to get additional financing or not? Type out your answer directly below. (2pts)

Problem 7: Billy's Burgers Figures in $ millions Income Statement 2020 Ratios 2021 Balance Sheet 2020 Ratios 2021 Net Sales 246.0 Assets 187.0 8.0 21.0 Costs exc. Dep. EBITDA Depreciation EBIT Interest 59.0 17.2 Cash Accts. Rec. Inventories Total Current Assets Net PP&E Total Assets 23.0 52.0 41.8 12.0 145.0 Pretax Income 29.8 197.0 Taxes 10.4 Net Income 19.4 18.0 Other Data: 82.0 Dividends 100.0 Liabilities and Equity Accts. Payable Long-Term Debt Total Liabilities Total Stockholders' Equity Total Liabilities and Equity Retained Earnings 97.0 Net New Financing 197.0 Problem 7: Billy's Burgers Figures in $ millions Income Statement 2020 Ratios 2021 Balance Sheet 2020 Ratios 2021 Net Sales 246.0 Assets 187.0 8.0 21.0 Costs exc. Dep. EBITDA Depreciation EBIT Interest 59.0 17.2 Cash Accts. Rec. Inventories Total Current Assets Net PP&E Total Assets 23.0 52.0 41.8 12.0 145.0 Pretax Income 29.8 197.0 Taxes 10.4 Net Income 19.4 18.0 Other Data: 82.0 Dividends 100.0 Liabilities and Equity Accts. Payable Long-Term Debt Total Liabilities Total Stockholders' Equity Total Liabilities and Equity Retained Earnings 97.0 Net New Financing 197.0