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.