Spreadsheet Modeling Although capital budgeting can be readily solved with a financial calculator, capital budgeting is usually done with the assistance of personal computers and spreadsheets such as Microsoft Excel. Spreadsheets are heavily used for several reasons. Spreadsheets provide a very effective way of building even complex models. Built-in spreadsheet functions (such as those for finding rates of return) are casy to use. The model's assumptions can be changed and solved easily. Models can be shared with other analysts, and they also help in presenting the results of the analysis. In this example, you are supposed to create a spreadsheet that can be used to solve a capital budgeting problem. What you are asked to do is to create the spreadsheet and answer the corresponding questions. Lawton Enterprises is evaluating a project with the following characteristics: . Fixed capital investment is $2,000,000 The project has an expected six-year life. . The initial investment in net working capital is $200,000. It will be recovered at the end of the project. of the next year's projected sales. The fixed capital is depreciated 30 percent in Year 1,35 percent in Year 2, 20 percent in Year 3, 10 percent in Year 4,5 percent in Year 5, and 0 percent in Year 6. Sales are $1,200,000 in Year 1. They grow at a 25 percent annual rate for the next two years, and then grow at a 10 percent annual rate for the last three years. . Fixed cash operating expenses are $150,000 for Years 1-3 and $130,000 for Years 4 6. Variable cash operating expenses are 40 percent of sales in Year 1, 39 percent of sales in Year 2, and 38 percent in Years 3- Lawton's marginal tax rate is 30 percent. Lawton will sell its fixed capital investments for $150,000 when the project terminates and recapture its cumulative investment in net working capital. Income taxes will be paid on any gains. . The project's required rate of return is 12 percent. . Please note to find CFI, CF2 up to t, you need to calculate the Operating Cash Flow on the project from year I to t. The OCF is defined as OCF-EBIT+D-Taxes. 1. Determine whether this is a profitable investment using the NPV and IRR. 2. Can you map the NPV profile for this project? You need to use Excel to be able to do that. 3. If the tax rate increases to 40 percent and the required rate of return increases to 14 percent, is the project still profitable