Microsoft Excel

3 minute read

Excel Challenge 46: Inventory Optimization With Excel Solver

Ilgar Zarbaliyev

Ilgar Zarbaliyev

Join the Excel conversation on Slack

Ask a question or join the conversation for all things Excel on our Slack channel.

Can you optimize inventory levels across multiple stores and warehouses while minimizing costs? Beware — balancing stock levels, meeting demand, and keeping transportation costs low without running into stockouts is a tough balancing act!

Everything you need to participate in the challenge can be found on this page. To take part:

  • First, watch the challenge video and read the instructions below the video.
  • Review the previously published video(s) and article(s) on which the challenge is based.
  • Download the Excel worksheet you will use to complete the challenge tasks.
  • Put yourself to the test!

Take the challenge

Download the exercise file to solve this challenge!

Want to chat about your approach and process with other Excel enthusiasts? Join our Slack channel to share your insights and questions with like-minded learners.


The challenge 🤺

Scenario:

You are managing inventory for 5 stores (Store 1 to Store 5) supplied from 3 warehouses located in Toronto, Montreal, and Vancouver. Each warehouse holds a specific amount of stock, and you must decide how much inventory to allocate to each store to minimize costs.

The cost structure includes:

  • Holding Cost: Cost of holding inventory at each store.
  • Stockout Cost: Penalty for failing to meet store demand.
  • Transportation Cost: Cost of transporting goods from warehouses to stores.

We have 2 datasets:

inventory and cost data
Inventory & cost data
warehouse stock and transportation costs
Warehouse stock & transportation costs

Constraints:

  1. Each store must meet its monthly demand.
  2. The total inventory allocated from a warehouse cannot exceed its available stock.
  3. Shipments must be whole numbers (no fractional units).

Task:

Optimize inventory levels across multiple stores to minimize total inventory costs while meeting customer demand and preventing stockouts.

Method:

Using Excel Solver, determine how much inventory to send from each warehouse to each store to minimize total inventory costs (holding + transportation + stockout costs).

This challenge will help you manage your inventory efficiently, balance stock levels across warehouses, and prevent shortages or excess inventory.

Are you up to the challenge? Share your solution in our GoSkills Learners Community.


The solution

Did you manage to tackle all the tasks? Check out my solution and see how it stacks up against yours. Have a different method? We'd love to hear it—there’s always more than one way to solve an Excel challenge!

If you liked this challenge and want to learn how Excel can simplify some of your most complicated tasks, check out this Advanced Excel course taught by Microsoft MVP Ken Puls. Plus, we’ve got plenty of other Excel challenges and a resource hub packed with tutorials, tips, and tricks to boost your expertise.

Until next time, happy Excel-ing! 👋

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial

Join the Excel conversation on Slack

Ask a question or join the conversation for all things Excel on our Slack channel.

Ilgar Zarbaliyev

Ilgar Zarbaliyev

Ilgar creates content on data analysis, business intelligence, and innovative training methods. With over 20 years of experience, he is the Training Program Manager at Novum LLC, a Microsoft MVP, and MCT Regional Lead for the CEE region. Currently pursuing a postgraduate degree in AI and ML from Texas McCombs School of Business, he also serves as a European Training Foundation Ambassador, promoting modern educational practices. In his free time, he enjoys watching films and football. Connect with him on LinkedIn here.