Use the following excel (info) to create an excel model that computes the Termination Value (TV) of the project at the end of the project and answer the questions below. This is a 10-year project. All long-term assets are purchased at year 0 and sold in the first days of year 11; however, the proceeds (and any taxes associated with them) are only received at the end of year 11 (considered the end of the project for easiness of NPV calculations and modeling in excel). The excel model must follow the format in CoCoMix. The excel spreadsheet must have 3 areas: Input area; working area; and results area. The excel file provided here is already set up that way. You just have to create the formula in the respective cells. Everything in the results area must be cell reference (no inputs by hand but only by referencing cells in the input or working areas). Make sure you fill out the cells in the shaded area (working area) using formulas and referencing to data in the input area. The results area of the model is the last part of the excel. Click here for the excel file for this problem. Round to nearest dollar; do not enter decimals; do not enter the $ symbol. For example, if your result is $237.85 then enter 238 What is the remaining book value of the building in year 10? A 2. What is the remaining book value of equipment at the end of year 10? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 3. What is the tax effect of selling the equipment at the end of the project? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 4. What is the tax effect of selling the building at the end of project? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not @se the + sign). For example, if your result is $4,562.85 then enter 4,563 A 5. What is the recovery of the - investments in working capital (net proceeds from selling the working capital) at end of prioject? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 6. What is the $4,562.85 then enter 4,563 A 5. What is the recovery of the investments in working capital (net proceeds from selling the working capital) at end of prioject? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 6. What is the - termination value of the project at time 11? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 - 63-year MACRS class 5-year MACRS class 7-year MACRS class 9 15-year MACRS class 10 11 INPUTS 12 13 MACRS method for new investments 14 Variable costs-CGS 15 Discount rate 16 Tax rate 17 ITC 18 start ap cest 19 20 21 22 Sales growth 23 Cash as % of sales 24 Accounts Receivable 25 Investeries as % of sales 26 Curvet liab as % of sales 27 sed part of CGS 28 sed part of Selling & Admen esp 29 Varsible part of S&A expenses 30 New investment 31 32 33 34 35 Sales for Best year 36 Overhead expenditures 37 dass 38 Equipment (7 year clan) Computers/IT 3-year dass) Building (15 year class) Special devices for beverage 0 yr class) S 60% 12% 20% 20 Percentage for depreciation under MACRS from table 74 (in study.net package) 1 2 3 5 0.3333 0.4445 0.1481 0.3200 0.1920 0,2000 0.1428 0.050 Year 0 Casti 400 200 600 50 85 Year 1 3% 7% 8% 4% 500 1,000 0 3.000 6.00 3% 0.2449 0.1749 0.1250 0.0892 0.0892 0.0892 0.0448 0.095 0.0855 Year 2 5% 2% 6% 6% 7% 540 1,349 6.00 35 Year 3 10% 4% 3% 8% 7% 500 1,000 8% 0.0741 0.1152 0.1152 0.0576 *.** 3% 0.0770 0.0693 0.0623 0.0590 0.0590 0.0591 Year Year Year 4 5 10% 3% 5% 6% 676 6 10% 3% 6% 6% 4% 6% 500 600 600 1,000 1,000 1,000 1,000 25% 25% 25% 25% 5% 2% 3% 5% 6% 500 0.00 3% 9.20 3% Year Year Year 7 5% 3% 6% 5% 0.00 3% 6.00 3% 25 2% 3% 3% 6% 6% 5% 5% 65 65 600 600 1,000 1,000 25% 25% 9.00 3% 9 8.00 3% 0.0590 Year 10 4% 3% 6% 3% 6% 600 1,000 25% 10 0.00 11-16 0.0295 Year 10 Selling Price 200 30 150 Work Space/Area: Estimated gross sales Erosion of sales Erosion of sales Sales net of erosion effects Working capital Chg. in Net Working Capital 8 9 50 RESULTS/FINAL REPORT AREA: 51 52 CASH FLOWS/YEAR 53 54 Initial Outlay: 65 66 67 68 69 Working Capital 70 71 1. Cash flows from Lovestments 12377 74 75 10 10 10 10