Assignment 3
Prepared by: Syed Mustafa Ahmed
Student ID: 500-513-715
Assignment #3
Professor: Amy Peng
Course: ECN 620
Question #1: Choosing optimal public transit fleet size Excel Solutions Q1
Q2
K
SUMPRODUCT
Solutions
5
2
5
1
Max (
290
Constraints
1
-1
0
<=
0
1
-1
-3
<=
0 1
5
>=
0
1
2
>=
0
1
5
>
0
How this table was created? The constrains and other valuable information that was given by the question and with manual analysis was inserted into the Excel spreadsheet in an appropriate format. The Excel Solver function was utilized in order to get the solutions.
Interpretation: This table gives us the optimal values of fleet size we should consider to solve the problem. Using the manual analysis we already know that the only possible cases are 3 and 4 respectively. But case 3 is most optimal because profit is maximized in it( 290) while in case 4( 215).
Question #2: Assigning milk to three variety stores problem.
Excel Solutions
How this table was created?
Choices values are binary and were chosen at random before the Excel Solver was computed. Number of gallons were given by the question alongside its respective probability. Revenue was also highlighted in the question. Demand one is $2 for one gallon, demand one for two gallons is $2-0.5=1.5 in which 0.5 is the opportunity cost, demand three for three gallon is 2-0.5-0.5=1. Same method is followed for demand two and three. Expected revenue of store one at one gallon is calculated by Demand one at one gallon*probability at one gallon + demand two at one gallon * probability at two gallons + demand three at one gallon * probability at three gallons. In numerical it will be (2*.06+2*0+2*0.4=2). Same method was considered to calculate expected revenue for all stores at two and three gallons respectively. Calculation are given below. Objective function was calculated by using Excel "SUMPRODUCT" of choices and expected revenue.
Expected
Student ID: 500-513-715
Assignment #3
Professor: Amy Peng
Course: ECN 620
Question #1: Choosing optimal public transit fleet size Excel Solutions Q1
Q2
K
SUMPRODUCT
Solutions
5
2
5
1
Max (
290
Constraints
1
-1
0
<=
0
1
-1
-3
<=
0 1
5
>=
0
1
2
>=
0
1
5
>
0
How this table was created? The constrains and other valuable information that was given by the question and with manual analysis was inserted into the Excel spreadsheet in an appropriate format. The Excel Solver function was utilized in order to get the solutions.
Interpretation: This table gives us the optimal values of fleet size we should consider to solve the problem. Using the manual analysis we already know that the only possible cases are 3 and 4 respectively. But case 3 is most optimal because profit is maximized in it( 290) while in case 4( 215).
Question #2: Assigning milk to three variety stores problem.
Excel Solutions
How this table was created?
Choices values are binary and were chosen at random before the Excel Solver was computed. Number of gallons were given by the question alongside its respective probability. Revenue was also highlighted in the question. Demand one is $2 for one gallon, demand one for two gallons is $2-0.5=1.5 in which 0.5 is the opportunity cost, demand three for three gallon is 2-0.5-0.5=1. Same method is followed for demand two and three. Expected revenue of store one at one gallon is calculated by Demand one at one gallon*probability at one gallon + demand two at one gallon * probability at two gallons + demand three at one gallon * probability at three gallons. In numerical it will be (2*.06+2*0+2*0.4=2). Same method was considered to calculate expected revenue for all stores at two and three gallons respectively. Calculation are given below. Objective function was calculated by using Excel "SUMPRODUCT" of choices and expected revenue.
Expected