Excel Challenge 46: Inventory Optimization With Excel Solver

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!

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:

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! 👋