$2)$ Profit Scenario

Murry Lutz owns a small shop, Lutz Motors that sells and services vintage motorcycles. Murry is curious how his profit will be affected by his sales over the next year.

Project Focus

Murry would like your help in creating the best$-$case, worst$-$case, and most$-$likely scenarios for his motorcycle sales over the next year. Using Scenario Manager, help Murry analyze the information provided in the worksheet titled $2-$Scenario. Generate a scenario summary for Murry and call it $2-$Summary. The result cell should be the cell which calculates the NPV $($see below$).$

Instructions

While plugging values for Unit Sales, Unit Cost and Unit Price for year $1,$ please ensure that the cells are mapped from the assumption area labeled Lutz Motors at the top of the worksheet i$.$e$.$ you should use cell references rather than typing in or copying the values provided. This improves the design of the spreadsheet so that if the values in the assumption area change, the rest of the worksheet will be updated automatically.

While calculating the growth for Unit Sales, Unit Cost and Unit Price from years $2$ to $5,$ please ensure that the necessary cells have absolute cell references $($F$4)$ to make it easier to copy your formulas.

Scenarios should be set up by changing Sales, Sales Growth and Price. The values given below should be assumed for each scenario.

Best$-$case: Sales$=20000,$ Sales Growth$=0\mathrm{.}2,$ Price$=$ $$10$

Worse$-$case: Sales$=5000,$ Sales Growth$=0\mathrm{.}02,$ Price$=$$$5$

Most$-$likely: Sales$=10000,$ Sales Growth$=0\mathrm{.}1,$ Price$=$$$7\mathrm{.}5$

Calculate the Net Present Value. If you are not familiar with this function in Excel, research it in Excel through Help $($F$1).$ Essentially, NPV provides the net present value of an investment, based on a discount rate, of a series of future payments and income.

In calculating the NPV in this problem, you have to provide two pieces of information $($see NPV formula$).$

$1.$ Rate: this is the Interest Rate

$2.$ Values $($value$1,$ value$2,$ etc...$)$: this is represented by the After Tax Profits for all $5$ years.

Necessary Formulas

Revenue$=$Unit Sales x Unit Price

Expenses$=$Unit Sales x Unit Cost

Before Tax Profits$=$Revenue$-$Expense

Tax$=$Before Tax Profits x Tax Rate

After Tax Profits$=$Before Tax Profits $$ Tax

$-$ Answer the formulas for year $3/4/5$ and also provide the formula for the net present value answer

$-$ give the excel answer for the questions and the formulas to compute on excel