please provide answers 1-16 for the excel workbook

\begin{tabular}{|l|l} \hline & \multicolumn{1}{|l}{ Coformation/Variables } \\ Company's Other Taxable Income \\ Units Sold \\ Project Life (years) \\ Price per unit \\ Machinery \\ Machinery maintenance costs \\ Depreciable Life \\ Salvage Value \\ Increase in Inventory \\ Increase in Cash Balance \\ Marginal Cost (per unit) \\ Cost of Capital \\ Marginal Tax Rate \end{tabular} Note : Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the XLOOKUP function. In addition, personal taxes are still based on similar tables, and most states also have tax tables like the one above. Capital Budgeting Problem Explained (100 points total) Garca and Martinez manufacture widgets and currently have $3 million in taxable income. The company is considering an expansion, and they've asked you to evaluate the project. The expansion requires the firm to produce 80,000 widgets a year for 6 years, and the company estimates they can sell them for $28 per widget. Garca and Martinez estimate they will need an additional $4,000,000 worth of machinery. The machinery costs $200,000 a year to operate and maintain. The machinery's depreciable life is 8-years, and the company expects to salvage the machinery for $1,000,000 at the end of Year 6 . If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project's life. Similarly, the company will immediately add $75,000 to their cash balance at start-up and maintain that higher cash balance over the project's life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 12%. Calculate the project's NPV by linking to the named variables in Column K. 1. Enter the relevant values for the variables that you use in solving this problem in Column K. Name each cell in Column K that contains a variable. Use the following names: Income, Quantity, Life, Price, Machinery, MaintenanceCost, DepLife, Salvage, Inventory, CashBalance, VariableCost, WACC, and TaxRate (e.g. define the name of cell K4 to be Quantity). When linking cells or writing functions, use these names to reference the cells in your calculations. This makes it easier for others to know what the functions are doing. ( 10 points) 2. Calculate revenues in Row 3. Always refer to the named cells. (5 points) 3. Calculate variable costs in Row 4. Always refer to the named cells. (5 points) 4. Calculate fixed costs in Row 5. Always refer to the named cells. (5 points) 5. Use the information in the previous steps to calculate EBITDA in Row 6. (5 points) 6. Use the INDEX function in Row 7 to calculate depreciation using the depreciation table. (5 points) Some helpful pointers... a. Reference the entire depreciation table in the INDEX function. Use the current project year in Row 2 to determine the correct row for depreciation. Use the depreciable life of the asset to identify the correct column. b. Fun fact: I formatted Row 2 to accept a number (which is why you can reference it in the formula), but Excel displays text with the number to make it more readable as well as functional. If you look in cell D2, only a one is displayed in the formula bar but not the text Year. I did this by using a custom cell format. Here the custom format I use is "Year "0. The text inside the " "tells Excel the text I want to display in front of the number, and the 0 tells Excel to display a whole digit after the text. If I had instead typed 0.0 it would display a whole digit as well as one decimal place (e.g., Year 1.0). c. FYI for calculating after-tax salvage value: If the depreciable life is not equal to the life/years you will own the asset, the book value may not equal zero. d. If done correctly, the Year 1 depreciation should be about $1 million, and the Year 5 and Year 6 will be just over $316,000. 7. Calculate EBIT in Row 8. (5 points) 8. Use that marginal tax rate to calculate taxes owed. (10 points) a. Use the XLOOKUP function on the provided tax table to calculate the marginal tax rate in cell K15. You may use web resources to learn about the function (e.g. httpsi//www.goskills,com/Excel/Resources/Xlookup-in-Excel). If you have trouble with this at first, just type in the rate in K15 and fix it later after completing the other steps below. b. Reference cell K15 (using the name TaxRate) to calculate taxes in each year in Row 9 . 9. Calculate operating cash flow for each period in Row 10. The OCF in Year 1 should be about $1.37 million, and in Year 6 it should be about $1.14 million. ( 5 points) 10. Calculate the change in net working capital for each period in Row 11. In most years it will be zero. (5 points) 11. Calculate the net capital spending for each period in Row 12. Don't forget to pay taxes on the salvage value. In most years net C.S. will be zero. (5 points) 12. Calculate the cash flow from assets for each period in Row 13. CFFA in Year 0 should be - $4.575 million and the Year 6 should be about $2.59 million. ( 5 points) 13. Evaluate the project. (15 points) a. Name cell D18 NPV. Use the NPV function in D18 to calculate the net present value of the project. The value should be close to $1.24 million. If you haven't used the NPV function before, I posted a video demonstrating the function. b. Name cell C18 IRR. Use the IRR function in C18 to calculate the internal rate of return on the project. c. Name cell E18 Decision. In E18, use an IF statement to return the text "Accept" or "Reject" depending on the NPV calculation. You can find examples at https://corporatefinanceinstitute.com/resources/excel/functions/excel-ifstatement-guide/ 14. Construct a sensitivity analysis for units sold (quantities are listed in Column B starting in Row 19). Define a 1-way data table using Excel's What-If Analysis located in the data tab. The table should include the IRR, NPV, and Decision for each quantity. That is, for each quantity Excel will calculate these three items in the table which will extend from C19 to E27. https://www, wallstreetmojo,com/one-variable-data-table-in-excel/ (5 points) 15. Define a base-case, best-case and worst-case scenario using the Scenario Manager in Excel's What-If Analysis. Examples are at https://wwweducba.com/what-if-analysis-in-excel/. Name them Base Case, Best Case, and Worst Case (ExPrep is case sensitive, so name them exactly as I've written them). Use the following value ranges for the best and worst cases: (5 points) a. Per unit price is plus/minus $5 b. Quantity sold is plus/minus 15,000 units c. Marginal cost of producing a widget is plus/minus $1.50 Note: Price per unit, quantity, and costs do not all move in the same direction when constructing best/worst case scenarios. 16. Using the Scenario Manager in Excel's What-If Analysis, create a scenario summary (this will create a new worksheet titled Scenario Summary; do NOT change the name). The left-hand column in the scenario summary on the new worksheet should list the changing cells as: Quantity, VariableCost, Price (example below). If you correctly named all cells you should see a table with these row names: (5 points) Note: The Best Case scenario should list an NPV of around $3.81 million and the IRR should be about 37%. \begin{tabular}{|l|l} \hline & \multicolumn{1}{|l}{ Coformation/Variables } \\ Company's Other Taxable Income \\ Units Sold \\ Project Life (years) \\ Price per unit \\ Machinery \\ Machinery maintenance costs \\ Depreciable Life \\ Salvage Value \\ Increase in Inventory \\ Increase in Cash Balance \\ Marginal Cost (per unit) \\ Cost of Capital \\ Marginal Tax Rate \end{tabular} Note : Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the XLOOKUP function. In addition, personal taxes are still based on similar tables, and most states also have tax tables like the one above. Capital Budgeting Problem Explained (100 points total) Garca and Martinez manufacture widgets and currently have $3 million in taxable income. The company is considering an expansion, and they've asked you to evaluate the project. The expansion requires the firm to produce 80,000 widgets a year for 6 years, and the company estimates they can sell them for $28 per widget. Garca and Martinez estimate they will need an additional $4,000,000 worth of machinery. The machinery costs $200,000 a year to operate and maintain. The machinery's depreciable life is 8-years, and the company expects to salvage the machinery for $1,000,000 at the end of Year 6 . If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project's life. Similarly, the company will immediately add $75,000 to their cash balance at start-up and maintain that higher cash balance over the project's life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 12%. Calculate the project's NPV by linking to the named variables in Column K. 1. Enter the relevant values for the variables that you use in solving this problem in Column K. Name each cell in Column K that contains a variable. Use the following names: Income, Quantity, Life, Price, Machinery, MaintenanceCost, DepLife, Salvage, Inventory, CashBalance, VariableCost, WACC, and TaxRate (e.g. define the name of cell K4 to be Quantity). When linking cells or writing functions, use these names to reference the cells in your calculations. This makes it easier for others to know what the functions are doing. ( 10 points) 2. Calculate revenues in Row 3. Always refer to the named cells. (5 points) 3. Calculate variable costs in Row 4. Always refer to the named cells. (5 points) 4. Calculate fixed costs in Row 5. Always refer to the named cells. (5 points) 5. Use the information in the previous steps to calculate EBITDA in Row 6. (5 points) 6. Use the INDEX function in Row 7 to calculate depreciation using the depreciation table. (5 points) Some helpful pointers... a. Reference the entire depreciation table in the INDEX function. Use the current project year in Row 2 to determine the correct row for depreciation. Use the depreciable life of the asset to identify the correct column. b. Fun fact: I formatted Row 2 to accept a number (which is why you can reference it in the formula), but Excel displays text with the number to make it more readable as well as functional. If you look in cell D2, only a one is displayed in the formula bar but not the text Year. I did this by using a custom cell format. Here the custom format I use is "Year "0. The text inside the " "tells Excel the text I want to display in front of the number, and the 0 tells Excel to display a whole digit after the text. If I had instead typed 0.0 it would display a whole digit as well as one decimal place (e.g., Year 1.0). c. FYI for calculating after-tax salvage value: If the depreciable life is not equal to the life/years you will own the asset, the book value may not equal zero. d. If done correctly, the Year 1 depreciation should be about $1 million, and the Year 5 and Year 6 will be just over $316,000. 7. Calculate EBIT in Row 8. (5 points) 8. Use that marginal tax rate to calculate taxes owed. (10 points) a. Use the XLOOKUP function on the provided tax table to calculate the marginal tax rate in cell K15. You may use web resources to learn about the function (e.g. httpsi//www.goskills,com/Excel/Resources/Xlookup-in-Excel). If you have trouble with this at first, just type in the rate in K15 and fix it later after completing the other steps below. b. Reference cell K15 (using the name TaxRate) to calculate taxes in each year in Row 9 . 9. Calculate operating cash flow for each period in Row 10. The OCF in Year 1 should be about $1.37 million, and in Year 6 it should be about $1.14 million. ( 5 points) 10. Calculate the change in net working capital for each period in Row 11. In most years it will be zero. (5 points) 11. Calculate the net capital spending for each period in Row 12. Don't forget to pay taxes on the salvage value. In most years net C.S. will be zero. (5 points) 12. Calculate the cash flow from assets for each period in Row 13. CFFA in Year 0 should be - $4.575 million and the Year 6 should be about $2.59 million. ( 5 points) 13. Evaluate the project. (15 points) a. Name cell D18 NPV. Use the NPV function in D18 to calculate the net present value of the project. The value should be close to $1.24 million. If you haven't used the NPV function before, I posted a video demonstrating the function. b. Name cell C18 IRR. Use the IRR function in C18 to calculate the internal rate of return on the project. c. Name cell E18 Decision. In E18, use an IF statement to return the text "Accept" or "Reject" depending on the NPV calculation. You can find examples at https://corporatefinanceinstitute.com/resources/excel/functions/excel-ifstatement-guide/ 14. Construct a sensitivity analysis for units sold (quantities are listed in Column B starting in Row 19). Define a 1-way data table using Excel's What-If Analysis located in the data tab. The table should include the IRR, NPV, and Decision for each quantity. That is, for each quantity Excel will calculate these three items in the table which will extend from C19 to E27. https://www, wallstreetmojo,com/one-variable-data-table-in-excel/ (5 points) 15. Define a base-case, best-case and worst-case scenario using the Scenario Manager in Excel's What-If Analysis. Examples are at https://wwweducba.com/what-if-analysis-in-excel/. Name them Base Case, Best Case, and Worst Case (ExPrep is case sensitive, so name them exactly as I've written them). Use the following value ranges for the best and worst cases: (5 points) a. Per unit price is plus/minus $5 b. Quantity sold is plus/minus 15,000 units c. Marginal cost of producing a widget is plus/minus $1.50 Note: Price per unit, quantity, and costs do not all move in the same direction when constructing best/worst case scenarios. 16. Using the Scenario Manager in Excel's What-If Analysis, create a scenario summary (this will create a new worksheet titled Scenario Summary; do NOT change the name). The left-hand column in the scenario summary on the new worksheet should list the changing cells as: Quantity, VariableCost, Price (example below). If you correctly named all cells you should see a table with these row names: (5 points) Note: The Best Case scenario should list an NPV of around $3.81 million and the IRR should be about 37%