Open a blank MS Excel workbook SHOW ALL EXCEL FORMULAS Daisy had to go through k
ID: 375113 • Letter: O
Question
Open a blank MS Excel workbook
SHOW ALL EXCEL FORMULAS
Daisy had to go through kitchen manager training to run the hotdog stand, so she knows that proper storage of meat products is very important. To provide that cold storage and handling, a local restaurant charges one cent per hotdog per calendar day and Madison, Daisy’s assistant, has a calendar that shows 365 days per year. The same restaurant has the food contracts with vendors, so they handle the orders, but charge $60 to process, receive, and put each order in their freezer on top of their storage costs.
Daisy holds hotdog sales throughout the year, but is very selective about her days. She averages 160 sale days per year, with an average of 263 hotdogs sold on each day.
Daisy is very concerned about storage and ordering costs as hotdog sales are likely to increase when people discover that by sharing a hotdog with her on the library lawn they can support the kids. Daisy is thinking that something like the table on the right might allow her to figure out important metrics and see how they would change as the inputs change over time.
a) Create a “hotdog” worksheet in your workbook into which Daisy can have Madison enter the number of sale days per year, the average sales per sale day, the cost per order, and the holding costs per hot dog per day and have it compute the optimal quantity she should order. Some extra “helper” cells (like the total annual demand) are OK with Daisy, but she wants the spreadsheet to round the number of hotdogs because she doesn’t know how to do that fancy math herself.
b) If Daisy orders the quantity that minimizes the sum of the order and carrying costs, compute how many orders per year will she need to make?
c) Given the above ordering quantity compute the annual carrying costs, order costs and total costs for this operation?
d) Compute the order cycle in sales days?
e) The restaurant is changing their pricing to $65 per order and .9 cents per hotdog per day. Add another
column next to the current numbers, that has all the same computations and do a comparison with this new pricing so that Daisy can see if her costs will be higher or lower after the change.
Explanation / Answer
Ans A to D:
Ans E:
With the changes: (and the comparison with A to D is mentioned)
D Demand 42080 units per day 160*263 units d Daily sales 115 units D/w Ans A K Ordering cost 60 $ per order h Holding cost 3.65 $ per unit per day w Working days 365 Days EOQ (Sqrt(2*k*D/h) 1176 units Ans A N No of orders 36 times Total orders/EOQ Ans B Toc Total ordering cost annualy 2147 $ N*K Ans C Thc Annual Holding cost 2147 $ EOQ*h/2 Ans C TC Total cost 4293 $ (Toc+Thc) Ans C Length of order cycle 10 days (Total days/No of orders) Ans DRelated Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.