How is the excel table supposted to look like and the structure of the answer(which calculations go first)?
1. Determine the NPV, IRR, PAYBACK, Discounted Payback, and MIRR for the opening of one average new store in 2016. Assume same sales as in 2014 but costs are 5% higher.
2. Perform a worst case scenario analysis: Sales are 30% lower than in 2014 and costs are 20% higher and the stock’s risk (beta) is 50% higher than it was on December 1 2015. Determine the NPV, IRR, PAYBACK, Discounted Payback, and MIRR for the opening of one average new store in 2016.
To answer these questions, you would typically create an Excel table to calculate the NPV, IRR, PAYBACK, Discounted Payback, and MIRR. Here is a suggested structure for the table:
Column A: Calculation
Column B: Formula
Column C: Explanation/Steps
Column D: Result/Answer
For both questions, the structure and calculations will be the same. The only difference will be the input values for sales and costs.
1. For the first question:
Column A: Calculation
Column B: Formula
Column C: Explanation/Steps
Column D: Result/Answer
Row 2: NPV (Net Present Value)
Row 3: IRR (Internal Rate of Return)
Row 4: PAYBACK
Row 5: Discounted Payback
Row 6: MIRR (Modified Internal Rate of Return)
In column B, use the appropriate formulas to calculate each of the above metrics using the given inputs. The exact formulas will depend on the cash flow projections and discount rate you are using. It is important to use consistent time periods for the calculations (e.g., annual, monthly, etc.).
In column C, explain the steps you took to calculate each metric. This can include the formula used and any assumptions made.
In column D, input the result or answer based on the calculations in column B.
2. For the second question:
Use the same table structure as in the first question, but change the input values for sales and costs according to the given scenario:
Sales: 30% lower than in 2014
Costs: 20% higher than in 2014
Risk (Beta): 50% higher than December 1, 2015
Again, use the appropriate formulas to calculate each metric based on the adjusted input values.