Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please check my answers and let me know which one I did wrong...Thanks! A B E H Hints for Working on this Assignment This exercises
Please check my answers and let me know which one I did wrong...Thanks!
A B E H Hints for Working on this Assignment 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. Relationship Notes Revenues is driven by the growth rate Business Driver Accounting Business Gross Profits is calculated as Revenues multiplied by Gross Margin OpEx is calculated as Revenues multiplied by OpEx Percent Revenues EBIT is calculated as Gross Profits minus Operating Expenses (OpEx) Driver Business Driver Accounting Business Driver Accounting Business Interest Income is calculated as Interest Rate multiplied by prior year Cash (as there is no Debt) Pretax Income is calculated as EBIT plus Net Interest Income (Expenses) Taxes is calculated as Pretax Profits multiplied by Tax Rate Driver Accounting Cash is previous year Cash plus Increase [Decrease) in Cash Accounting Business Driver 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. Accounting Business Accour 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 Driver Accounting 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 30 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 The exercise is designed such that an error early in the assignment will not adversely impact later grading. 54 55 2020 2021 2022E 56 Income Statement 57 Revenues 11,000 58 Growth Rate 10% 59 Cost of Revenues 60 Gross Profits 61 Grass 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 76 PP&E 77 Accounts Payable 78 79 Equity 80 Days of Sales Outstanding (DSO) Days of Payables Outstanding (DPO) 10,000 6,000 4,000 D 1500 15% 4,500 50 5% 4,450 1,335 30% 3,115 1,000 1,500 55 15,000 1,000 61 12,500 6,710 4,290 0 1700 15% 2,590 50 5% 2,540 737 29% 1,803 2,500 1,600 53 14,000 1,200 65 15,055 12,100 10% 4,598 4,719 39% 1,870 15% 2,849 125 5% 2,724 789.95 29% 1934 4,100 1,760 53 13,000 569 65 16,858 K L M N 0 P Q R 81 Hints for Working on the Cash Flow Statement 82 83 Cash Flow Statements always aggregate (sum up) all the Inflows and Outflows of Cash Flow during a period. Inflows of Cash are positive numbers and Outflows of Cash are negative numbers on the Cash Flow Statement. To help you, we show the Outflows in italics. 84 85 You should remember that Increases in Assets, actually decrease Cash so you are subtracting an increase. In contrast, increases in Liabilities, Debt and Equity increase Cash so you are adding an increase. 86 87 Capital Expenditures (CapEx) are investments of Cash and an Outflow of Cash on the Cash Flow Statement. 88 As an investment, CapEx isn't less than zero, but it is shown as a negative number because it is an Outflow. Dividends are an Outflow of Cash and also shown as a negative number, but Dividends are never less than zero 89 90 91 Cash Flow Statement 92 Net Income 1,500 1,400 1,300 Accounting Accounting 93 + Depreciation 1,500 1,500 1,500 94 - Increase in Accounts Receivable 100 160 Business 95 + Increase in Accounts Payable 200 Accounting 96 - Capital Expenditures (CapEx) (1,000) Accounting 97 - Dividends (560) Accounting 98 Payout Ratio 31% Business 99 = Increase (Decrease) in Cash Accounting 100 (800) (600) 19% 1,600 1,600 190 (531) (1,000) (404) 31% 1,600 The company uses 10-year straight line depreciation (10% a year for 10 years beginning year after the investment) Minus the increase in Accounts Receivable (the Accounts Receivable in the current year minus that in the previous year) Increase in Accounts Payable (the Accounts Payable in the current year minus that in the previous year) Dividends is the negative of the Net Income multiplied by Payout Ratio A B E H Hints for Working on this Assignment 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. Relationship Notes Revenues is driven by the growth rate Business Driver Accounting Business Gross Profits is calculated as Revenues multiplied by Gross Margin OpEx is calculated as Revenues multiplied by OpEx Percent Revenues EBIT is calculated as Gross Profits minus Operating Expenses (OpEx) Driver Business Driver Accounting Business Driver Accounting Business Interest Income is calculated as Interest Rate multiplied by prior year Cash (as there is no Debt) Pretax Income is calculated as EBIT plus Net Interest Income (Expenses) Taxes is calculated as Pretax Profits multiplied by Tax Rate Driver Accounting Cash is previous year Cash plus Increase [Decrease) in Cash Accounting Business Driver 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. Accounting Business Accour 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 Driver Accounting 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 30 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 The exercise is designed such that an error early in the assignment will not adversely impact later grading. 54 55 2020 2021 2022E 56 Income Statement 57 Revenues 11,000 58 Growth Rate 10% 59 Cost of Revenues 60 Gross Profits 61 Grass 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 76 PP&E 77 Accounts Payable 78 79 Equity 80 Days of Sales Outstanding (DSO) Days of Payables Outstanding (DPO) 10,000 6,000 4,000 D 1500 15% 4,500 50 5% 4,450 1,335 30% 3,115 1,000 1,500 55 15,000 1,000 61 12,500 6,710 4,290 0 1700 15% 2,590 50 5% 2,540 737 29% 1,803 2,500 1,600 53 14,000 1,200 65 15,055 12,100 10% 4,598 4,719 39% 1,870 15% 2,849 125 5% 2,724 789.95 29% 1934 4,100 1,760 53 13,000 569 65 16,858 K L M N 0 P Q R 81 Hints for Working on the Cash Flow Statement 82 83 Cash Flow Statements always aggregate (sum up) all the Inflows and Outflows of Cash Flow during a period. Inflows of Cash are positive numbers and Outflows of Cash are negative numbers on the Cash Flow Statement. To help you, we show the Outflows in italics. 84 85 You should remember that Increases in Assets, actually decrease Cash so you are subtracting an increase. In contrast, increases in Liabilities, Debt and Equity increase Cash so you are adding an increase. 86 87 Capital Expenditures (CapEx) are investments of Cash and an Outflow of Cash on the Cash Flow Statement. 88 As an investment, CapEx isn't less than zero, but it is shown as a negative number because it is an Outflow. Dividends are an Outflow of Cash and also shown as a negative number, but Dividends are never less than zero 89 90 91 Cash Flow Statement 92 Net Income 1,500 1,400 1,300 Accounting Accounting 93 + Depreciation 1,500 1,500 1,500 94 - Increase in Accounts Receivable 100 160 Business 95 + Increase in Accounts Payable 200 Accounting 96 - Capital Expenditures (CapEx) (1,000) Accounting 97 - Dividends (560) Accounting 98 Payout Ratio 31% Business 99 = Increase (Decrease) in Cash Accounting 100 (800) (600) 19% 1,600 1,600 190 (531) (1,000) (404) 31% 1,600 The company uses 10-year straight line depreciation (10% a year for 10 years beginning year after the investment) Minus the increase in Accounts Receivable (the Accounts Receivable in the current year minus that in the previous year) Increase in Accounts Payable (the Accounts Payable in the current year minus that in the previous year) Dividends is the negative of the Net Income multiplied by Payout RatioStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started