Guide to Using the Inventory Planning by Month Spreadsheet

Introduction

The Inventory Planning by Month spreadsheet is a powerful tool designed to help you manage and forecast your inventory needs. This guide will walk you through how to use each section of the spreadsheet to optimize your inventory planning and avoid stockouts while maintaining efficient inventory levels.

Spreadsheet Overview

Your spreadsheet contains several interconnected sheets that work together to provide a comprehensive inventory planning system:

Monthly Sales - Historical sales data and trends
Inventory Forecasting - Future stock level predictions
Stock On Hand - Current inventory snapshot
Product List - Consolidated product information
Plan - Editable planning sheet for manual adjustments
Final Plan - Combined plan with forecasts and adjustments
Incoming Inventory - Track expected inventory arrivals
Stock Burn - Projection of inventory depletion over time

How to Use Each Sheet

Monthly Sales

This sheet provides historical sales data with monthly breakdowns.


What to look for:
- Review historical sales patterns to identify seasonal trends
- Analyze growth rates to understand product performance
- Use this data as a foundation for future planning decisions

Inventory Forecasting

This sheet predicts future inventory needs based on sales history.



What to look for:
- Projected sales volumes for upcoming months
- Recommended reorder points
- Suggested purchase orders

How to use it:
- Review the forecasted numbers to validate against your business knowledge
- Use these predictions as a starting point for your planning

Stock On Hand

This is a comprehensive view of your current inventory. This sheet is hidden as default.

What to look for:
- Current stock levels across all SKUs
- Products with critically low inventory
- Potential overstock situations

Product List

A consolidated list of all products extracted from the Stock On Hand sheet. This sheet is hidden as default.

What to look for:
- Complete product catalog with variants and SKUs
- Current inventory levels in an organized format

Plan

This is where you'll make most of your manual adjustments.



How to use it:
Review the forecasted numbers from the Inventory Forecasting sheet
Input your own projections based on:
- Upcoming marketing campaigns
- Anticipated seasonal fluctuations
- New product launches
- Discontinuations
Enter your planned inventory levels for each product variant across the upcoming months

Tips:
- Focus on products with high value or strategic importance
- Adjust numbers that differ significantly from historical patterns when you have specific reasons (e.g., a planned promotion)
- Leave cells blank to use the system's forecasted values

Final Plan

This sheet combines historical data, forecasts, and your manual adjustments.



What to look for:
- The finalized inventory plan for the next 12 months
- Areas where your manual adjustments have modified the forecast
- Comprehensive overview of expected inventory needs

How to use it:
- Use this as your master reference for inventory planning decisions
- Share this information with suppliers for production planning
- Reference during purchasing decisions

Incoming Inventory

Track expected deliveries and arrivals in this sheet.


How to use it:
Enter anticipated delivery dates and quantities for each product variant
Update as you place orders with suppliers
Adjust as delivery dates change or are confirmed

Why it's important:
- Helps calculate future inventory levels
- Prevents duplicate ordering
- Identifies when new orders need to be placed

Stock Burn

This projects how quickly you'll run out of stock based on your current inventory, forecast, and incoming deliveries.


What to look for:
- Yellow highlighted cells indicate potential stockouts
- Timeline of when products will run out of inventory
- Products requiring immediate attention

How to use it:
- Prioritize purchasing based on projected stockout dates
- Identify products that need expedited shipping
- Focus your inventory management efforts on critical items

Best Practices

Regular Updates
- Review and update your manual plan at least monthly
- Update incoming inventory information as orders are placed and confirmed
- Check stock burn projections weekly to identify urgent needs

Seasonal Planning
- Pay special attention to historical seasonal patterns
- Plan inventory increases before high-demand periods
- Consider inventory reductions during traditionally slower periods

Collaborative Process
- Share the Final Plan with operations and purchasing teams
- Get input from sales on upcoming promotions or expected changes
- Coordinate with finance on inventory investment planning

Data Validation
- Regularly compare actual sales to forecasted amounts
- Adjust your manual planning process based on forecast accuracy
- Look for systematic patterns in forecast errors

Practical Examples

Example 1: Planning for a Seasonal Product

Identify the product in the Product List
Review historical patterns in Monthly Sales
Check the system forecast in Inventory Forecasting
Adjust numbers in the Plan sheet to account for expected seasonal fluctuations
Review the updated projection in Stock Burn to ensure adequate coverage

Example 2: Managing a Product with Long Lead Times

Identify the product in the Stock On Hand sheet
Review current inventory and sales forecast
Input confirmed deliveries in the Incoming Inventory sheet
Check Stock Burn to see if additional orders are needed
Place orders early based on lead time requirements

Example 3: Discontinuing a Product

Locate the product in the Plan sheet
Gradually reduce planned inventory to zero over the appropriate timeframe
Monitor Stock Burn to avoid excess inventory at discontinuation
Adjust incoming orders in Incoming Inventory accordingly

Troubleshooting

Common Issues:

Problem: Forecasts seem inaccurate for certain products
- Solution: Add your own adjustments in the Plan sheet based on business knowledge

Problem: Stock Burn shows immediate stockouts
- Solution: Verify current inventory in Stock On Hand, then place rush orders and update Incoming Inventory

Problem: Need to plan beyond the visible time horizon
- Solution: Focus on the 12-month view provided, updating as time progresses

Conclusion

The Inventory Planning by Month spreadsheet is designed to combine automated data analysis with your business expertise. By regularly reviewing and updating the information, particularly in the Plan and Incoming Inventory sheets, you'll develop a robust inventory management system that minimizes stockouts while preventing excessive inventory investment.

Remember that the system's projections are based on historical data, but your knowledge of upcoming business changes is invaluable for accurate planning. Use both together for optimal results.

Updated on: 27/03/2025