Do Economic Order Quantity Analysis

Economic order quantity (EOQ) analysis offers a business a key advantage -- ordering just the right amount of stock at the right price right on time. The business owner must know some basic facts first, and be able to make some reasonable assumptions, but then this tool proves invaluable. It seems that even Mother Nature needs this for allocating resources to avoid often having "feast-or-famine" (too much or not enough). Learn to use this tool in the following steps.

Steps

The Tutorial

  1. Open a new Excel workbook and create 3 worksheets: EOQ Data, EOQ Chart and Saves. Save the workbook into a logically named file folder.
  2. Please see "Things You'll Need" at the bottom of this article, because the formula for EOQ = sqrt((2AP)/S) where "sqrt" means find the square root, A = the Annual Quantity used of sold in units, P = Cost of placing a Purchase Order and S = Annual cost of carrying one unit of stock (in inventory for one year). Or if an in-house production run is used instead of making a purchase, then substitute for P the setup costs (for example, the labor costs to adjust machines).
  3. Set Preferences. Open Preferences in the Excel menu. Recommended Settings: Set General to R1C1 Off and to Show - 10 Most Recent Documents; Edit - set all the Top options to checked except Automatically Convert Date System. Display number of decimal places = blank (for integers preferred). Preserve display of dates, and set 30 for 21st century cutoff; View - show Formula Bar and Status Bar, hover for comments and all of Objects; show gridlines and set all boxes below that auto or checked; Chart - show chart names and data markers on hover. Leave the rest unchecked for now; Calculation -- Automatically and calculate before save, Limit iteration checked, max iterations 100, max change .000,000,000,000,01 w/o commas as this problem may come to involve precise goal seeking and save external link values and use 1904 date system; Error checking - check all; Save - save preview picture with new files and Save Autorecover after 5 minutes; Ribbon -- all checked except Hide group titles and Developer.
  4. Begin the EOQ Data worksheet by entering the formula abbreviations and descriptions in columns A and B, and 3 in H and I:
    • A1: E
    • B1: (Economic) Order Size
    • A2: E/2
    • B2: Avg. Inventory in Units
    • A3: A
    • B3: Annual Quantity Used in Units
    • A4: A/E
    • B4: Number of Purchase Orders
    • A5: S
    • B5: Annual Cost of Carrying 1Unit of Stk 1 Yr
    • A6: S(E/2)
    • B6: Annual Carrying Cost
    • A7: P(A/E)
    • B7: Enter an = and then the string concatenation formula "Annual Pchs Order Cost @ "&TEXT(P_,"$0.00")&"/PO"
    • A8: C
    • B8: Total Annual Cost
    • Select cell H13 and enter 26.042 962 129 9847 (without spaces) and Insert Name Define Name P_ for cell $H$13. Format Cell Fill Yellow, Border Black bold Outline. Font Bold.
    • Select cell I13 and enter P_ an = and then Purchase Order Cost
    • Select H14 and enter the formula an = and then the formula SQRT((2*A_*P_)/S_). There will be an error -- it will be fixed shortly. Insert Name Define name EOQ for cell $H$14.
    • Select I14 and enter EOQ an = and then SQRT(2AP/S)
    • Select H15 and enter an = and then the formula A_/EOQ
    • Select I15 and enter PO's/Yr an = and then A/EOQ
  5. Create the main Data Table.
    • In cell C1, enter an = and then the formula L1/100. Select cell range D1:J1 and with D1 active, enter an = and then the formula C1*2 and Edit Fill Right. Select cell K1 and enter an = and then the formula L1*.8 and select cell L1 and enter an = and then the formula A_
    • Select cell range C2:L2 and with C2 active, enter an = and then the formula C1/2 and Edit Fill Right.
    • Select cell C3 and enter 3000 and Insert Name Define Name A_ to cell $C$3 and Format Cell Fill Yellow and Border Black bold Outline. Select cell range D3:L3 and enter an = and then the formula A_.
    • Select cell range C4:L4 and enter an = and then the formula C3/C1 and Edit Fill Right.
    • Select cell C5 and enter 2.5 and Format Cells Fill yellow and Border Black bold Outline and Insert Name Define name S_ to cell $C$5. Select cell range D5:L5 and with D5 active, enter an = and then the formula S_.
    • Select cell range C6:L6 and enter an = and the formula C5*C2 and Edit Fill Right.
    • Select cell range C7:L7 and enter an = and then the formula P_*C4 and Edit Fill Right. Format Cells Border Black Bottom Single Underline.
    • Select cell range C8:L8 and enter an = and then the formula C6+C7 and Edit Fill Right. Format Cells Border Black Bottom Double Underline.
  6. Enter the Production Run Note.
    • In cells B10 to B13, enter this note: The Annual PO Cost may also be ... B11: thought of as a SETUP CHARGE ... B12: for a PRODUCTION RUN! In which case ... B13: the EOQ is the Economic Run size.
  7. Complete the Static Summary Table (does NOT automatically update):
    • In cell C10 enter E, in C11 enter: Order Size, D10: S(E/2), D11: Annual Carrying Cost, E10: P(A.E) Annual Pchs Order, E11 with quotes: an = and then the formula " Cost @ "&TEXT(P,"$0.00")&"/PO", F10: C, F11: Total Annual Cost
    • Copy cell range C1:L1 and Paste Special Values Transpose to cell C12 so that the row of data is now a column of data.
    • Copy cell range C6:L6 and Paste Special Values Transpose to cell D12 so that the row of data is now a column of data.
    • Copy cell range C7:L7 and Paste Special Values Transpose to cell E12 so that the row of data is now a column of data.
    • Select cell range F12:F21 and with F12 active enter an = and the formula D12+E12,
  8. As an alternative to Step 7, reference via formula each cell just transposed, so that the lower summary will automatically update.
  9. Insert the EOQ answer into the tables.
    • Insert a column between the top Order Sizes of 240 and 480 and Edit Copy F1:F8 into the top of this new column and change the top number, the E (Economic Order Quantity), to an = and then the formula EOQ and Format Cells for this cell and the one beneath it Number Number Decimal Places 0. It should have an E=250 and a Total C of $625.02
    • Insert a rows in the lower table between 240 and 480, In the leftmost cell, enter an = and then the formula EOQ and Format Cells Number Number Decimal Places 0, Proceed right to column D and enter an = and input the formula S*C16/2; proceed right to column E and input the formula P_*(A_/EOQ); proceed right and copy the formula from the above cell to the current cell.
    • Select cell ranges with the EOQ data and Format Cells Font Blue or Red or Bold, etc.

