Manage the inventory of your product

In this exercise, you will learn how to manage a company’s product inventory using Excel.

Proper inventory management is crucial for any business. It ensures that products are available when needed, prevents overstocking, and helps avoid lost sales due to stockouts. By using Excel’s IF function, you will create a simple yet effective system to monitor stock levels and determine when to reorder products.

You will:

  • Set up a spreadsheet to track the stock levels of products in your inventory.
  • Apply the IF function to automate decisions:
    • Display "In Stock" when the product quantity is above a safe threshold.
    • Show "Restock Needed" when the quantity falls below the threshold, indicating it’s time to reorder.
  • Use conditional formatting to visually highlight products that need restocking, making it easy to identify which items require action.
Manage Stock and New Price

Click on the following button to start the training.

Stock management — IF, logical test and nested IF

Flag items below a stock threshold with a logical test, then a message-returning IF, then a 2-level nested IF — 10 guided steps, write the formula then fill it down.

Write the logical test in C3

1 / 10

In C3, write a logical test that checks whether the current stock (column B) is below the threshold (cell B1, locked with $ signs).

Stock mgmt — Q1 · 225

2 / 10

Fill down the test (column C)

Stock mgmt — Q2 · 226

Write the IF function in D3

3 / 10

In D3, write an IF function: if the stock is below the threshold ($B$1), display "Restock". Leave the third argument empty.

Stock mgmt — Q3 · 227

4 / 10

Fill down the action (column D)

Stock mgmt — Q4 · 228

Calculate the gap in E3

5 / 10

In E3, calculate how many units are left before the stock reaches the threshold.
Subtract the threshold ($B$1) from the current stock (B3).

Stock mgmt — Q5 · 229

6 / 10

Fill down the margin (column E)

Stock mgmt — Q6 · 230

Write the IF function in F3

7 / 10

In F3, write an IF function: if the stock is below the threshold, calculate the quantity to order (threshold − stock + 30 units of margin). Otherwise, display 0.

Stock mgmt — Q7 · 231

8 / 10

Fill down the order (column F)

Stock mgmt — Q8 · 232

Write the nested IF in G3

9 / 10

In G3, write a nested IF:
• If the stock is critical (below half the threshold), order 50 units
• Otherwise, if the stock is below the threshold, order 30 units
• Otherwise, display 0

Stock mgmt — Q9 · 233

10 / 10

Fill down the adjusted order (column G)

Stock mgmt — Q10 · 234

Your score is

0%

Excel-Tutorial.com, your training partner.

The purpose of Excel-tutorial.com is to help users master Excel through a series of tests, tutorials, and online training programs. Whether you're a beginner or an advanced user, our resources are designed to build your skills step by step.

Our certified experts have developed detailed training programs tailored to all skill levels. Our courses cover various Excel topics, from basic data processing to advanced data analysis and visualization. You'll find clear instructions, easy-to-follow lessons, and structured content that ensures steady progress.

Excel-tutorial.com stands out for its interactive teaching approach. Our lessons include exercises, real-life use cases, and engaging quizzes to solidify your understanding. This lets you apply your new skills immediately to real-world tasks, making learning both practical and impactful.

Plus, you can track your progress and test your knowledge with quizzes that provide instant feedback. Our goal is to help you gain confidence in Excel, one concept at a time. Join us at Excel-tutorial.com and start transforming your data skills today!

Most Viewed Posts