Case Study
Spreadsheet to Track Supply Availability for Seasonal Planning
A plant nursery owner, receiving weekly inventory updates from a supplier, needed a way to track and analyze the availability and pricing of hundreds of plant varieties over the course of a year. With a busy schedule, she was looking for a more efficient way to prepare inventory data for the following season.
Project Goals
Consolidate Weekly Data: Integrate weekly spreadsheets into a single, master document for a full-year view.
Enable Yearly Analysis: Develop a pivot table to track availability trends and identify which plants were highlighted or discounted across the year.
Simplify Seasonal Preparation: Make it easy for the client to review past data and anticipate the following year’s inventory needs.
Solution Process
Data Consolidation:
Gathered and organized 52 weekly spreadsheets, each listing hundreds of plants, availability numbers, and prices.
Integrated these sheets into one master spreadsheet, consolidating each week’s data.
Data Analysis Setup:
Created a dedicated worksheet to combine the weekly data into a comprehensive table.
Developed a pivot table that allowed the client to see each plant name with its availability displayed by week in chronological order.
Highlighting Key Insights:
Highlighted plants that were featured on sale or appeared in the nursery’s newsletter, helping the client easily identify special promotions and best deals.