Explanatory Charts, Diagrams, Photos

  • (dependent upon the tutorial data above)
  1. Create the EOQ Chart.
  2. Select cell range C10:F21 and with the Chart Wizard or the Ribbon select Charts All/Other and scroll down to Scattered Smoothed Line Scattered and a small chart should appear on the data page. Copy or Cut this chart and paste it into cell A1 of the EOQ Chart worksheet and pull it open via using the double-headed hover-arrow at the bottom right of the chart, clicking and expanding the chart, In Chart Layout I added Axis Titles and a Chart Title, then via menu item Insert Picture WordArt, a text box was inserted and made a note about the EOQ and Purchase Orders.The axis were scaled so that the major units crossed at the EOQ point of 250 units and $625, The main Total Cost Line was made bright red and another curve's color was changed as well to olive green.
  3. Select on EOQ Data the entire Data block and Format Cells Border Black bold Outline and holding down the shift key, Copy Picture, activate the Saves worksheet and Paste Picture, Select the the Chart on the EOQ Charts worksheet and click within the chart area and with the shift key depressed Edit Copy Picture and activate the Saves worksheet and Paste Picture below the data.
  4. It is possible and recommended to copy the EOQ data of A1:L21 and paste it down to an area below it two rows at A24 and Insert Name Define name SetupCosts to cell H36 where the old yellow-filled Purchase Order Cost is, enter 50, and move right a cell and change the label to Setup Costs. Change the formula in H37 in the EOQ formula to read an = and then the formula SQRT((2*A_*SetupCosts)/S_) and change the label in cell I37 to read EOQ and an = and then SQRT(2ASetupCosts/S). Change the label in cell A30 from P(A/E) to sc(A/E) and move right 1 cell and change the description from ="Annual Pchs Order Cost @ "&TEXT(P_,"$0.00")&"/PO" to an = and then the formula "Setup Cost @"&TEXT(SetupCosts,"$0.00")&"/Production Run". Then Edit Go To cell range C30:L30 and change the formula from "=P_*C4" to an = and then the formula SetupCosts*C4 and Edit Fill Right. (Replace the single right hand black border line in cell L(n).) Copy and transpose via Paste Special Values Transpose the Setup Costs in cell range C30:L30 to the column in the lower tier of the report at E34.
    • Change the Heading of column E in the lower tier from "P(A/E); Annual Pchs Order" to sc(A/E); Setup Costs: and down a cell to an = and then the formula "Cost @ "&TEXT(SetupCosts,"$0.00")&"/Production Run"; widen the column to fit. Change cell C38 to be an = and then H37. Change cell G24 to be = and H37. Change cell D39 to be = and S*C39/2. Check to make sure that the EOQ data matches the formula result in column H. Change cell H38 to be = and then A_/H37 and the label in cell I38 to read Production Runsz an = and then A/EOQ. Set C3 to = and C26 and Format Cells Fill Sky Blue. Set C5 to = and then C28 and Format Cells Fill Sky Blue. Change C26 to 1000 units and change C28 to 5. Select cell H39 and enter 24 and select cell H40 and enter = and then H39-H38. Select cell I39 and enter Contract: semi-monthly production runs. With H40 the active cell, do Tools, Goal Seek, H40, 0, by changing cell C28. Select row 28 and Format Cells Number Number Decimal Places 2. Copy and Paste Special Values Transpose again for C29:L29 to D35 and C30:L30 to E35. Copy C24:L24 and Paste Special Values Transpose to C35.
  5. If the lower tier report matches the upper tier for the new lower report, you're done! If not, I'm not sure why not, so check through the logic of all this. For one thing, the annual usage or sales could be quite different and we have not changed that variable name everywhere it occurs. And the carrying costs would be higher, given profit expectations in a special order situation.
  6. If preferred, copy the values from C1:L1 and Paste Special Values Transpose to C12; also copy the values from C6:L6 and Paste Special Values Transpose to D12;and also copy the values from C7:L7 and Paste Special Values Transpose to E12. 33.3 PO's is quite a high number of PO's to be sending out -- in fact, it's ridiculous. The new ways of doing business work better and are vastly more profitable.
  7. Lastly change the last line of the note in cell B36 to read the ERS is the Economic Run Size. and change cell I37 to read ERS an = and then without quotes "SQRT(2ASetupCosts/S)}}". If deciding to make a new chart, make sure the title reads Economic Run Size instead of EOQ.

