Min Max Inventory Calculation in Excel (with Easy Steps)

The fundamental goal of inventory control is to guarantee that sufficient items or resources are available to fulfill demand without generating surplus inventory. That brings us to the following lesson – creating a step-by-step process for establishing the Min Max Inventory Calculation in Excel.

Key Stock Level Metrics in an Inventory System?

Safety Stock

Safety Stock = [Maximum Consumption * Maximum Delivery Time] – [Average Consumption * Average Delivery Time]

Order Quantity

Order Quantity = Damages + Safety Stock

Min Inventory Level

Minimum Inventory level = Order Quantity – [Average Consumption × Average Delivery Time]

Max Inventory Level

Maximum Inventory Level = Safety Stock + Order Quantity – [Minimum Consumption * Minimum Delivery Time]

Min Max Inventory Calculation in Excel: With Easy Steps

The below dataset has a column titled Product Name that contains some computer accessories, and other columns keep information about their monthly sales from January to December. We will determine the necessary Order Quantity and Safety Stock throughout the following steps. With the help of two typical formulas, we will also establish the Min Max Inventory Calculation.

Make sure you download the sheet from the practice workbook linked below when using these methods.

Method 1 – Use a Date Model to Calculate Min Max Inventory in Excel

Select the DemandSupply sheet.
Highlight the four columns titled Product Name, Min Consumption, Max Consumption, and Average Consumption.

Make another table for the Delivery Time in the B12:E18 range.

Open the SafetyStock sheet.
Select three columns: Product Name, Damages, and Safety Stock.

Choose the B4:F10 range to display the Min Max Inventory Level.

Method 2 – Apply MIN, MAX, ROUND and AVERAGE Functions to Get Consumptions of Inventory

The MIN function finds the lowest digit in a list. Meanwhile, the MAX function multiplies together the most significant value in a collection. The ROUND function in Excel provides a result rounded to a specified precision. The AVERAGE function finds the average of the numbers you provide. The Consumption data will be determined using these procedures.

Open the DemandSupply sheet and select the C5 cell.
Input the following equation in the Formula bar.

=MIN(Dataset!C5:N5)

Press the Enter or Tab key to see the result.

Drag the AutoFill Handle to cell C10 to apply the same formula to other cells in the Min Consumption column.

Choose cell D5.
Type for following in the Formula bar.

=MAX(Dataset!C5:N5)

Press the Enter or Tab key to see the result.

Drag the AutoFill Handle to cell D10 to apply the same equation to other cells in the Max Consumption section.

Choose cell E5.
Type the following equation into the Formula bar.

=ROUND(AVERAGE(Dataset!C5:N5),0)

Press the Enter or Tab key to see the result.

You can also use the AutoFill Handle icon to apply the same formula to other cells in the Average Consumption portion.

Formula Breakdown

=MIN(Dataset!C5:N5)

To understand this formula, you must be familiar with the following Excel function:

MIN Function

MIN(Dataset!C5:N5)

The MIN function finds the least significant digit in a list. Enter an Exclamation mark between the sheet’s name and the range to refer to data or cells in other sheets. In this example, by involving the MIN function, we find 2.

=MAX(Dataset!C5:N5)

For this formula to make sense, you need to know how to use the following Excel function:

MAX Function

MAX(Dataset!C5:N5)

When called, the MAX function always returns the highest number in the given range. In this example, by using the MAX function, we get 26.

Formula Breakdown

=ROUND(AVERAGE(Dataset!C5:N5),0)

To understand this formula, you must be familiar with the following Excel function:

ROUND and AVERAGE Functions

AVERAGE(Dataset!C5:N5)

In Excel, you can get the mean of a set of integers using the AVERAGE function. To refer to data or cells in other sheets, you must use an Exclamation sign between the sheet name and range. In this demonstration, by involving the AVERAGE function, we find 12.8333333333333.