show ALL FORMULAS

Your firm owns a Volkswagen dealership, and you are considering entering into a 5-year agreement to sell Audi A3s as well. The cars would cost you $28,000, and you believe that you can sell 50 Audis per year at an average price of $35,000. You would have to hire 2 new salespeople that you would pay $30,000 per year each plus 5% of the revenue they each generate. Audi would require that you invest $200,000 (depreciable straight line over 5 years using the halfyear convention) in Audi-related signs, equipment, and furniture to place in your dealership. You would also be required to invest in 20 cars immediately and would keep that amount in inventory over the life of the project until liquidating inventory once the project ends. After 5 years, you can recover any investment in working capital, and the unneeded equipment would have a market value of $50,000. Your firm requires a 12% retum on all new investments, and the tax rate is 40%. You have completed a free cash flow analysis. However, you wish to perform a variety of tests to determine how your assumptions influence your final decision. Perform the following steps using the Excel file. 1. Scenario Analysis: You want to determine how hiring a different number of salespeople will influence the value of this investment, so you would like examine the following scenarios. Perform the following steps to determine the how the NPV changes in each scenario. a. Name the primary assumption cell for number of workers "Num_Workers," the primary assumption cell for cars sold per year "Cars_Sold," and the NPV cell "NPV." You can name cells under Formulas, define name. b. Use scenario manager to define each scenario. Scenario manager is under Data, What if Analysis. c. Generate a summary tab that shows the NPV of the project in each scenario. 2. Sensitivity Analysis: Use a data table to complete the sensitivity analysis that is started in the Excel sheet. Data tables can be constructed in What if Analysis under the Data tab. 3. Break-Even Analysis: In the Excel template, use Goal Seck to perform a break-even analysis for the cost per car. To do so: a. Click on Data, What if Analysis, Goal Seek. b. Where it says set cell, click on the cell that computes that project's NPV. Type " 0 " in the box next to where Goal Seek says "to value." Finally, the cell that you want to change is the base assumption cell with blue text for the cost per car