Modern Business Considerations

  1. "Incidentally, in various machine shops, the high cost of setup time has historically been a critical factor that tends to boost the size of production runs and the optimal size of inventory. The increasing use of automated machine tools, which are set up and controlled by computer programs, has reduced setup time enormously. It is now much easier to switch from the production of one item to another. This phenomenon has led toward lower production runs and lower optimal inventory sizes."(1)
  2. Basic Assumptions of the EOQ Model:

Calculus Method

  • The EOQ formula as labels may be derived via Calculus in the following manner, seeking a minima:
  • C an = and then AP/E + ES/2
  • Set dC/dE an = and then without quotes "to 0; S/2 - AP/E^2 = 0}}"
  • SE^2 and = and then 2AP
  • E^2 an = and then 2AP/S
  • dC/dE an = and then without quotes "-AP/E^2 + S/2"
  • E an = and then without quotes "SQRT(2AP/S)"

Helpful Guidance

  1. Make use of helper articles when proceeding through this tutorial:
    • See the article How to Do Cost Volume Profit Analysis for a list of articles related to Excel, Geometric and/or Trigonometric Art, Charting/Diagramming and Algebraic Formulation.
    • For more art charts and graphs, you might also want to click on Microsoft Excel Imagery, Mathematics, Spreadsheets or Graphics to view many Excel worksheets and charts where Trigonometry, Geometry and Calculus have been turned into Art, or simply click on the category as appears in the upper right white portion of this page, or at the bottom left of the page.



Warnings

  • ERRORS: If you have errors or error values, either the sheet in incomplete and needs further input or Lookup Tables for critical variables or perhaps you've made a mistake somewhere along the line. If the instructions have been completed and there are still errors, select the cell that has the error value that is furthest left and topmost first. Look for a typo in a formula or unmatched parentheses. Possibly, a Defined Name is wrong -- they need to be input into the formulas exactly as they were defined. Do Insert Name Define to check. If you have DIV/0!, I do not, so look for a variable that somehow did not get filled in with a value perhaps. At any rate, what you want to do is select the cell with the error, and after checking all those typical errors, do Tools Auditing Trace Precedents and/or Trace Error. If fixing all the topmost leftmost errors does not fix the rest of your errors on your worksheet, you may need to do it the hard way, from the bottom right upwards then leftwards; that is the slow but sure way to fix all errors.
  • Also, errors in your chart data will most likely plot as zeroes. This may be acceptable or desirable even. However, if too many lines (or curves) are returning to 0, it may indicate a logical flaw in the data -- or too many tiny values and then perhaps rescaling the chart is needed by inspecting the horizontal and vertical axes and changing them to zero in on the problem. Hover over or click on a data marker on the series plot and then do a search in the proper column by value for that value, and identify its precedents.

Things You'll Need

  • You need to know these inventory carrying costs per year, or estimate them, such as:
    • Desired annual return on inventory investment, 10%*$20= $2.00
    • Rent, Insurance, Taxes per unit per year .50
    • Carrying Costs Per Unit Per Year $2.50
  • Costs per purchase order:
    • Clerical costs, software and hardware depreciation, any stationery and postage, telephone/fax, filing system $26.04
  • The expected annual usage or sales of the item. Units = 3,000

Related Articles

Sources and Citations

  • (1) "COST ACCOUNTING - A Managerial Emphasis", Charles T. Horngren, 1972, Prentice-Hall, Inc., Englewood Cliffs, NJ, ISBN 0-13-180034-5, pp. 545
Retrieved from "https://kipkis.com/index.php?title=Do_Economic_Order_Quantity_Analysis&oldid=353466"