FACULTY OF SCIENCE AND TECHNOLOGY

SUBJECT:  DECISION ANALYSIS USING EXCEL

DATE: 19^th of August-22^nd of August

TIME: Home exam – 72 hours

Lord is a fictional car manufacturer from Detroit, USA.

Assignment 1

Lord has 22 different car models that can be produced. Producing a specific model requires a minimum production level due to economies of scale. Each model also has a maximum sale quantity given by market conditions. Each model requires a certain number of inputs, with the prices of the inputs being:

The input requirement, minimum production and maximum sale for each model is shown in the “Assignment 1 Starting File”. Each input has a limited availability:

1. Is this a linear programming model? Explain your answer.
2. Set up the model to maximize profits and use it to answer the following questions:
   1. Which car models should be produced?
   2. What is the production quantity of each model?

• What is the maximum profit?

1. For those car models that are not produced, by how much must the price increase before each of them are produced?

Assignment 2

One of the production facilities for Lord is in Nashville. At this facility there are ten jobs that need to be completed exactly once a day. To complete the job, Lord can use several machines, twelve in total. If a machine is used at all, a setup time is required as shown in the table below (time is in minutes):

The relevant times to complete each job (in minutes) for each machine are given in the starting file “Assignment 2 Starting File”.

1. Set up the problem and find the plan that minimizes machine operation times needed to complete all jobs.
2. Assume the maximum number of jobs at each machine is limited to two. How does this information change the optimal solution in a)?
3. Use a sensitivity analysis to investigate how a change in the setup time of machine 12 changes the optimal solution in b).

Assignment 3

Lord has production facilities in seven different American cities producing the model R-Max. The company needs to have a minimum production at each facility to make the plant economic feasible. Each facility has the following minimum production, maximum annual capacity and production cost:

The cars are shipped to 26 American cities and must meet the annual demand. The annual demand for each city is:

The costs of shipping a car from each plant to each city along with the city requirements are given in the starting file “Assignment 3 Starting File”.

1. Set up the model with the objective of minimizing cost. Use this model to find:
   1. The optimal production at each facility
   2. The number of cars that should be transported from each facility to each of the different cities to meet annual market demand

• The minimum cost

1. Lord wants to invest in the production capacity of one production facility to make more of the model R-Max. Which facility is the most optimal to invest in? Explain your answers.

Assignment 4

Lord uses marketing to increase the sale of its model Lord Fiasco in several markets. Fiasco is produced at one facility in the US and has the production cost (in US dollars):

The number of Fiasco cars produced is given by . After production cars are exported to four main markets, with the shipping cost per car and the price per car for each market shown in the table below:

The demand in each market is dependent on the amount of money spent on marketing. The annual demand in each market is given by:

• Demand in China:
• Demand in Euro area:
• Demand in Great Britain:
• Demand in Japan:

Where , ,  and  is the amount of marketing used in China, the Euro area, Great Britain and Japan respectively. The sum of the marketing expenses in all four markets, which is the marketing budget, is $1.000.000. The total production quantity  is defined as the sum of the production to all individual markets, .

1. There is no starting file for this assignment. Set up the model with the profit being the objective function to maximize. Use the model to find the optimal amount of marketing for each of the four markets and the total profit.
2. Find the shadow price for the marketing budget constraint and interpret the solution.
3. Run a sensitivity analysis for China to see how a change in the price affects the optimal solution. Do this for prices up to 50.000 USD. Use this sensitivity analysis to answer the following questions:
   1. What is the lowest price in China to make advertising profitable?
   2. What is the optimal advertising in each market and the profit if the price in China is 15.000 USD?

• What is the optimal advertising in each market and the profit if the price in China is 50.000 USD?

Assignment 5

The research department at Lord could potentially develop three new cars, called Alfa, Beta and Gamma. The company has the choice to introduce none, one, two or all three cars at the same time. The success of the car sales depends on the state of the economy the next few years. There are five states of the economy considered:

The cars yield the following profit (net present value) if produced given the state of the economy:

The profit does not include the expenditure on research and development. If the company decides to produce one model, it will take one year to develop. To develop each model, the research and development expenditure is:

1. Open the starting file “Assignment 5 Starting File”. Use PrecisionTree to identify the strategy that maximizes Lord’s expected net profit including the research and development expenditure.
2. Perform a sensitivity analysis on the optimal decision to evaluate how the research and development expenditure for the three cars affects the optimal decision. Comment the results.
3. Suppose now that Lord’s utility function of net revenue (measured in dollars) is . Find the decision that maximizes Lord’s expected utility. How does this optimal decision compare to the optimal decision with an EMV criterion? Explain any differences between the two.

Assignment 6

A Lord facility in Dallas produces the model Super Duper. You are given the following information:

• The car price during the year is normally distributed with a mean of $20.000 and a standard deviation of $2.000.
• Monthly production is also normally distributed with a mean of 2.800 cars and a standard deviation of 250 cars. It is assumed that Lord has no inventory, meaning that production and sales are equivalent.
• The unit production cost (in $) is given by:

Lord has a vital machine, and  are the unit costs at this machine. The size of the costs during a month depends on the state of the machine at the beginning of the month. The different states and the associated unit costs are:

1. Excellent:
2. Good:
3. Average:
4. Poor:
5. Bad:

• You are given the following information on the probability of the state of the machine:
  • If the machine at the start of the month is excellent, there is a 56 % probability it is also excellent at the start of the following month and a 44 % probability it is in a good state at the start the following month.
  • If the machine at the start of the month is good, there is a 49 % probability it is also good at the start of the following month and a 51 % probability it is in an average state at the start the following month.
  • If the machine at the start of the month is average, there is a 54 % probability it is also average at the start of the following month and a 46 % probability it is in a poor state at the start the following month.
  • If the machine at the start of the month is poor, there is a 29 % probability it is also poor at the start of the following month and a 71 % probability it is in a bad state at the start the following month.
• It is assumed that once the machine deteriorates, it cannot be “fixed” and revert to a better state. It is further assumed that at the start of the period of analysis the state of the machine is excellent. Lord can instantaneously replace the machine with a new one in an excellent state for a cost of $12.000.000. If a machine is replaced it is at the beginning of the month.
• Five maintenance policies are under consideration:
• Policy 1: Never replace a machine.
• Policy 2: Immediately replace a bad machine.
• Policy 3: Immediately replace a bad or poor machine.
• Policy 4: Immediately replace a bad, poor or average machine.
• Policy 5: Immediately replace a bad, poor, average or good machine.

The company wants you to set up a model for the average profit for the next 12 months.

1. Use @Risk to set up a simulation model (Hint: Use the RiskDicrete(States; Probabilities for the state) for the probabilities for the states). Use a separate excel file for each policy simulation, it is recommended to have only one excel file open when running the student version of @Risk. Use this model to find the optimal maintenance policy and the associated profit with each policy. Comment the results.
2. How high must the replacement cost be for policy 1 to be optimal?
3. How high must the replacement cost be for policy 5 to be optimal?