E5 B. D G H K Scenario A company is deciding to purchase equipment to produce a new product. Both the equipment and the product have a 10-year life. A pro forma has been created to determine net income and cash flows for the 10 years for which the company believes their will be a market for this product. 4 6 9 10 11 12 13 14 15 16 17 18 19 20 21 22 28 24 Instructions for Pro forma Completed Worksheet The Pro forma has been developed from the assumptions. You only need to calculate: 1. Cash flows in row 15 2. Net present value in B17 3. Internal rate of return in B18 Cash flows in row 15 The purchase price should be a negative number Cash flows per year are net income excluding depreciation expense NPV in B17 Refer the appendix 26A in your textbook on how to calculate this formula. Refer to the example in the workbook. Use the required rate in B3 to calculate the NPV. Use NPV to calculate the cash flows per year. Add the cell that contains the negative value for the purchase price. Function Arguments NP Rate + Value 1 Instructions Proforma Completed Complete the Proforma Pro forma completed Example File Home Insert Draw Page Layout Formulas Data Review View Help Share Calibri 11 ' ' == 23 General Insert - Delete- Format . Z Sort Paste B TV - a A- $ -% -88 - Fit Conditional Formatas Cell Formatting Table Styles Styles Clipboard Font Alignment Number 5 Cells Editing Sentity H22 G . K M N 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 10% 10% 20% 25% 20% 0% 0% -15% B Purchase 1 Project 1 Assumptions price 2 Project Cost 775,000 3 Required rate 8.0% 4 Sales growth 5 Sales 240,000 year 1 6 Cost of goods sold 40.0% of sales 7 Gross profit 8 Operating expenses 15.0% of sales 9 Depreciation expense 10 SL useful life 10 Operating income 11 Interest Expense 3.5% of purchase price 12 Taxes 21.0% of net income 13 Net Income 14 15 Cash Flows 16 17 NPV 18 IRR 19