Reference no: EM132281457
Management Information Systems Assignment - Excel Solver
In this assignment, you are asked to solve three optimization problems using Excel Solver.
Question 1: What To Eat?
Jamie is planning to compete in her first mini triathlon this summer and wants to ensure her diet covers the energy she needs to train. The table below shows the foods she can choose from and their associated calories, some nutritional amounts, and cost. Using the information provided, determine the optimal diet (i.e. how many of each type of food) to consume that will minimize her cost and fulfil the following requirements:
Intake must be a minimum of:
3500 calories
15g protein
10g carbohydrates
And a maximum of:
20g salt
|
|
Sandwich
|
Energy Bar
|
Energy Drink
|
Trail Mix
|
|
Calories
|
450
|
200
|
200
|
400
|
|
Protein (grams)
|
5
|
5
|
0
|
2
|
|
Carbohydrates (grams)
|
3
|
5
|
4
|
4
|
|
Salt (grams)
|
3
|
5
|
1
|
2
|
|
Cost
|
$4.00
|
$3.50
|
$2.50
|
$4.00
|
Question 2: Cyclone Robotics
Cyclone Robotics needs some help to optimize their production. They want to make the best use of their existing resources to generate the highest possible profit. The company has asked you to help plan their upcoming production levels for the next quarter.
The company has provided the following data to help get you started:
It manufactures three types of robots, as shown below. As part of their contractual obligation, they must manufacture and distribute at least 10 of each robot to their retail partner. The table below shows the profit per unit, and the resources required per unit in the manufacturing process. The last column (furthest right) shows the total resources that are available for the upcoming quarter's production. Your task is to determine:
1. How many of each robot they should produce (given the production constraints), in order to generate the highest profit.
2. How does your answer change if they must manufacture and distribute a minimum of 11 of each robot to their retail partner? i.e. In a separate section of the same worksheet, enter the new quantities of each robot to produce under this constraint.
|
Robots
|
Kit
|
Cougar
|
Kat
|
Resources Available
|
|
Profit per user
|
$500
|
$1,150
|
$800
|
|
|
Resource Required per Unit
|
|
|
|
|
|
Labour Hours
|
6
|
15
|
11
|
850 hours
|
|
Machine Hours
|
7
|
19
|
12
|
1,200 hours
|
|
Compressors (units)
|
15
|
31
|
23
|
850 units
|
|
Motors (units)
|
5
|
9
|
7
|
250 units
|
|
Batteries (units)
|
2
|
10
|
6
|
200 units
|
Question 3: Total Tyre Distribution
Total Tyre Distribution wants to minimize the total shipping costs between their suppliers and their regional warehouses. Each supplier has a limited quantity of product to ship and each region has a specific demand projection that they need to fulfill. Given the information provided in the table below, how should the company stock their product? In other words, how many units should each supplier ship to each regional warehouse in order to minimize Total Tyre's shipping costs?
|
|
Shipping Capacity (units)
|
Shipping Costs per Unit to Regional Warehouses
|
|
West
|
Central West
|
Central
|
Central East
|
East
|
|
Okanagan
|
43,500
|
$1.70
|
$2.56
|
$1.95
|
$2.06
|
$2.95
|
|
Saskatoon
|
65,800
|
$1.97
|
$2.84
|
$1.93
|
$1.87
|
$1.48
|
|
Montreal
|
75,200
|
$2.17
|
$3.25
|
$2.50
|
$1.73
|
$1.26
|
|
Regional Demand in Units
|
46,300
|
32,200
|
29,400
|
37,500
|
39,100
|