Project 3: Capital Budgeting Analysis: Goodweek Tires - Instructions Financial Modeling and Analytics – FIN 4453 I. Purpose: The purpose of this project is for you to perform a capital budgeting analysis and to decide whether to accept a project. You will also apply some risk analysis tools to analyze the Goodweek Tires project given an uncertain future, make observations about your findings, and explain the implications of your findings based on these tools. (Note: it is very important that you watch my Excel Application videos and my Lecture Videos for Chapters 5, 6, and 7 to ensure maximum success with this project!) II. Task: 1. Original Assumptions: Please read through the Goodweek Tires, Inc. case study at the end of Ch. 6 in your text book (p. 204 in the 13th edition). The assumptions are all summarized in the Excel workbook template that is attached to this assignment in the ‘Original Assumptions’ worksheet. Use the first worksheet to calculate the net present value (NPV), internal rate of return (IRR), payback, discounted payback, and profitability index (PI). Make sure your model is ‘dynamic’. Always tie the variables in your formulas to the cells in the assumptions section. Then decide if Goodweek Tires should accept the project based on the NPV (using the =IF function). Note: All your answers should be written into this Excel workbook. 2. Risk Analysis: Next you will perform a sensitivity, scenario and break-even analysis, and you will use the data table tool. Create copies of the ‘Original Assumptions’ worksheet to perform each of the following analysis: 2. a. Sensitivity analysis: In the sensitivity analysis you are supposed to find out if the NPV is more sensitive to the OEM tire market share or the replacement tire market share. To do that, first copy the Original Assumptions sheet and rename the copied sheet ‘Sensitivity OEM Mshare’. On this new sheet, change the OEM tire market share from © Martina Schmidt, p. 1 11.00% to 12%. Change the color of the cell you changed and add a note to this cell to explain what you did. To insert a note, right click on the cell, click on “New Note”. (If you have an older version of Excel, you can use insert comment). Calculate how sensitive the NPV measure is to this increase in the OEM tire market share. Remember that you first have to calculate the % change in the NPV and the % change in the market share. The formula for the % change is =(ending value – beginning value)/beginning value. Then divide the two % changes by each other to find the sensitivity. Note: be careful to format the sensitivity correctly. It should be formatted as a number (not a percent). Use 2 decimals for the sensitivity. Please enter all your sensitivity calculations on the top right area of this sheet. This way I know where to find it. Then add a text box. Note: To insert a text box, go to the ‘Insert’ button, ‘Illustrations’ group, under ’Shapes’, ‘Basic Shapes’, select ‘Text Box’. Draw the text box and start typing in it. You can later expand it to fit and show all the text. Change the color of the textbook to make it stand out. In this text box explain in specific terms what the interpretation of this sensitivity is. (For each 1% change in… the …changes by…%) Next, copy the ‘Original Assumptions’, sheet again. Rename this sheet to ‘Sensitivity Rep Mshare’. On this new sheet, change the replacement tire market share from 8.00% to 9.00%. Again, change the color of the cell you changed and add a note to this cell to explain what you did. To insert a note, right click on the cell, click on “New Note”. (If you have an older version of Excel, you can use insert comment). Calculate how sensitive the NPV measure is to this increase in the replacement tire market share. Please enter all your calculations for the sensitivity on the top right area of this sheet and highlight them in a different color so that they are easily visible. Add a text box and explain in specific terms what the interpretation of this sensitivity is. Compare the two sensitivities and describe what the implications of your findings from the two sensitivity analysis are. Please write your answer in a text box on the ‘Sensitivity Rep Mshare’ worksheet underneath the sensitivity calculation. 2. b. Data Table: Next, perform an analysis with a two-dimensional ‘Data Table’. © Martina Schmidt, p. 2 For this analysis, copy the ‘Original Assumptions’ sheet again. Rename it to ‘Data Table’. You will use the ‘Data Table’ function to calculate the NPVs for different ‘price & variable growth rates’ and different ‘discount rates’. Construct a table that has the discount rates 22%, 23%, 24%, 25%, 26%, 27%, 28%, 29%, and 30% in the headings in the 1st row and the price & variable growth rates 2%, 3%, 4%, 5%, 6% in the headings in the first column. In the upper left corner of the table, link to the cell that contains the NPV. Change the color of the table to make it stand out. Your data table will look something like this: =$B$81 references the cell on this sheet which contains the NPV in this 2-dimensional data table. Select the table (the cell range in the dark brown area in the image above), click on the Data button, What-If Analysis, Data Table. The ‘Row input cell’ is the cell in the assumptions that contains the ‘discount rate’. The ‘Column input cell’ is the cell in the assumptions that contains the ‘price & variable cost growth rate’. Hit OK. Format the cells that contain the NPV in this table to ‘Currency’ with 0 decimal place. Now also add ‘conditional formatting’ to the data table. The goal is to automatically have Excel change the color of all NPVs in the data table to ‘red’ if the NPV is less than or equal to zero and to ‘green’ if the NPV is greater than zero. To do so, highlight the part of the data table that contains the new calculated NPVs. Then in the ‘Home’ tab, click on ‘Conditional Formatting’ in the ‘Styles’ group. Select ‘Color Scales’, New Rule. © Martina Schmidt, p. 3 ‘Set a Rule Type’: →Format only cells that contain ‘Edit the Rule Description’: Format only cells with: Cell Value Drop-down: ‘less than or equal to’ Enter $0 in the box to the right Below, click on ‘Format’, in the ‘Fill’ tab, click on ‘red’. Click on Ok, Ok. Then do the same for positive values: Highlight the part of the data table that contains the new calculated NPVs. In the Home tab, click on ‘Conditional Formatting’ in the ‘Styles’ group. Select ‘Color Scales’, New Rule. Under ‘Set a Rule Type’: →Format only cells that contain Under ‘Edit the Rule Description’: Format only cells with: Cell Value Drop-down: ‘greater than’ Enter $0 in the box to the right Below click on Format, in the ‘Fill’ tab, click on the color ‘green’. Click on Ok, Ok. Next, insert a text box and make an observation about what happens to the NPV as the discount rate and price & variable cost growth rate changes. What is the ‘implication’ of your observations? In other words, what would be best for the firm with regards to the firm’s discount rate and growth rate? © Martina Schmidt, p. 4 2. c. Scenario analysis: In the scenario analysis you are supposed to change multiple underlying assumptions given different outcomes and probabilities and then decide if Goodweek Tires should accept the project. Assume that there are three scenarios: The first scenario is the ‘Original Assumptions’ scenario. This is the "best case" scenario. Assume that it occurs with a 30% chance. The second scenario is the “normal case”, which occurs with a 50% chance. The third scenario is the “worst case”, which occurs with a 20% chance. In the second ‘Normal’ scenario, the projected ‘Automobile production’ in the OEM market changes from 8,500,000 to 7,000,000 and the projected ‘Total market sales’ in the replacement tire market change from 35,000,000 to 25,000,000. In the third ‘Worst Case’ scenario the projected ‘Automobile production’ changes from 8,500,000 to 4,000,000 and the projected ‘Total market sales’ change from 35,000,000 to 20,000,000. To conduct the scenario analysis, copy the ‘Original Assumptions’ sheet. Rename the new sheet to ‘Scenario’. Change the color of the two cells in the assumptions that contain the variables you will be changing: ‘Automobile production’ (in the OEM Market) and ‘Total market sales’ (in the Replacement Market). Now click on the cell that contains the current assumption for the ‘Automobile production’, then Ctrl and the cell that contains the current assumption for the ‘Total market sales’. This selects both input cells you will be changing in the scenario analysis you will be conducting next with the ‘Scenario Manager’. Click on the ‘Data’ tab, then under ‘What-If Analysis’ in the ‘Forecast’ group, click on ‘Scenario Manager’. Click on ‘Add’, then under ‘Scenario name’, type ‘Best Case’. Under ‘Changing cells’, the two cells you just selected should show up. If they are not there, type them in. Select ‘Prevent changes’. Click OK. The ‘Scenario Values’ window should now open up (you may have to left click your mouse). Keep the current values. Click on OK. The ‘Scenario Manager’ window should now open up. You can see the ‘Best Case’ has already been saved under ‘Scenarios’. Now add the other two scenarios next. © Martina Schmidt, p. 5 Click on ‘Add’, under ‘Scenario name’, type ‘Normal Case’. Under ‘Changing cells’, you should still see the same two input cells. Do not make any change to the cells. Keep ‘Prevent changes’ selected. Click OK. The ‘Scenario Values’ window should now open up again. Enter the values for the ‘Normal Case’. That means change the values to 7,000,000 for the cell that represents ‘Automobile production’, and 25,000,000 for the cell that represents ‘Total market sales’. Click OK. You should now see the two saved scenarios: Best Case and Normal Case. Click on Add again. Under Scenario name, type ‘Worst Case’. Under ‘Changing cells’, keep the same cell designations. Keep ‘Prevent changes’ selected. Click OK. The ‘Scenario Values’ window should now open up again. Enter the value for the ‘Worst Case’. That means change the values to 4,000,000 for the cell that represents ‘Automobile production’, and 20,000,000 for the cell that represents ‘Total market sales’. Click OK. This will open the ‘Scenario Manager’ window again. You should now see all three scenarios you saved. Click on ‘Summary’. Under ‘Report type’ select: ‘Scenario summary’. Underneath ‘Result cells’, enter the cell reference that contains the NPV in this worksheet. Click Ok. Excel will create a new worksheet called “Scenario Summary” which contains an output table. At the bottom of the table, insert three rows. © Martina Schmidt, p. 6 Name the first row ‘Probabilities’. (Note: type this name into the cell in Column B). To the right in this row, manually enter the given probabilities for each of the three scenarios: the Best Case, the Normal Case, and the Worst Case. Call the 2nd row you inserted E(NPV), i.e. type this name into the cell in Column B. To the right of the E(NPV) cell, calculate the expected NPV or E(NPV). The formula is E(NPV)=NPV1 x probability1 + NPV2 x probability2 + NPV3 x probability3. The third inserted row below that is called ‘Accept?’. Type ‘Accept?’ in the cell in column B and use an =IF statement in the next cell to the right to decide if this project should be accepted based on the E(NPV). Note: unfortunately, the output table for a scenario analysis in Excel is NOT dynamic. If you make any changes to your assumptions, you will need to re-run the scenario analysis. 2. d. Break-even analysis: For the break-even analysis, copy the ‘Original Assumptions’ worksheet again and call it “Break Even”. On this sheet use the ‘goal seek’ function (using the original assumptions) to find out what the minimum unit price for the OEM market tires has to be in order for Goodweek Tires Inc. to break even (i.e. to have an NPV = 0). Change the color of the cell that contains the unit price for the OEM market tires to highlight it. Insert a note in the cell that shows the break-even price. To insert a note, right click on the cell, click on “New Note”. (If you have an older version of Excel, you can use insert comment). In this note explain the calculation you performed and what this value means. Your worksheet will end up with a total of 7 worksheets in the following order: 1. the ‘Original Assumptions’ sheet 2. the ‘Sensitivity OEM Mshare’ sheet 3. the “Sensitivity Rep Mshare’ sheet 4. the ‘Data Table’ sheet 5. the ‘Scenario’ sheet 6. the ‘Scenario Summary’ sheet © Martina Schmidt, p. 7 7: the ‘Break Even’ sheet Don’t forget to save your file. Then submit it in Canvas. Only submit one file per group. Note that once you have submitted your file, you cannot resubmit it. So make sure that you submit the correct file. The reason is that once you submit your file, I will start grading it. That takes a long time to do. I do not have time to re-grade re-submitted files. III. Grading Criteria Please take a look at the rubric to see how your group will be graded. © Martina Schmidt, p. 8 
Purchase answer to see full attachment