Module 12.2 Data science Spreadsheet modeling
xlsx
keyboard_arrow_up
School
New York University *
*We aren’t endorsed by this school
Course
MISC
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
xlsx
Pages
13
Uploaded by GeneralMinkPerson933
Staffing Model
Data Input
Hours/work day
6.5
Month
May
June
July
August
Desired throughput
700
750
800
825
Days in month
22
20
22
22
Model
Files per month
Products
Product Mix
Hours per file
May
June
July
August
May2
Product 1
22%
3.5
154
165
176
181.5
539
Product 2
17%
2
119
127.5
136
140.25
238
Product 3
13%
1.5
91
97.5
104
107.25
136.5
Product 4
12%
5.5
84
90
96
99
462
Product 5
9%
4
63
67.5
72
74.25
252
Product 6
9%
3
63
67.5
72
74.25
189
Product 7
6%
2
42
45
48
49.5
84
Product 8
5%
2
35
37.5
40
41.25
70
Product 9
3%
1.5
21
22.5
24
24.75
31.5
Product 10
1%
3.5
7
7.5
8
8.25
24.5
Misc
3%
3
21
22.5
24
24.75
63
Total
100%
Output
May
Hours per employee per month
143
Total hours predicted
2089.5
FTEs required
14.6
Hours per month
June3
July4
August5
577.5
616
635.25
255
272
280.5
146.25
156
160.875
495
528
544.5
270
288
297
202.5
216
222.75
90
96
99
75
80
82.5
33.75
36
37.125
26.25
28
28.875
67.5
72
74.25
June
July
August
130
143
143
2238.75
2388
2462.625
17.2
16.7
17.2
Newsvendor Model
Data
Selling price $ 18.00 Unit cost $ 12.00 Discount price $ 9.00 Initial Trial:
Demand
41
Purchase Quantity
44
Revenue
Cost
Profit
Quantity Sold
41
$ 738.00 $ 492.00 $ 246.00 Surplus Quantity
3 $ 27.00 $ 36.00 $ (9.00)
Total
44
$ 765.00 $ 528.00 $ 237.00 Purchase Quantity with Normal Distributiom
Mean
80
Standard Deviation
20
Unit Overage Cost Co
$ 3.00 Unit Underage Cost Cu
$ 6.00 Critical Ratio
0.67 Cu/(Co+Cu)
Z-value
0.431 Normsinv(Critical ratio)
Purchase Quantity
89 =mean+z*SD
Simulation:
Demand
90
84
82
56
33
90
Quantity Sold
89
84
82
56
33
89
Surplus Quantity
0
5
7
33
56
0
Revenue $ 1,595.06 $ 1,550.62 $ 1,531.63 $ 1,299.40 $ 1,093.34 $ 1,595.06 Cost $ 1,063.37 $ 1,063.37 $ 1,063.37 $ 1,063.37 $ 1,063.37 $ 1,063.37 Profit $ 531.69 $ 487.25 $ 468.25 $ 236.03 $ 29.96 $ 531.69
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
53
93
73
125
112
53
89
73
89
89
35
0
16
0
0
$ 1,276.18 $ 1,595.06 $ 1,450.70 $ 1,595.06 $ 1,595.06 $ 1,063.37 $ 1,063.37 $ 1,063.37 $ 1,063.37 $ 1,063.37 $ 212.81 $ 531.69 $ 387.32 $ 531.69 $ 531.69
Overbooking
Data
Capacity
300
Selling price
$ 120 Overbooking cost
$ 100 Initial trial
Overbooking # rooms
10
Total reservation limit
310
Customer Demand
312
Reservation made
310
Cancellation
6
Customer arrivals
304
Overbooking customers
4
Overbooking costs
$ 400 Booking revenue $ 36,000 Final revenue $ 35,600 Optimal solution
Mean cancellation
6
SD of the cancellation
3
Unit overage Cost Co
$ 100 Unit underage Cu
$ 120 Critical ratio
0.55
Z-value
0.11
Overbooking # of rooms to accept
7
Total reservation limit
307
Customer demand
312
Reservation made
307
Cancellation
4
Customer arrivals
303
Overbooking customers
3
Overbooking cost
$ 300 Booking revenue
$ 36,000 Final revenue
$ 35,700
Investment
Minimum
Maximum
1. Life Insurance
5% $ 2,500 $ 5,000 -0.5
2. Bond mutual funds
7% $ 30,000 $ 100,000 1.8
3. Stock mutual funds
11% $ 15,000 $ 100,000 2.1
4. Savings account
4% $ - $ 100,000 -0.3
Total invest amount limit
$ 100,000 Weighted risk amount limit
$ 100,000 Note: if there is not limit, make it to the total funds available.
Model
Amount Invested
Life Insurance
$ 5,000.00 Bond mutual funds
$ 30,000.00 Stock mutual funds
$ 28,333.33 Savings account
$ 36,666.67 Total amount invested
$ 100,000.00 Total weighted Risk
$ 100,000.00 Total Expected Return
$ 6,933.33 Annual Return
Risk Factor/ Dollar
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
Investment
Minimum Maximum
1. Life Insurance
5% $ 2,500 $ 5,000 2. Bond mutual funds
7% $ 30,000 None
3. Stock mutual funds
113% $ 15,000 None
4. Savings account
4% None
None
Investment
Risk Factor/Dollar Invested
1. Life Insurance
-0.5
2. Bond mutual funds
1.8
3. Stock mutual funds
2.1
4. Savings account
-0.3
Annual Return
Laboratory Location Model
Data
Location
X Coordinate
Y Coordinate
Trips/month
A1
0
0
5
A2
20
80
25
A3
60
30
20
A4
100
100
35
A5
70
110
15
New lab Location
71
89
1
Location
Eucilidean
Weighted
A1
113.9
569.7
A2
52.2
1304.0
A3
59.9
1197.8
A4
30.7
1074.7
A5
21.3
318.8
4465.03
Total Euclidean Distance with wrighted by the trips/month
0
20
40
60
80
100
0
20
40
60
80
100
120
Lab 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
120
Total money avaliable for investment:
2500
There are three stocks considered:
Stock
A
B
C
Price/share
25
15
30
Return/Share
8
7
11
Find the highest return and limits the investment to what you have. Model
Stock
A
B
C
Price
25
15
30
Return
8
7
11
No. of Stocks
1
165
0
Amount
25
2475
0
Profit
8
1155
0
Total Amount
2500
Available
2500
Total Profit
$ 1,163
ElectroMart
Data
Location
X Coordinate
Y Coordinate
Truckloads
A
18
15
12
B
3
4
18
C
20
5
24
D
3
16
12
E
10
20
18
New Location
12
12
1
Location
Distance
Weighted Distance
A
7 80.5233455334401
B
12
216.6
C
11
254.8
D
10
118.3
E
8 148.683968037674
Total
819.0
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
2
4
6
8
10
12
14
16
18
20
22
0
5
10
15
20
25
Y Coordinate