Inventory Model Simulation with Spreadsheets

The term inventory means the amount of materials or resources in hand, which includes raw material, work in process, finished goods, stores, and spares. Inventory management (IM) is the procedure in which inventory is measured and planned according to prearranged rules such as economic lot size for order or production, safety stock, minimum, maximum, and order level. IM pertains primarily to the administration of established policies, systems, and procedures in order to reduce the cost of inventory.

Inventory Management (IM)

IM is to the process of ordering, storing, and using a company's/firm's inventory. These include the management of raw materials, components, and finished products as well as warehousing and processing such items. IM uses a variety of data to keep a record of the goods as it moves through the process, including lot numbers, serial numbers, cost of goods, quantity of goods and the dates when they move through the process. IM helps in minimizing cost, optimize fulfillment, provide better customer service, and avoid loss from theft, damage, and return. The figure below shows an example of an inventory management system.

Economic Order Quantity (EOQ) Model

F. Harries first developed the EOQ concept in the year 1916. EQE is the amount of inventory to be ordered at one time towards minimizing annual inventory cost. It is one of the methods of IM, which minimizes the total holding ordering cost for the year. EOQ is the method which solves the problem of the material manager. The figure shows the EOQ model cost curve.

The basic EOQ model gives the equation to estimate EOQ. Following formula used to estimate EOQ:

Where, A = Annual consumption or demand, B = Cost to placing a single order and C = Cost to hold one unit inventory for a year.

The EOQ is a company's/firm's optimal order quantity that reduces its total costs related to ordering, receiving, and holding the inventory. For many firms, inventory is the main benefit owned by the firm, and these productions must carry sufficient stock to meet the needs of consumers. This model is best suited in situations where demand, ordering, and holding costs remain constant over the period of time.

Basic Terminology of EOQ

The inventory system may be classified depending on the nature of the variables. The variables are various costs, such as shortage cost, carrying cost, ordering cost, demand, lead time, reorder cycle time, input rate, and shortages. Demand may be known and constant (static), or known and variable (dynamic), or it may be estimated in an inventory system.

• Shortage Cost: Shortage cost is the cost of having a shortage and unable to meet demand from stock. Shortages of inventory levels may result in the cancelation of orders which lead heavy losses in the sale that results in losses in goodwill, profit even the business itself.
• Holding Cost: Holding costs are cost related to storing inventory that remains unsold. Holding costs are one element of total inventory costs, along with ordering and shortage costs. A company's holding costs include the price of goods damaged or ruined, as well as that of storage space, labor, insurance, investment, and pilferage, etc.
• Annual Demand: Annual demand is the amount that is needed during a year for the inventory item.
• Single Order Cost: Total cost experienced to place a single order.
• Lead Time: It is the delay pertinent for inventory management purposes. This delay is the summation of supply delay and the reordering delay.

How does the EOQ Model work?

IM is very important for a business to run smoothly and efficiently. Example, a sugar manufacturing company, if it keeps running out of sugarcane. Sugarcane is the primary raw material which keep the sugar manufacturing company process running. The sugar company maker needs to plan the quantities of sugarcane so they never run out and are able to produce sugar whenever they need too. But IM is not just about bulking up with supplies. If too much sugarcane is stocked and there is not as much demand for sugar, the sugarcane would go bad, which would cause financial losses to the business. IM encourages optimization of inventory. The IM makes sure that the sugar maker has almost the exact amount of sugarcane, which is just enough to make sugar, which matches with the demand of sugar consumers who order sugar from various sources. The figure shows four wide parameters for managing inventory effectively.

Simulation with Spreadsheet

Assign Basic Parameters and Create Quantity Column

• First, you will create and assign the basic parameters such as Annual Demand (A), Ordering Cost (B), Cost to Hold Each Unit (C), and each unit cost. Now create a Quantity column start with 5 at an interval of 5. After the initial two values drag it down for 50 values.

Create Ordering Cost Column

• After creating a Quantity column, create an Ordering Cost column using the following formula:

• In this formula, annual demand and each unit cost will be constant, and quantity will be variable. So make it constant by putting $ as shown in the snapshot.

• After calculating one value, select the value and drag it down for the remaining values—this is how it will automatically generate Ordering Cost for each given quantity.

Create Holding Cost Column

• After creating Ordering Cost column, create a holding cost column by using the following formula:

• In this formula, each unit holding cost will be constant and the quantity will be variable. So make it constant again by putting $ as shown in the snapshot.

• After calculating one value, select the value and drag it down for the remaining values—this how it will automatically generate Holding Cost for each given quantity.

Create Total Cost Column

• After creating holding Cost column, create total cost column by adding ordering and holding cost.
• After adding one value, select the value and drag it down for the remaining values—this how it will automatically generate Total Cost for each given quantity.

Create Line Chart

• After creating the three columns, it is now time to create a line chart for all of the columns.
• To create a line chart, go to Insert menu and select the line chart option from the drop-down list shown on the right-hand side of the screen. Your Line chart should look like the below snapshot.

Apply Conditional Formatting

• After creating a chart for ordering, holding, and total cost. It is now time to find the minimum value of order quantity.
• To find the minimum order quantity, you need to find the minimum total cost using conditional formatting.
• First, go to format and click on conditional formatting. It will open a window on the right side of the screen.

• In this window:

• In following Bottom 10 Items..., fill 1 in the cell and select the color. I have chosen the Light Red with Dark Red Color.

• This is how you will get the Bottom 1 Item or one minimum value of total cost, which is 3535.714 and Economic order quantity is 70.

Pros and Cons of EOQ Model

Small business owners want an efficient inventory system to maximize profit. The EOQ model is a frequently used element of a continuous review inventory system. It is based on a formula that estimates the best reasonable amount of items a business should order to minimize costs and maximize value when re-stocking inventory (The Advantages & Disadvantages of Economic Order Quantity (EOQ)). The main advantage of this model is the customized recommendations provided regarding the most economical number of units per order.

Efficient EOQ models need complete data to estimate several figures. The cons of the EOQ model as below:

• It requires complicated math calculations.
• The model depends on the assumption of just one-product, and the equation does not permit for merging several different products in the single order.
• EOQ model does not take into account purchase discounts that could be achieved by purchasing inventory in bulk.

Conclusion

Congratulations, you have made it to the end of this tutorial!

In this tutorial, you have covered a lot of details about Inventory Management. You have learned about Inventory Management, the EOQ model, the terminology used in the EOQ model, and the pros and cons of the EOQ model. IM has to do with keeping accurate records of goods that are ready for shipment. IM is very crucial for keeping costs down while meeting all the necessary requirements. Demand and supply is a delicate balance and IM helps with these two to ensure the balance is uninterrupted.

Hopefully, you can now utilize the EOQ concept to analyze your own datasets. Thanks for reading this tutorial!

If you would like to learn more about spreadsheets, take DataCamp's Conditional Formatting in Spreadsheets course.