Apply the best practice of Excel modelling (i.e. flexibility and readability) to develop an Excel model 

1. Use meaningful headings for the decision variables and outputs/constraints; 

2. Clearly indicate the decision variables using red-colored border or yellow-colored background; 

3. Avoid hard coding.

Problem description: Randy is planning to open a club sandwich shop in Causeway Bay, Hong Kong Island. The demand during the first year is estimated to be 100,000. Demand is expected to grow at a rate of 3% each year. At the beginning of year 1, Ready needs to determine the capacity for his shop (this decision is made only once) and incurs a one-time cost which is $30 for each unit of capacity. The amount of club sandwiches made in each year cannot exceed this capacity level (i.e. the capacity is the upper bound to the supply in each year). The club sandwich is sold for $40 each and incurs a variable cost of $20. The annual discount rate is 5%. Assume that the profit in each year is collected at the end of that year. You are required to do the following. 

1. Define range names for all the inputs and the decision variable in the file Assignment1.xlsx and paste the range names onto the worksheet.

2. Construct a model to calculate the net present value (NPV) over the next 10 years for any given capacity level. 

3. Conduct a sensitivity analysis to find out the NPV over the next 10 years for the capacity level 80,000 to 200,000 in increments of 10,000. 

4. Based on your result in question (3), create a scatter chart to demonstrate how the NPV changes with the capacity level. 

5. Determine the capacity level that Randy should choose to maximize the NPV over the next 10 years. Provide your answer on the worksheet. Use conditional formatting to highlight the highest NPV. Show your answer in flexible way by using INDEX and MATCH Excel functions so that it can be automatically updated based on the input data values.

 

Transcribed Image Text:

J16 X v fx Capacity Decision 33 Inputs Year 1 demand 30 Annual growth in demand 100,000 3% $30.00 Unit capacity cost (one-time cost) 58 Unit selling price $40.00 Unit variable cost $20.00 Discount rate 52 5% Decision variable 53 ss 50 Yearly profit calculations Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 8E re AD 13 TS 8 e 2 3 J16 X v fx Capacity Decision 33 Inputs Year 1 demand 30 Annual growth in demand 100,000 3% $30.00 Unit capacity cost (one-time cost) 58 Unit selling price $40.00 Unit variable cost $20.00 Discount rate 52 5% Decision variable 53 ss 50 Yearly profit calculations Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 8E re AD 13 TS 8 e 2 3

Answer rating: 100% (QA)

1 2 3 Input Demand g r OTC SP VC Capacity This is defi... View the full answer