DECISION ANALYSIS USING EXCEL Assignment
[ad_1]
FACULTY OF SCIENCE AND TECHNOLOGY
SUBJECT: DECISION ANALYSIS USING EXCEL
DATE: 19th of August-22nd 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:
Input prices per ton
Steel price $ 310,00
Plastic price $ 240,00
Paint price $ 50,00
Battery price $ 150,00
Tire prices $ 240,00
Wage assembly hour $ 160,00
Wage testing hour $ 280,00
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:
Resources available
Steel 700.000 tonnes
Plastics 1.200.000 tonnes
Paint 180.000 tonnes
Battery 64.000 pieces
Tires 900.000 pieces
Labor hours assembly 225.000 hours
Labor hours control 100.000 hours
Is this a linear programming model? Explain your answer.
Set up the model to maximize profits and use it to answer the following questions:
Which car models should be produced?
What is the production quantity of each model?
What is the maximum profit?
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):
Setup time (in minutes)
Machine 1 75
Machine 2 57
Machine 3 76
Machine 4 47
Machine 5 57
Machine 6 75
Machine 7 65
Machine 8 27
Machine 9 72
Machine 10 29
Machine 11 34
Machine 12 74
The relevant times to complete each job (in minutes) for each machine are given in the starting file “Assignment 2 Starting File”.
Set up the problem and find the plan that minimizes machine operation times needed to complete all jobs.
Assume the maximum number of jobs at each machine is limited to two. How does this information change the optimal solution in a)?
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:
Facility Minimum production Annual capacity Unit production costs
Chicago 1.300 cars 9.700 cars $900
Dallas 1.200 cars 8.100 cars $600
Detroit 2.900 cars 9.500 cars $1.000
Nashville 2.500 cars 8.600 cars $700
Philadelphia 1.200 cars 9.000 cars $700
Phoenix 2.900 cars 9.400 cars $1.400
Seattle 1.900 cars 8.800 cars $1.000
The cars are shipped to 26 American cities and must meet the annual demand. The annual demand for each city is:
City Annual market demand
Austin 1.300 cars
Boston 2.400 cars
Charlotte 1.800 cars
Chicago 700 cars
Columbus 1.200 cars
Dallas 2.100 cars
Denver 1.400 cars
Detroit 1.200 cars
El Paso 2.300 cars
Fort Worth 2.300 cars
Houston 1.500 cars
Indianapolis 1.000 cars
Jacksonville 2.300 cars
Las Vegas 2.200 cars
Los Angeles 1.100 cars
Nashville 1.400 cars
New York City 930 cars
Philadelphia 1.600 cars
Phoenix 1.800 cars
Portland 700 cars
San Antonio 2.200 cars
San Diego 2.300 cars
San Francisco 2.500 cars
San Jose 1.800 cars
Seattle 1.900 cars
Washington 1.700 cars
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”.
Set up the model with the objective of minimizing cost. Use this model to find:
The optimal production at each facility
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
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):
C(X)=3.000.000+8.000X
The number of Fiasco cars produced is given by X. 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:
China Euro area Great Britain Japan
Unit selling price $ 21.200 $ 21.600 $ 19.900 $ 20.200
Unit shipping cost $ 5.400 $ 5.700 $ 3.800 $ 5.200
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: x_1=3m_1^0,5
Demand in Euro area: x_2=4m_2^0,5
Demand in Great Britain: x_3=2m_3^0,5
Demand in Japan: x_4=3m_4^0,5
Where m_1, m_2, m_3 and m_4 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 X is defined as the sum of the production to all individual markets, X=x_1+x_2+x_3+x_4.
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.
Find the shadow price for the marketing budget constraint and interpret the solution.
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:
What is the lowest price in China to make advertising profitable?
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:
Probability for different states of the economy the next few years
Probability of a strong economy 7,5 %
Probability of a slightly above average economy 19,4 %
Probability of an average economy 44,0 %
Probability of a slightly below average economy 22,3 %
Probability of a poor economy 6,8 %
The cars yield the following profit (net present value) if produced given the state of the economy:
Strong Above average Average Below average Poor
Lord Alfa $ 65.130.000 $ 60.360.000 $ 49.620.000 $ 36.800.000 $ 32.120.000
Lord Beta $ 52.020.000 $ 46.380.000 $ 39.410.000 $ 32.350.000 $ 22.840.000
Lord Gamma $ 83.010.000 $ 75.540.000 $ 60.440.000 $ 46.410.000 $ 28.350.000
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:
Research and development expenditure
Lord Alfa $ 32.000.000
Lord Beta $ 42.000.000
Lord Gamma $ 55.000.000
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.
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.
Suppose now that Lord’s utility function of net revenue x (measured in dollars) is U(x)=1-e^(-x/350000). 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:
C=8.000+M
Lord has a vital machine, and M 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:
Excellent: M=5.900
Good: M=6.700
Average: M=8.400
Poor: M=10.500
Bad: M=14.800
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.
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.
How high must the replacement cost be for policy 1 to be optimal?
How high must the replacement cost be for policy 5 to be optimal?
The post DECISION ANALYSIS USING EXCEL Assignment appeared first on mynursinghomeworks.
[ad_2]
Source link
