Can some one help me to solve this on excel.
The London Store Logistic company is exploring whether to renew the rent of their warehouses in London through a standard lease for the next three years or using the spot market. The company expects a demand of 5000 units per annum and each unit requires an area of 2m2. The selling price is estimated at 18 per unit. The company strategy is either to lease all of its space or nothing. The distribution manager can buy the space on the spot market at 6.75 per m2 or sign a 3 year deal at 6.50 per m2 to lease a 10,000m warehouse space and buy the remaining space from the spot market if necessary. Consider the discount rate to be 7%. a) Assume there are no uncertainties; use the concept of Net Present Value (NPV) to select the most financially viable option to whether to opt for a 3 year deal or to use the spot market. Calculate the NPV using both Excel Function and also manually. Does your decision change if the spot market goes up or down? [4 Marks] b) Comment on your decision found in part (a) by highlighting issues that are worth considering [2 marks] c) The logistic unit of the company estimated that the demand will remain constant the second year but can go up by 8% as well as down by 5% in the third year while the spot price of the product can go up by 15% and can go down by 10% every year. The team considers that both the spot price and the demand are independent from each other. i. Construct the decision trees to reflect the different scenarios for both the spot market and the fixed lease by showing all your working. [4 Marks] ii. Using decision analysis, determine the best option by showing your working clearly. [10 Marks] The London Store Logistic company is exploring whether to renew the rent of their warehouses in London through a standard lease for the next three years or using the spot market. The company expects a demand of 5000 units per annum and each unit requires an area of 2m2. The selling price is estimated at 18 per unit. The company strategy is either to lease all of its space or nothing. The distribution manager can buy the space on the spot market at 6.75 per m2 or sign a 3 year deal at 6.50 per m2 to lease a 10,000m warehouse space and buy the remaining space from the spot market if necessary. Consider the discount rate to be 7%. a) Assume there are no uncertainties; use the concept of Net Present Value (NPV) to select the most financially viable option to whether to opt for a 3 year deal or to use the spot market. Calculate the NPV using both Excel Function and also manually. Does your decision change if the spot market goes up or down? [4 Marks] b) Comment on your decision found in part (a) by highlighting issues that are worth considering [2 marks] c) The logistic unit of the company estimated that the demand will remain constant the second year but can go up by 8% as well as down by 5% in the third year while the spot price of the product can go up by 15% and can go down by 10% every year. The team considers that both the spot price and the demand are independent from each other. i. Construct the decision trees to reflect the different scenarios for both the spot market and the fixed lease by showing all your working. [4 Marks] ii. Using decision analysis, determine the best option by showing your working clearly. [10 Marks]
