BA 356 Central Texas College Minimum Total Cost & Order Policies Excel Worksheet
This week the problems are 6.1, 6.2, 6.3 and 6.4. All work/calculations must be shown in Excel QM and submitted through the Assignment.
6.1) Lisa Simpson sells beauty supplies. Her annual demand for a particular skin lotion is 1,000 units. The cost of placing an order is $20, while the holding cost per unit per year is 10 percent of the cost. This item currently costs $10 if the order quantity is less than 300. For orders of 300 units or more, the cost falls to $9.80. To minimize total cost, how many units should Lisa order each time she places an order? What is the minimum total cost?
6.2) Furniture Fanatics Inc., uses 20,000 loads of lumber per year. A load of lumber costs $500 and the carrying cost is 10 percent of the unit cost. The cost to order is $200 per order and the lead-time is three working days. Assuming 200 working days, determine:
(a) the economic order quantity.
(b) the reorder point.
(c) number of orders per year.
(d) working days between orders.
6.3) Annie Lennox recently took over a cleaning supply store. Her predecessor always ordered carpet shampoo in quantities of 100 units. Annie is reevaluating this policy. Based on her analysis, the cost to place each order is $35 and the holding cost is $8 per shampoo bottle per year. The annual demand for this product is 2500 bottles. Should Anne change the current order policy and, if so, how much can she save?
6.4) Candy Incorporated stocks bubble gum game cards, an item that has a normally distributed demand during the reorder period with a mean of 12 dozen boxes and a standard deviation of two dozen boxes. If it is desirable to experience a stock-out only 10 percent of the time, what is the appropriate safety stock?
The rubric for problems is below:
Rubric Name: W1 Problem Rubric
Submission fully addresses questions listed in the problem. Answer is consistent and supported by Excel QM spreadsheets/results.
Response fully answers all parts listed in the question. Answer is consistent and supported by Excel QM spreadsheets as a separate document. All calculations are shown.
Response partially answers all parts listed in the question. Answer is consistent and supported by Excel QM spreadsheets as a separate document. Most calculations are shown.
Response partially answers few parts listed in the question. Answer is not consistent with supporting Excel QM spreadsheets. Most or all calculations not provided.
Each part of each question is fully answered and explained. All Excel QM or additional calculations are shown if they are required.
Most questions are fully answered and explained. All Excel QM or additional calculations are shown if they are required.
Few question are answered and explained. Few to no Excel QM or additional calculations are shown if they are required.
· Work is original.
· Adheres to all spelling and grammar rules
· Work is original
· Few grammar and spelling errors
· All or portions of the work are not original
· Follows few or does not follow grammar and spelling rules