BUSI 2012 – Business Analytics
Excel Project #1
Economic Order Quantity Analysis
Purpose: The purpose of this assignment is to apply your learning from Modules 1 through 5 to solving
an economic order quantity analysis. You will write formulas and create a line chart.
Caution: This IS an individual project. You can get help from the instructor if you don’t understand
something. Do not turn in a copy of someone else’s work as your own. It is very easy to identify a copy
of someone else’s work, especially when it is an electronic file.
Deliverables (120 points): An Excel spreadsheet that solves for economic order quanitty in a general
way. Post your assignment solution to the D2L dropbox.
• File Name for Deliverables: Excel Project 1.xls
Scenario: This assignment is based upon an analysis concept found in economics, accounting, finance,
and management – economic order quantity, also known as EOQ analysis. This analysis determines the
number of units to order (and the number of times to order) to minimize the sum of inventory
management costs (ordering costs and carrying costs).
In order to apply EOQ analysis, four basic inputs are necessary: (a) projected annual demand for a
product, (b) the purchase cost per unit of the product, (c) the carrying cost as a percentage of total cost,
and (d) the administrative costs of placing an order.
Economic Order Quantity (units). The formula for economic order quantity computed in units is:
EOQ (units) =
√ 2 x Annual Demand x Order Cost
Cost Per Unit x Carrying Cost Percentage
Order Frequency. The order frequency is based upon the EOQ in units and the projected annual demand:
N = Annual Demand
EOQ (units)
Total Inventory Costs. The formulas for total inventory management costs, excluding purchase costs of
the product, are:
• Total Ordering Costs = Order Frequency x Order Cost Per Order.
• Total Carrying Costs = Quantity Ordered x Cost Per Unit x Carrying Cost Percentage
2
• Total Inventory Costs = Total Ordering Costs + Total Carrying Costs
Page 2
EOQ Chart. An EOQ chart plots total carrying costs, total order costs, and total inventory management
costs for various order quantities. The resulting chart will show the minimum total inventory
management cost where the total carrying costs and total order costs intersect. This is lowest total
inventory management cost.
Instructions: Create an Excel worksheet to solve for economic order quantity in a general way.
1. Diagram 1 shows the “design” layout of the spreadsheet. Input elements are shown in cells with
a green, pink, or yellow background. These are to be typed by you. The cells with white
backgrounds are formulas. Each formula element is identified with the word formula, meaning
you must type the appropriate formula in that cell.
2. Diagram 2 shows sample results of computations for 20 different order quantities from the
sample inputs. The sample results demonstrate the type of formatting desired.
3. Diagram 3 shows the economic order quantity chart.
Diagram 1: Layout for CVP spreadsheet Inputs and Formulas
Page 3
Formula Elements:
• Economic Order Quantity (Formula1) = computation using EOQ formula on page 1. Use the
SQRT() function under the Formula tab, Math and Trig option. Write your formula in the box
labeled “Number” in the dialog.
• Order Frequency (Formula2) = computation shown on page 1
• Total Inventory Costs (Formula3) = computation shown on page 1
• Text for Chart (Formula4) = Use concatenation to create the following text string:
Economic Order Quantity = Approximately 17 Orders of 583 units
where the numbers in the text string are cell references to the analysis results (computations).
The concatenation function can be found under the Formulas tab, Text item on the ribbon in the
Function Library group. Use/nest the round function as appropriate.
Note: When different inputs are entered, the text for chart should automatically update with new
results.
Formula Columns:
• Analysis (Formula5) – Rows 2 through 19. Add 1 to the element in the row above.
• Quantity (Formula6) – In Row 1 start with the input “Beginning Quantity”. In rows 2 through
19, add the input “Quantity Increment” to the element in the row above.
• Order Frequency (Formula7) – In rows 1 through 20, compute using the formula Annual
Demand divided by Quantity for each Quantity.
• Total Ordering Costs (Formula8) – In rows 1 through 20, compute using the formula: Order
Frequency x Order Cost Per Order.
• Total Carrying Costs (Formula9) – In rows 1 through 20, compute using the formula: Quantity
divided by 2 x Cost Per Unit x Carrying Cost Percentage
• Total Inventory Costs (Formula10) – Sum “Total Ordering Costs” and “Total Carrying Costs”
in each row.
Page 4
Diagram 2: Sample Results
Use the inputs (values in the yellow cells) and the results in this diagram to test your formulas.
The chart (see next page) will have the quantity column as the x-axis and Total Ordering Costs, Total
Carrying Costs, and Total Inventory Costs as separate series (lines) on the y-axis. To start:
1) Select the Total Ordering Costs, Total Carrying Costs, and Total Inventory Costs headings and data.
2) Insert a line chart. The chart will have 1, 2, 3, 4, etc. as the x-axis.
3) Change the x-axis to refer to the quantity column as follows:
a) Click on the chart
b) Click on “Select Data” button under the “Chart Tools | Design” tab.
i) In the dialog, click “Edit” above the panel labeled “Horizontal Axis Labels”
(1) For the axis label range, select the data rows in the Quantity column.
(2) Click OK to close the axis label range dialog
c) Click OK to close the Select Data dialog.
4) Edit your chart further as appropriate
Page 5
Diagram 3. Sample EOQ Chart
CHART ELEMENTS:
The basic chart elements include the title, legend, x-axis title, x-axis labels, y-axis labels; the plot of total
ordering costs, total carrying costs, and total inventory costs. In addition:
For the next item, search Google for “how to associate a cell with text box in a chart” or use the
procedure demonstrated in class.
• Create a text box in the chart for EOQ, then associate the contents of the “Text for chart” formula
(identified on page 3) with the text box. Format with bold font, purple color. Position this text
box above the plotted graph.
ANALYSIS QUESTIONS:
To answer each of the analysis questions, select a row of cells below the chart to create a wide cell into
which to type your answer. On the Home tab, select merge and center, then left-justify. Then type your
answer.
Situation #1. Suppose improved information technology will allow more automated ordering, reducing
the order cost per order to $5. How will this change the recommended inventory policy and total
inventory costs?
Situation #2. Suppose your manager decides to place orders weekly rather than follow the recommended
inventory policy. What order cost per order would make this an optimum decision? (Hint: Try different
order costs per order in your spreadsheet until the order frequency is 52, which is once a week.)