Assignment2 (1)
xlsx
keyboard_arrow_up
School
Yale University *
*We aren’t endorsed by this school
Course
MISC
Subject
Communications
Date
Nov 24, 2024
Type
xlsx
Pages
18
Uploaded by ogwenogatamu
Optimization modelling
max
9
(7, 2)
8
Transportation
8
Transhipment
8
8
9
9
Readability & flexibility*
1
60
Aggregate planning
Investment planning
Fixed cost manufacturing
Set covering
Locating and assigning service centers
UNIVERSITY OF SASKATCHEWAN
EDWARDS SCHOOL OF BUSINESS
COMM 393.3 (01 & 03)
Spreadsheet Modelling for Business Decisions
Assignment #2
Instructor:
Jafar Sadeghi
This assignment is to be done
individually.
Due:
Nov. 14, 2023, 11:59 pm
On each worksheet of this workbook perform the asked actions using Excel, save it regularly o
computer, and then submit it to Course Tools (Canvas) by the due date and time above.
Please
your workbook “A2-X” where X = your first and last name.
*Readability
means that the worksheet has clear and logical layout, separate
parts (inputs, uncertain variable, decision variable, output), clear bold
headings, specific (cell) fill colours, good alignment, possibly cell comments,
and textboxes for explanation.
Flexibility
means that an input is not entered in a formula directly, but it is
referred by cell reference.
on your
e name
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Inputs
Initial
inventory
500
Initial no. of workers
100
Production per worker per month
40
Wages per worker per month
$1,500
Hiring cost per worker
$1,600
Firing cost per worker
$2,000
Material cost per unit
$15
Holding cost per unit per month
$3
Month 1
Month 2
Month 3
Month 4
Decision variables
No. of workers from previous month
100
94
94
50
No. of workers to hire at beginning of month
0
0
0
0
Aggregate planning
Reconsider the shoe manufacturer problem of Session 10.
Its speadsheet model is reproduced
below for your convenience.
The Solver parameters are also embedded in this worksheet.
Change the spreadsheet model to include the following:
If necessary, a worker can produce up
to
5 pairs of shoes during overtime in each month.
A worker is paid $52 per pair of shoes
produced during overtime.
a.
Change the spreadsheet model and re-solve it using Solver.
b.
Compare the optimal solutions with (this problem) and without overtime (the problem in
Session 10).
What is the difference?
Does the difference make sense?
Briefly explain.
Hints:
1.
Insert 2 rows in Inputs for Maximum overtime production per worker per month
and
Overtime production cost per unit
2.
Insert 4 rows below the row containing No. of workers during the month
i.
Dedicate a row to decision variables for No. of units to produce in overtime during each
month
ii.
Dedicate a row to Maximum no. of units to produce in overtime during each month and
enter the
formulas for these.
Note that these depend on the No. of workers during the month
and Maximum overtime production per worker per month
3.
Change the No. of units produced to include the overtime production
4.
Insert a row in Output and add the overtime cost
5.
In Solver Parameters window, add overtime production cells as decision variables (in addition
to no. of hires and fires) and add the max OT production constraints.
Note:
Ensure that the Integer Optimality % in Solver is 0.
No. of workers to fire at beginning of month
6
0
44
0
No. of workers during the month
94
94
50
50
No. of units produced
3760
3760
2000
2000
No. of units available
4260
5020
2020
2020
>=
>=
>=
>=
Demand
3000
5000
2000
1000
End-of-month inventory
1260
20
20
1020
Output
Hiring cost
$0
Firing cost
$100,000
wage cost
$432,000
Material cost
$172,800
Holding cost
$6,960
Total cost
$711,760
Desired
>=
1000
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Inputs
Cash to invest
$100,000
Investment A
Investment B
Investment C
Investment D
Investment E
Outlays (beginning of year)
Year 1
$1.00
$1.00
$1.00
Year 2
$1.00
Year 3
$1.00
Year 4
Payouts (beginning of year)
Year 1
$1.00
$1.00
$1.00
Year 2
$1.15
$1.00
Year 3
$1.28
$1.00
Year 4
$1.40
$1.15
$1.32
Decision Variables
Cash invested
$100,000
$0
$50,000
$125,000
$237,500
Beginning of
Cash inflow
Cash outflow
Year 1
$100,000
>=
$150,000
Year 2
$237,500
>=
$237,500
Year 3
$189,000
>=
$125,000
Year 4
$597,250
Output
Year 4 cash inflow
$597,250
Investment planning
You have $100,000 to invest. There are five possible investments with irregular payouts.
You can
invest any amount in any of five investments.
You can only invest the amount of cash you have on
hand plus payouts from previous investments.
You can only invest at the beginning of the years.
The payouts will be proportional to the amount of investment (outlay).
Investment A:
For each dollar Invested at the beginning of year 1, receive payout of $1.40 at the beginning of year
4.
Investment B:
For each dollar Invested at the beginning of year 1, receive payout of $1.15 at the beginning of year
2
Investment C:
For each dollar Invested at the beginning of year 1, receive payout of $1.28 at the beginning of year
3
Investment D:
For each dollar Invested at the beginning of year 3, receive payout of $1.15 at the beginning of year
4
Investment E:
For each dollar Invested at the beginning of year 2, receive payout of $1.32 at the beginning of year
4
Determine the investment strategy that maximizes the amount of cash inflow at the beginning of
year 4 subject to cash inflow
≥
cash outflow at the beginning of each year 1 to 3.
Hint:
In the inputs area, keep outlays and payouts separate.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Outlet
Currently available
Demand
Excess supply
Shortage
A
4
20
B
14
10
C
5
20
E
17
5
F
22
10
G
7
20
The demand and supply with the distance matrix can be shown in the following table
Demand
Location
Demand
D
2
6
16
7
-
5
15
2
-
4
-
6
13
a) the locations with excess supply of cars are 2,4,5,7,8
b) the locations that need cars are 1,3,6
Transportation
A rent-a-car company has to fulfill the excess demand for cars in 4 cities:
D
E
F
G
2
3
4
6
The company has excess supply of cars in 3 cities:
A
B
C
6
7
5
The distances between the cities (in 100 km) are:
D
E
F
G
A
7
11 3
2
B
10 12 7
1
C
9
15 11 10
Formulate this problem as a transportation model and use Solver's Simplex LP to determine how
cars are to be transported from each supply city to each demand city in order to minimize total d
travelled subject to meeting the demands and not exceeding the supplies.
Hint:
Use Transportation model a, i.e., a rectange range of decision variables, as opposed to a co
is easier.
E
3
F
4
G
6
Supply
Supply
A
6
B
7
C
5
the cars have to move from the following origin to destination
Destination
Origin
D
E
F
G
A
7
11
3
2
B
10
12
7
1
C
9
15
11
10
Location
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
w many
distances
olumn.
It
Inputs
Origin
Dest
Unit Cost
Company
1
3
$356.0
1
4
$379.0
2
3
$195.0
2
4
$247.0
3
5
-
3
6
-
4
5
-
4
6
-
Decision variables
Origin
Dest
Flow
Company
Outflow
1
3
356
1
0
1
4
379
2
0
2
3
195
2
4
247
DC
Outflow
3
5
-
3
0
3
6
-
4
0
4
5
-
4
6
-
Customer
Inflow
5
0
Transhipment
A company can manufacture 2 units of a product per day at its Montreal factory.
Two custom
in Calgary and the other in Saint John, each require 1 unit of the product on an emergency ba
products can be flown from the factory directly to the customer.
But it might be cheaper to fi
Toronto, and then to the customer. The air freight costs per unit are shown in the table below
Unit air freight costs
From/To Toronto
Calgary
Saint John
Montreal
$144
$356
$379
Toronto
$195
$247
Formulate this problem as a transhipment model and use Solver's Simplex LP to determine w
routes to use in order to minimize the total air freight cost subject to meeting the demands an
exceeding the capacity.
Hint:
It will be easier if you index the cities.
For example, Montreal 1, Toronto 2, Calgary 3, a
John 4.
If you use the name of cities as origin or destination, type the name carefully because
function expects exact text match.
Even an extra space will result in wrong answer.
1
2
6
0
Output
Transport cost
$369,411
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Plant
DC
Customer
DC
Customer
Unit Capacity
<=
0
<=
0
Inflow
=
551
=
626
Demand
=
551
mers, one
asis.
The
first fly to
w.
which
nd not
and Saint
e SUMIF
3
4
5
6
1
2
3
4
5
6
=
626
Problem 13.47
Inputs
Model A
Model B
Labour hours/unit
1.5
2.5
Profit/unit
$100
$150
Fixed cost for equipment
$35,000
$40,000
Decision variables
Binary, 1 if produce, 0 if not
0
0
No. of units to produce
0
0
<=
<=
<=
used
Max
Labour hours
4
<=
2.5
Fixed Cost Used
75000
<=
40,000
Output
Total revenue
$75,250
Total Fixed cost
$75,000
Profit
$250
Fixed cost manufacturing
A manufacturer produces two products: Model A and Model B. The relevant data are as follows:
Model A Model B
Fixed equipment rental cost
$35,000 $40,000
Unit profit
$100
$150
Labour hours used per unit 1.5
2.5
A total of 1000 hours of labour are available.
The equipment cost is a fixed cost
that is incurred only if a positive
amount of a product is produced.
Formulate this problem as a spreadsheet model and use Solver's Simplex LP to determine how many of each
model the company should produce in order to maximize its total profit subject to constraint on labour hours.
Note:
Don't use the IF function
.
Don't account for equipment cost after
determining the number of units to
produce.
A
B
C
D
E
F
G
H
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
:
surgeon 1
surgeon 2
surgeon 3
surgeon 4
surgeon 5
surgeon 6
Hire:
1
1
0
0
0
0
2
Operation 1
1
1
1
Operation 2
1
1
1
Operation 3
1
1
1
Operation 4
1
1
1
Operation 5
1
1
1
1
Operation 6
1
1
So,
Hiring only 2 surgeons
(surgeon-1 and 2) will
cover all the
operations.
Set Covering
Six types of surgical operations are to be performed in a new hospital.
The hospital management ha
surgeons have applied for the jobs.
The type of operation each surgeon is qualified to perform is ind
below.
The management wishes to know which surgeons to hire in order to minimize the number o
the constraint that each type of operation can be performed by at least one surgeon.
Formulate this
model and use Simplex LP of Solver to solve it.
Surgeon 1
Surgeon 2
Surgeon 3
Surgeon 4
Surgeon 5
Surgeon 6
Operation 1
X
X
Operation 2
X
X
Operation 3
X
X
Operation 4
X
X
Operation 5
X
X
X
Operation 6
X
Hint:
This problem is similar to Locating airline hubs.
>=
1
>=
1
>=
1
>=
1
>=
1
>=
1
has advertised the jobs.
Six
dicated by an X in the list
of surgeons hired subject to
s problem as a spreadsheet
Solution:
From
To \ From
NY
Chicago
LA
Atlanta
Total received
Demand
Bases opened (Y)
0
1
1
1
To
Northeast
0
500
0
0
500
500
Midwest
0
400
0
0
400
400
West
0
0
300
0
300
300
South
0
0
0
400
400
400
Total sent
0
900
300
400
Sent - M*Y
0
-99
-699
-599
NY
Chicago
LA
Atlanta
Fixed costs
Variable cost
Northeast
Midwest
West
South
Total cost
2050000
$
100,000
$
100,000
$
100,000
$
100,000
$
1,100
$
1,200
$
1,900
$
1,300
$
1,400
$
1,000
$
1,700
$
1,300
$
1,900
$
1,500
$
1,100
$
1,500
$
1,400
$
1,200
$
1,400
$
1,050
So,
Bases will be open in Chicago,
LA, and Atlanta.
Chicago serves Northeast and
Midwest
LA serves West
Atlanta serves South
The total minimum cost is
$2,050,000
Locating and assigning service centers
The State of Texas frequently audits companies doing business in Texas.
Because these companies often have headquarters outside the state, auditors must be sent to out-of-state locations.
Each year, auditors must make the following number of trips to the cities in each region:
Northeast
500
Midwest 400
West
300
South
400
Sate of Texas is considering basing auditors in one or more of the following cities: New York, Chicago, Los Angeles, and Atlanta.
The cost of sending an auditor from any of these bases to a region
is given below.
Cost per trip per auditor
From
To
NY
Chicago
LA
Atlanta
Northeast
$1,100
$1,200
$1,900
$1,300
Midwest
$1,400
$1,000
$1,700
$1,300
West
$1,900
$1,500
$1,100
$1,500
South
$1,400
$1,200
$1,400
$1,050
Determine the bases to open and regions to assign to each open base in order to minimize the total annual trip cost subject to assigning each region to exactly one base, having a total of 2 bases,
and assigning maximum of 2 regions to a base.
A
B
C
D
E
F
G
H
I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help