In this assignment youll solve the basic economic order quantity (EOQ) model in Excel using two methods: (A) a trial-and-error approach to find a very good but approximate answer, and (B) the EOQ formula to find the exact answer. Set up your spreadsheet similar to that shown below.

The situation: Roxie is responsible for purchasing the paper used in all copiers and laser printers at Budco. After looking at her records, Roxie has found that demand for paper averages 600 boxes per month. The price of a box of paper is $20 (regardless of the number ordered). Placing and handling an order costs $66. Annual unit holding costs per box are 25% of the unit price. Last year, Roxie ordered paper once every two months, but she wants to know if another ordering policy would be cheaper.

	A	B	C	D	E	F
1	Annual Demand		boxes/year		B. EOQ Formula
2	Unit Price		per box		Optimal	Annual
3	Ordering Cost		per order		Order	Order+Holding
4	Annual Holding Cost		per box/year		Quantity Q*	Costs at Q*
5
6	Trial-and-Error Method
7		Annual	Annual	Annual
8	Order	Ordering	Holding	Ordering+Holding
9	Quantity (Q)	Costs	Costs	Costs
10	25
11	50
12	75
:	:

1. Trial-and-Error (Approximate) Method

• Order Quantity: type in 25 for cell A10; then use the formula =25+A10 for cell A11 and copy this formula down, until Q reaches 1200.
• Annual Ordering Costs (DS/Q): for cell B10, use =$B$1*$B$3/A10; then copy formula down.
• Annual Holding Costs (HQ/2): for cell C10, use =$B$4*A10/2; then copy formula down.
• Annual Ordering + Holding Costs: for cell D10, use =B10+C10; then copy formula down.

Make a scatter (XY) chart of the Annual Ordering + Holding Costs (y-axis) vs. Order Quantity (x-axis).

2. EOQ (Exact) Formula

• Find the optimal order quantity Q* in cell E5 using the EOQ formula discussed in lecture and Ch. 12 of the text. You can find the square root of x in Excel with the function SQRT(x). In your E5 and F5 cell formulas you should reference other cells that contain the relevant data rather than typing the numbers themselves into the cell formula.

1. Based on the Trial-and-Error (Approximate) Method

A1. Approximately what order quantity minimizes total annual ordering + holding costs? ____ boxes

2. Based on the EOQ (Exact) Method

B1. What order quantity Q* minimizes total annual order + holding costs? _____ boxes

B2. What Excel cell formula is required for Annual Ordering + Holding Costs in cell F5? _____

B3. At Q*, what are the total annual ordering + holding costs? _____

B4. How many times per year will Roxie place an order of size Q*? _____

B5. Last year, Roxie ordered every 2 months. How much were her total annual ordering+holding costs?

_____________ (Hint: First ask yourself how many boxes would be ordered each time if you ordered exactly once every 2 months?)

B6. What is the percentage reduction in annual ordering + holding costs achieved by Roxie in following the optimalinventory policy instead of last years ordering policy? _______