Please write the formulas. ONLY DO THIS IF YOU KNOW HOW TO

This exercises is a bit like solving a puzzle. First, you do the easy formulas to fill in the box. So, first solve for the Revenues in the forecast year in column F by growing the prior year by the growth rate provided in red. The formula in cell F57 should calculate the 2022 Revenues based on the 2021 Revenues and the 2021 Growth Rate. We did this for you in the Revenue Box without shading. Again, solving the puzzle we fill in the blanks we know for sure. So, your next steps it calculate the historical Gross Profits (in D57 and E57) and the historical Gross Margin (in D58 and E58). Once you know these answers, you can apply the historical model to the forecast. This means you will forecast the Gross Margin and use that information to forecast the Gross Profits. Easy, right? Now you have learned how to build this model and can proceed with the rest of the exercise. The formula in cell F59 should calculate the 2022 Gross Profits based on the 2022 forecast Gross Margin and the 2022 forecast Revenues. 2020 2021 2022E Relationship Notes 10,000 Business Revenues is driven by the growth rate 11,000 10% 6,710 10% 6,000 Driver Accounting Business Gross Profits is calculated as Revenues multiplied by Gross Margin 0% Driver 1500 1700 OpEx is calculated as Revenues multiplied by OpEx Percent Revenues 0% Business Driver Accounting Business EBIT is calculated as Gross Profits minus Operating Expenses (OpEx) Interest Income is calculated as Interest Rate multiplied by prior year Cash (as there is no Debt) 50 50 5% 5% 5% Driver 55 56 Income Statement 57 Revenues 58 Growth Rate 59 Cost of Revenues 60 Gross Profits 61 Gross Margin 62 Operating Expenses (OpEx) 63 OpEx Percent of Revenues 64 EBIT 65 Net Interest Income (Expense) 66 Interest Rate 67 Pretax Income 68 Taxes 69 Tax Rate 70 Net Income 71 72 Balance Sheet 73 Cash 74 Accounts Receivable 75 Days of Sales Outstanding (DSO) 76 PP&E 77 Accounts Payable 78 Days of Payables Outstanding (DPO) 79 Equity 80 Pretax Income is calculated as EBIT plus Net Interest Income (Expenses) Taxes is calculated as Pretax Profits multiplied by Tax Rate Accounting Business Driver Accounting 0% 1,000 1,500 1,600 15,000 1,000 Accounting Business Driver Accounting Business Driver Accounting Cash is previous year Cash plus Increase (Decrease) in Cash Accounts Receivable is calculated as Revenues multiplied by Days of Sales Outstanding (DSO) / 365 Days of Sales Outstanding (DSO) is calculated as Accounts Receivable / Revenues * 365 PP&E in a year is calculated as PP&E in the previous year plus CapEx in the current year minus Depreciation in the current year. Accounts Payable is calculated as Cost of Revenues multiplied by Days of Payables Outstanding (DPO) / 365 Days of Payables Outstanding (DPO) is calculated as Accounts Payable / Cost of Revenues * 365 Equity in a year is calculated as Equity in the previous year plus Net Income minus Dividends ALERT: Because the value of the Dividends are shown as a negative on the Cash Flow Statement the value should be added to subtract Dividends 1,200 12,500 This exercises is a bit like solving a puzzle. First, you do the easy formulas to fill in the box. So, first solve for the Revenues in the forecast year in column F by growing the prior year by the growth rate provided in red. The formula in cell F57 should calculate the 2022 Revenues based on the 2021 Revenues and the 2021 Growth Rate. We did this for you in the Revenue Box without shading. Again, solving the puzzle we fill in the blanks we know for sure. So, your next steps it calculate the historical Gross Profits (in D57 and E57) and the historical Gross Margin (in D58 and E58). Once you know these answers, you can apply the historical model to the forecast. This means you will forecast the Gross Margin and use that information to forecast the Gross Profits. Easy, right? Now you have learned how to build this model and can proceed with the rest of the exercise. The formula in cell F59 should calculate the 2022 Gross Profits based on the 2022 forecast Gross Margin and the 2022 forecast Revenues. 2020 2021 2022E Relationship Notes 10,000 Business Revenues is driven by the growth rate 11,000 10% 6,710 10% 6,000 Driver Accounting Business Gross Profits is calculated as Revenues multiplied by Gross Margin 0% Driver 1500 1700 OpEx is calculated as Revenues multiplied by OpEx Percent Revenues 0% Business Driver Accounting Business EBIT is calculated as Gross Profits minus Operating Expenses (OpEx) Interest Income is calculated as Interest Rate multiplied by prior year Cash (as there is no Debt) 50 50 5% 5% 5% Driver 55 56 Income Statement 57 Revenues 58 Growth Rate 59 Cost of Revenues 60 Gross Profits 61 Gross Margin 62 Operating Expenses (OpEx) 63 OpEx Percent of Revenues 64 EBIT 65 Net Interest Income (Expense) 66 Interest Rate 67 Pretax Income 68 Taxes 69 Tax Rate 70 Net Income 71 72 Balance Sheet 73 Cash 74 Accounts Receivable 75 Days of Sales Outstanding (DSO) 76 PP&E 77 Accounts Payable 78 Days of Payables Outstanding (DPO) 79 Equity 80 Pretax Income is calculated as EBIT plus Net Interest Income (Expenses) Taxes is calculated as Pretax Profits multiplied by Tax Rate Accounting Business Driver Accounting 0% 1,000 1,500 1,600 15,000 1,000 Accounting Business Driver Accounting Business Driver Accounting Cash is previous year Cash plus Increase (Decrease) in Cash Accounts Receivable is calculated as Revenues multiplied by Days of Sales Outstanding (DSO) / 365 Days of Sales Outstanding (DSO) is calculated as Accounts Receivable / Revenues * 365 PP&E in a year is calculated as PP&E in the previous year plus CapEx in the current year minus Depreciation in the current year. Accounts Payable is calculated as Cost of Revenues multiplied by Days of Payables Outstanding (DPO) / 365 Days of Payables Outstanding (DPO) is calculated as Accounts Payable / Cost of Revenues * 365 Equity in a year is calculated as Equity in the previous year plus Net Income minus Dividends ALERT: Because the value of the Dividends are shown as a negative on the Cash Flow Statement the value should be added to subtract Dividends 1,200 12,500