39 Hints for Working on this Assignment 40 This exercises is a bit like solving a puzzle. 41 First, you do the easy formulas to fill in the box. 42 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. 43 The '=11628*1.1' in cell F57 calculates the 2020 Revenues based on the 2019 Revenues and the 2019 Growth Rate. 44 We did this for you in the Revenue Box without shading. 45 Again, solving the puzzle we fill in the blanks we know for sure. 46 So, your next steps it calculate the historical Gross Profits (in D57 and E57) and the historical Gross Margin (in D58 and E58). 47 Once you know these answers, you can apply the historical model to the forecast. 48 This means you will forecast the Gross Margin and use that information to forecast the Gross Profits. 49 Easy, right? 50 Now you have learned how to build this model and can proceed with the rest of the exercise. 51 The '=F57-F60' in cell F59 calculates the 2020 Gross Profits based on the 2020 forecast Gross Margin and the 2020 forecast Revenues. 52 53 Errors in the model will turn red. The exercise is designed such that an error early in the assignment will not adversely impact later grading. 54 55 2018 2019 2020E Relationship Notes 56 Income Statement 57 Revenues 10,571 11,628 12,791 Business Revenues is driven by the growth rate 58 Growth Rate 10% 10% Driver 59 Cost of Revenues 6,343 7,093 7,802 Accounting 60 Gross Profits 4,228 4,535 4,988 Business Gross Profits is calculated as Revenues multiplied by Gross Margin 61 Gross Margin 40%) 39% 39% Driver 62 Operating Expenses (OpEx) 1500 1700 1,919 Business OpEx is calculated as Revenues multiplied by OpEx Percent Revenues 63 OpEx Percent of Revenues 14% 15% 15% Driver 64 EBIT 2,728 2,835 3,070 Accounting EBIT is calculated as Gross Profits minus Operating Expenses (OpEx) 65 Net Interest Income (Expense) 97 97 NA Interest Income is calculated as Interest Rate multiplied by Cash (as there is no Debt) 66 Interest Rate 5% 5% 5% Driver 67 Pretax Income 2,825 2,932 Accounting Pretax Income is calculated as EBIT plus Net Interest Income (Expenses) 68 Taxes 848 850 Business Taxes is calculated as Pretax Profits multiplied by Tax Rate 69 Tax Rate 30% 29% 29% Driver 70 Net Income 2,852 2,932 Accounting 71 72 Balance Sheet 73 Cash 1,942 Accounting Cash is previous year Cash plus Increase (Decrease) in Cash 74 Accounts Receivable 1,500 1,600 1,752 Business Accounts Receivable is calculated as Revenues multiplied by Days of Sales Outstanding (DSO) /365 75 Days of Sales Outstanding (DSO) 52 50 50 Driver Days of Sales Outstanding (DSO) is calculated as Accounts Receivable / Revenues * 365 76 PP&E 15,867 13,180 10,393 Accounting 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. 77 Accounts Payable 1,000 1,200 1320 Business Accounts Payable is calculated as Cost of Revenues multiplied by Days of Payables Outstanding (DPO)/365 78 Days of Payables Outstanding (DPL 58 62 62 Driver Days of Payables Outstanding (DPO) is calculated as Accounts Payable / Cost of Revenues * 365 79 Equity 12,500 14,259 Accounting Equity in a year is calculated as Equity in the previous year plus Net Income minus Dividends 39 Hints for Working on this Assignment 40 This exercises is a bit like solving a puzzle. 41 First, you do the easy formulas to fill in the box. 42 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. 43 The '=11628*1.1' in cell F57 calculates the 2020 Revenues based on the 2019 Revenues and the 2019 Growth Rate. 44 We did this for you in the Revenue Box without shading. 45 Again, solving the puzzle we fill in the blanks we know for sure. 46 So, your next steps it calculate the historical Gross Profits (in D57 and E57) and the historical Gross Margin (in D58 and E58). 47 Once you know these answers, you can apply the historical model to the forecast. 48 This means you will forecast the Gross Margin and use that information to forecast the Gross Profits. 49 Easy, right? 50 Now you have learned how to build this model and can proceed with the rest of the exercise. 51 The '=F57-F60' in cell F59 calculates the 2020 Gross Profits based on the 2020 forecast Gross Margin and the 2020 forecast Revenues. 52 53 Errors in the model will turn red. The exercise is designed such that an error early in the assignment will not adversely impact later grading. 54 55 2018 2019 2020E Relationship Notes 56 Income Statement 57 Revenues 10,571 11,628 12,791 Business Revenues is driven by the growth rate 58 Growth Rate 10% 10% Driver 59 Cost of Revenues 6,343 7,093 7,802 Accounting 60 Gross Profits 4,228 4,535 4,988 Business Gross Profits is calculated as Revenues multiplied by Gross Margin 61 Gross Margin 40%) 39% 39% Driver 62 Operating Expenses (OpEx) 1500 1700 1,919 Business OpEx is calculated as Revenues multiplied by OpEx Percent Revenues 63 OpEx Percent of Revenues 14% 15% 15% Driver 64 EBIT 2,728 2,835 3,070 Accounting EBIT is calculated as Gross Profits minus Operating Expenses (OpEx) 65 Net Interest Income (Expense) 97 97 NA Interest Income is calculated as Interest Rate multiplied by Cash (as there is no Debt) 66 Interest Rate 5% 5% 5% Driver 67 Pretax Income 2,825 2,932 Accounting Pretax Income is calculated as EBIT plus Net Interest Income (Expenses) 68 Taxes 848 850 Business Taxes is calculated as Pretax Profits multiplied by Tax Rate 69 Tax Rate 30% 29% 29% Driver 70 Net Income 2,852 2,932 Accounting 71 72 Balance Sheet 73 Cash 1,942 Accounting Cash is previous year Cash plus Increase (Decrease) in Cash 74 Accounts Receivable 1,500 1,600 1,752 Business Accounts Receivable is calculated as Revenues multiplied by Days of Sales Outstanding (DSO) /365 75 Days of Sales Outstanding (DSO) 52 50 50 Driver Days of Sales Outstanding (DSO) is calculated as Accounts Receivable / Revenues * 365 76 PP&E 15,867 13,180 10,393 Accounting 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. 77 Accounts Payable 1,000 1,200 1320 Business Accounts Payable is calculated as Cost of Revenues multiplied by Days of Payables Outstanding (DPO)/365 78 Days of Payables Outstanding (DPL 58 62 62 Driver Days of Payables Outstanding (DPO) is calculated as Accounts Payable / Cost of Revenues * 365 79 Equity 12,500 14,259 Accounting Equity in a year is calculated as Equity in the previous year plus Net Income minus Dividends