I need it done ASAP. All the work has to be showed to get full credit.
The book used was "Optimization Modeling with Spreadsheets" It uses Excel spreadsheets and Excel solver to complete the problems.
Here are the problems and I enclosed a sample sheet, this might not pertain to the problems just something to give an idea!
There is no word limit but the work has to be shown!
I enclosed an example of the wording and stuff this was the last assignment of the course, something like this should be good and the questions answered!
Question:-1
Merrill Sporting Goods (Revisited) in which the criterion gives equal weight to each of the retail sites. But in practice, there will be different levels of traffic between the warehouse and the various sites. One way to incorporate this consideration is to estimate the number of trips between the warehouse and each retail site and then weight the distances by the trip volumes. Thus, the original data set has been augmented with volume data (vk), as listed in the table below.
|
Site (k)
|
xk
|
Yk
|
vk
|
|
1
|
9
|
29
|
12
|
|
2
|
5
|
50
|
15
|
|
3
|
26
|
68
|
20
|
|
4
|
39
|
79
|
12
|
|
5
|
41
|
54
|
8
|
|
6
|
38
|
59
|
16
|
|
7
|
63
|
6
|
18
|
|
8
|
52
|
58
|
20
|
|
9
|
81
|
76
|
12
|
|
10
|
95
|
93
|
24
|
Now we can use as a criterion the weighted sum of distances between the warehouse and the retail sites.
What location is optimal for the weighted version of the criterion?
How much of an improvement is achieved by the solution in (a) over the optimal location for the unweighted version (39.59, 58.43)?
Question:- 2
Pricing with Dependent Demands Covington Motors is a car dealership that specializes in the sales of sport utility vehicles and station wagons. Due to its reputation for quality and service, Covington has a strong position in the regional market, but demand is somewhat sensitive to price. After examining the new models, Covington's marketing consultant has come up with the following demand curves.
Truck Demand = 400 - 0.014(truck price)
Truck Demand = 425 - 0.018(wagon price)
The dealership's unit costs are $17,000 for SUVs and $14,000 for wagons. Each SUV requires 2 hours of prep labor, and each wagon requires 3 hours of prep labor. The current staff can supply 320 hours of labor.
Determine the profit-maximizing prices for SUVs and Wagons. (Round off any fractional demands.)
What demand levels will result from the prices in (a)?
What is the marginal value of dealer prep labor?
Question:-3
Pricing with Interdependent Demands Covington Motors sells sport utility vehicles and station wagons in a price-sensitive market. Its marketing consultant has rethought the simple demand curves first proposed (in the previous exercise) and now wants to recognize the interaction of the two markets. This gives rise to a revised pair of demand curves for SUVs and wagons, as shown below.
SUV demand = 300 - 0.014(SUV price) + 0.003(wagon price)
Wagon demand = 300 - 0.018(Wagon price) + 0.005(SUV price)
The dealership's unit costs are $17,000 and $14,000 per unit, respectively. Each SUV requires 2 hours of prep labor, and each Wagon requires 3 hours of prep labor. The current staff can supply 320 hours of labor. Covington Motors wants to maximize its profits from the SUVs and Wagons that it acquires for its stock.
- Determine the profit-maximizing prices for SUVs and Wagons. (Ignore the fact that these prices may induce fractional demands.)
- What sales levels will result from the prices in (a)?
- What is the marginal value of dealer prep labor?
Question:- 4
The Canadian Motorcycle Company (CMC) has determined that its customers are very price-sensitive, with the number of motorcycles purchased heavily dependent upon the prices set. The company makes two different styles of bike: the high-end "Jet" and the lower-end "Canuck." Market research has shown that the Jet has a price-demand curve of p = 22,000 - 0.25x, where p represents price and x represents the demand volume. For the Canuck, the curve is p = 18000 - 0.3x. In order to determine profit, CMC will simply take the selling price minus the materials costs. Raw materials for the Jet are $6000 per bike, and they are $5000 per bike for the Canuck. Since CMC hand-builds each bike it makes, the only real constraint for building the bike is the number of manufacturing hours available. CMC has 10,000 hours per month of manufacturing capacity available. A Jet takes 23 hours to manufacture and a Canuck takes 20. CMC also wants to ensure the integrity of each product line, so it will require that a minimum of 100 of each bike should be made each month. Note: All figures are in Canadian dollars.
1. How many of each type of bike should CMC manufacture each month? What will its profit be?
2. Should it drop its minimum limit of 100 of each bike? Why or why not?
Question:- 5
Furrel's Ice Cream Company ships ice cream in bulk from its manufacturing facility to its 25 retail outlets. Furrel's has categorized its retail outlets into four types, each of which sells a certain level of ice cream per week, measured in pounds. Furrel's ships its ice cream in two different size reusable containers: a 6-pound container and a 10-pound container. The company currently has 200 6-pound containers and 25 10-pound containers. The company would like to minimize the amount of excess ice cream shipped to each store while making use of its existing reusable containers. For example, store type 1 needs 25 pounds of ice cream a week. Furrel's could ship this in one 10-pound container and three 6-pound containers for a total of 28 pounds (3 pounds excess) or in two 10-pound containers and one 6-pound containers (1 pound excess). However, with only 25 10-pound containers available, it is not clear that this is the best choice for this type of store.
|
|
Store Type
|
|
Type 1
|
Type 2
|
Type 3
|
Type 4
|
|
Pounds of Ice Cream needed
|
25
|
40
|
50
|
100
|
|
Number of Stores
|
10
|
5
|
4
|
6
|
2. Given this current store configuration and the number of reusable containers available, how many 6-pound and 10-pound containers should be used to ship to each store in order to minimize the excess ice cream? How much excess ice cream will this lead to each week?
Should Furrel's purchase more 6-pound containers or more 10-pound containers? Why?
Question:- 6
The AppleBerry Company has three warehouses where it stores its tablet
| From |
To |
| |
Dist 1 |
Dist 2 |
Dist 3 |
Dist 4 |
| Whse A |
$8 |
$10 |
$6 |
$3 |
| Whse B |
$9 |
$15 |
$8 |
$6 |
| Whse C |
$5 |
$12 |
$5 |
$7 |
| Distributor |
Estimated Monthly Demand |
| 1 |
2500 |
| 2 |
2500 |
| 3 |
2000 |
| 4 |
3500 |
computer devices and four distributors that place these products in retail stores and online. Each warehouse holds 5000 devices. Because of the various distances between the warehouses and the distribution centers, there are different costs to ship the devices from each warehouse to each distributor. The cost per device for shipping between the warehouses and distributors is given in the table below. Additionally, each distributor has calculated an estimated monthly demand for the tablet and does not want to receive any more tablets than this estimated demand.
1. Given these facts, how many devices should be shipped from each warehouse to each distributor per month, in order for AppleBerry to minimize its costs? What is this minimized cost? Set this up as a spreadsheet using Solver.
2. AppleBerry is looking to shut down one of its warehouses. In your opinion, based on this model, which warehouse should be shut down? Explain your answers.
Question:- 7
Snoo Coffee Company creates three blends of coffee: budget, classic, and premium. The coffee blends are composed of four ingredients: A, B, C, and D. The table below shows the percentage of each component that are in each blend and the cost per pound for that ingredient. The blends have a wholesale price of $2.50, $3.00, and $4.50 for budget, classic, and premium, respectively. A limited amount of each ingredient is available on a weekly basis.
|
Ingredient
|
Budget
|
Classic
|
Premium
|
Cost/lb.
|
Max Weekly Availability
|
|
A
|
25%
|
35%
|
0%
|
$0.60
|
40,000
|
|
B
|
55%
|
25%
|
0%
|
$0.80
|
20,000
|
|
C
|
5%
|
10%
|
50%
|
$0.95
|
35,000
|
|
D
|
15%
|
30%
|
50%
|
$0.70
|
45,000
|
Snoo can make 100,000 pounds of coffee per week, and it wants to make a minimum of 25,000 pounds of each blend. (20 points)
1. In order to maximize weekly profits, how many pounds of each ingredient should be purchased?
2. What is the shadow price of ingredient C?
3. How much should Snoo be willing to pay for an additional pound of ingredient C to raise total profit?