Excel Exam
I have an excel exam and I am horrible at it. Need help!
MGMT 3
6
4
Midterm Exam Spring 2021
Name:__________________________
Problem 1: Amazon – Online Sales
Amazon sells books, music, and many other items over the Internet and is one of the pioneers of online consumer sales. Amazon, based in Seattle, Washington, started by filling all orders using books purchased from a distributor in response to customer orders. As it grew, the company added warehouses, allowing it to react more quickly to customer orders. In
200
9, Amazon had about 20 warehouses in the United States and another 30 in the rest of the world. It uses the U.S. Postal Service and other package carriers such as UPS and FedEx to send products to customers. Outbound shipping-related costs at Amazon in 2009 were almost $2 billion.
With the Kindle, Amazon has worked hard to increase sales of digital books. As of 2009, Amazon offered more than 460,000 books in digital form. The company has also added a significant amount of audio and video content for sale in digital form.
Amazon has continued to expand the set of products that it sells online. Besides books and music, Amazon has added many product categories such as toys, apparel, electronics, jewelry, and shoes. In 2009, one of its largest acquisitions was Zappos, a leader in online shoe sales. This acquisition added a lot of product variety. According to the Amazon annual report, this required creating 121,000 product descriptions and uploading more than 2.2 million images to the Web site! In 2010, another interesting acquisition by Amazon was diapers.com. Unlike Zappos, this acquisition added little variety but considerable shipping volumes.
Several questions arise concerning how Amazon is structured and the product categories it continues to add:
1. What advantages does selling books via the Internet provide over a traditional bookstore? Are there any disadvantages to selling via the Internet?
2. Should Amazon stock every product it sells?
3. What advantage can bricks-and-mortar players derive from setting up an online channel? How should they use the two channels to gain maximum advantage?
4. What advantages/disadvantages does the online channel enjoy in the sale of shoes /diapers relative to a retail store?
5. For what products does the online channel offer the greater advantage relative to retail stores? What characterizes these products?
Problem 2.
The Green Company is a retailer of gourmet bottled pickles that purchases its products from Whole, a gourmet food manufacturer. Green buys units (from Whole) at a price of $15 per unit and sells them to customers at $45 per unit. Currently Whole produces to Green’s order and delivers all requirements at the start of the period. Leftover inventory at the end of the season will be donated to a charity organization for free. The demand is lost when Green does not have inventory. Whole’s production cost is $10 per unit.
|
Demand(units) |
Probability |
|
|
100 |
0.2 |
|
| 200 |
0.3 |
|
|
300 |
||
|
400 |
1. Given the current information, how many units of products should Green stock to satisfy demand? What is the associated profit for Whole and Green and for the supply chain in total?
2. If Whole and Green were one integrated company, how many units would be stocked? What is the associated profit for the supply chain?
3. Why is there a discrepancy between the previous two questions? What steps would you recommend to coordinate this supply chain?
Problem 3.
The First Bank of Lafayette has a common office for mortgage and commercial credit processing. The process involves several departments: data collection, data verification, loan pricing, loan closing, and loan maintenance. For each of the departments, there are a number of clerks working in parallel. All requests pass through each of the 5 stages.
|
Stage |
Number of Clerks |
Set-up Time |
Processing Time |
|||||
|
Data Collection |
6 |
20 minutes |
10 minutes |
|||||
|
Data Verification |
4 |
25 minutes |
3 minutes |
|||||
|
Loan Pricing |
15 minutes |
|||||||
|
Loan Closing |
4 minutes |
|||||||
|
Loan Maintenance |
Data Regarding orders received shows 40 orders per day for mortgage processing and 20 orders per day for commercial credit. Given that all orders are processed in order of arrival and that every order has to go through all 5 stages, provide the average lead time for First Bank to complete the process. Assume 8 hours of work per day, 5 days per week, and 50 weeks per year.
>Problem .
1. What advantages does selling books via the Internet provide over a traditional bookstore? Are there any disadvantages to selling via the Internet? 2. Should Amazon stock every product it sells? . What advantage can bricks-and-mortar players derive from setting up an online channel? How should they use the two channels to gain maximum . What advantages/disadvantages does the online channel enjoy in the sale of shoes /diapers relative to a retail store? . For what products does the online channel offer the greater advantage relative to retail stores? What characterizes these products? 0
0 0 100 0.2 0
0 0 200 0
0.2 0 0 300 0
0.3 0 0 400 1 0
0 .00
per unit per unit =
Supply Chain Exp. Profit 100 200 400 Given the current information, how many units of products should Green stock to satisfy demand? What is the associated profit for Whole and Green, respectively? What is the profit for the supply chain in total? Sales Leftover Capacity Shortage E[sales] E[left] E[short] Salvage Value $ – 0 per unit Whole’s Production Cost $ 10.00 per Unit Underage Cost cu= 100 If Whole and Green were one integrated company, how many units would be stocked? What is the associated profit for the supply chain? 3. Why is there a discrepancy between the previous two questions? What steps would you recommend to coordinate this supply chain? 40 20 day Lead Time (read from ) 6 orders per day day 4 minutes
orders per day day 4 10 minutes orders per day day 4 20 minutes orders per day day 6 10 minutes 20 minutes orders per day day Queue Template queuing computations
5
0 15 /s 0.9375 15 1 1 0.9375 1 1.0 0.0625 0 1 0.0625 0.0625 1.0 1.0 .0625
1 0 0 1.0 1.0 2 0 6406
0 1.0 1.0 0.9375 3 0 1
0 1.0 1.0 4 0 976 0 1.0 1.0 0.9375 5 0 771
0 1.0 1.0 48
0 1.0 1.0 78 983
0 1.0 1.0 49671
0 1.0 1.0 96403 0 1.0 1.0 7787797
0 1.0 1.0 0 0 1.0 1.0 8094743
0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 6
0 1.0 1.0 5597 2
0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 0 1.0 1.0 1.0 1.0
1
Your Answer:
Your Answer:3
advantage?
Your Answer:
4
Your Answer:5
Your Answer:Problem 2.1
No
Cooperation
Capacity=
0
Sales
Leftover Capacity
Shortage
Demand
Probability
Demand Satisfied
Extra Capacity
Deficient Capacity
Cumulative Probability
10
0.2
20
0.3
0.5
30
0.
7
40
E[sales]
E[left]
E[short]
Exp.Value
2
6
Exp. Demand Satisfied
Exp. Extra Capacity
Exp. Capacity Deficiency
0 0
26
Green’s Selling Price
$ 45.00
per unit
Green’s Exp. Profit =
Green’s purchase price
$
15
Whole’s Exp. Profit =
Salvage Value
$ – 0
Supply Chain Exp. Profit
Whole’s Production Cost
$ 10.00
per Unit
Possible Capacity
Retailer Exp. Profit
Manufacturer Exp. Profit
Underage Cost cu=
Overage Cost co=
300
Critical Ratio =
Your Answer:Problem 2.2
Cooperation Capacity= 0
Demand Probability Demand Satisfied Extra Capacity Deficient Capacity Cumulative Probability
100 0.2 0 0 100 0.2
200 0.3 0 0 200 0.5
300 0.2 0 0 300 0.7
400 0.3 0 0 400 1
Exp.Value 260 Exp. Demand Satisfied Exp. Extra Capacity Exp. Capacity Deficiency
0 0 260 Green’s Selling Price $ 45.00 per unit
Green’s purchase price $ 15.00 per unit
Supply Chain Exp. Profit =
Possible Capacity Retailer Exp. Profit Manufacturer Exp. Profit Supply Chain Exp. Profit
Overage Cost co= 200
300
Critical Ratio = 400
Your Answer:Problem 2.3
Your Answer:Problem 3
Mortgage arriving order arrival rate:
orders per
day
working hours
8
hours per day
Commerical Credie order arrival rate:
orders per day
working minutes
480
minutes per day
Total aarival rate:
Total Lead Time:
Stage
Number of Clerks
Set-up Time
Processing Time
Processing Rate
Queue Template
Data Collection
20 minutes
10 minutes
Data Verification
25
3 minutes
Loan Pricing
15 minutes
Loan Closing
4 minutes
Loan Maintenance
M/M
/s
lambda/mu
0.
9
37
s-1
THE ARRIVAL RATE SHOULD BE LESS THAN THE OVERALL SERVICE RATE!
Arrival rate
Assumes Poisson process for
Service rate
16
arrivals and services.
s factorial =
Number of servers
(max of 40)
P(0) =
0.0625
Utilization
93.75%
P(n)
1.0
P(0), probability that the system is empty
Nq, expected queue length
14
0.05859375
N, expected number in system
15.0000
0.0549
31
Lq, expected time in queue
0.0514984
13
L, expected total time in system
1.0000
0.048
27
23
Probability that a customer waits
0.04526
22
6 0
0.04
24
33
38
7 0
0.0
39
12
8 0
0.037
29
9 0
0.0
34
17
10 0
0.0
32
11
0.030730106
12 0
0.0
28
13 0
0.0270088822
14 0
0.0253208271
15 0
0.0237382754
16 0
0.0222546332
17 0
0.0208637
18
18 0
0.0
19
36
19 0
0.0183372527
20 0
0.0171911744
21
0.016116726
22
0.0151094306
23
0.0141650912
24
0.013279773
25
0.0124497872
26
0.0116716755
27
0.0109421958
28
0.0102583085
29
0.0096171642
30
0.0090160915
31
0.0084525858
32
0.0079242991
33
0.0074290305
34
0.006964716
35
0.0065294213
36
0.0061213325
37
0.0057387492
38
0.0053800774
39
0.0050438225
40
0.0047285836
1.0 1.0
1.0 1.0
1.0 1.0
1.0 1.0
1.0 1.0
1.0 1.0