more-arw search

Q&A Forum

Is there a good, cost effective solution for sales forecasting and inventory planning?

Robert Ro's Profile

Currently using a spreadsheet to forecast sales by customer and sku by month for a distribution operation (I.e. no manufacturing).  Also used to tie into inventory planning.  Tracking units as well as sales and cost dollar amounts.  ERP system is Netsuite.

Pros: very flexible and cheap. Can use formulas.  Cons: not very robust, cumbersome to add new skus, customers, error prone, reporting limitations.

Requirements: as an early stage business, can't rely on historical sales.  Need to be able to easily enter varying amounts by month for a seasonal business (sell thru) for each sku for each (key) customer.  Need to be able to enter notes to support each layer of forecast (e.g., for a specific customer, there may be two forecasted amounts for a given sku in the same month - a regular sell thru figure and a special promotion figure).

Interested to know how others are handling this area.  I found a product called Justenough, but it was way too expensive.  Don't need all the bells and whistles.  Just a more stable, robust solution than my current spreadsheet.

Answers

Topic Expert
Regis Quirin
Title: Director of Finance
Company: Gibney Anthony & Flaherty LLP
LinkedIn Profile
(Director of Finance, Gibney Anthony & Flaherty LLP) |

Of course this answer relates to the size of your company. But to keep costs down, it may make sense to develop a database of your inventory information in Access. For any projections and modeling, simply download the results and manipulate in Excel. There are limits to storing data in spreadsheets. This approach is the most cost effective for a start-up business.

Dmitry Faybysh
Title: CEO
Company: Bankcard Analytics
(CEO, Bankcard Analytics) |

Spreadsheetmarts, Access, and other bandage solutions are only going to extend your pain and frustration. Bite the bullet and buy a performance management and analytical solution. Currently working with SAP - Business Planning and Consolidations. Oracle owns Hyperion pricier and more involved. There are other vendors.

In the 90's we all invested in ERP and now is the time to invest in the Analytical Solutions. Today you would not think about doing AP, AR, GL or Inventory in a spreadsheet or even Access.

A real planning solution will trully get you of the data gathering mode and move you toward better analytics and driving better decisions. Focuses you on Top line or Bottom line...

Hope this helps, let me know if you want to talk more...

Dmitry

david waltz
Title: Assistant Treasurer
Company: Integrys Energy Group
(Assistant Treasurer, Integrys Energy Group) |

Robert - The advantage to spreadsheets is that you can customize as much as you want. The disadvantage is that it can be quite manual. Some of the "manualness" can be overcome if you have folks with Visual Basic for Applications programming capabilities - then adding a sku becomes as simple as pushing a button.

Sometimes systems such as those who have commented already can be helpful, though in my experience have never run across one that does exactly what is needed without "workarounds" being performed outside of them.

Topic Expert
John Orlando
Title: CFO
Company: Centage
(CFO, Centage) |

Hi Robert, There are a number of factors to take into account when considering the effectiveness of a sales and inventory planning solution. The factors you need to take into account for best in class sales / inventory forecasting and planning are:

•Is your revenue planning based on dollar amounts or do you need drivers such as units times a rate?
•Are you in the type of industry where you need to have a way to defer revenue and recognize it at a later date?

When dealing with inventory you would want a way to synchronize your revenue, cost of sales and inventory. A solution that offers you the ability establish minimum & maximum inventory levels and auto replenish your inventory based on your revenue projections is what you should be looking for.

Spreadsheets may be able to address one of these issues but the most cost effective solutions address all of these issues in an automated, scalable fashion.

There are applications on the market that are extremely accurate and cost effective relative to the actionable information and time savings you’ll receive in return.

Brian Karr
Title: CFO
Company: In-between
(CFO, In-between) |

I've worked in environments with high volume, high frequency replenishment and low volume seasonal shipments, and in both cases, we resorted to building our own forecast tools by querying sequel tables with visual basic or other sequel query tools, after maxing out with excel. Here's the approach I would take:

(1) Go back a season or two and model expected and actual demand for your product at different points in the purchasing cycle
(2) Determine what variables are the best predictors of demand (i.e. % of orders booked, # of unique orders booked, like style attributes). For example, if you had 25% of your orders booked for a sku at the time you made a purchase decision in the prior season, and purchased using the actual orders at that time divided by 25%, ask yourself how good was that method as a predictor of actual demand. Was the actual demand more highly correlated instead with the number of unique customer orders at the time of the purchase decision? This is the hardest part of the process, but also the part where you develop the critical knowledge necessary to run your business.
(3) Create the output you would like to generate from real-time queries of the data (I would do this in excel). You should focus here on outputs that help you make a purchasing decision, not on the presentation of sequel data.
(4) Hire a programmer to write the code that provides you with real time views of actual and expected demand in the format mentioned in (3) above using the formulas you've developed in (2) above.

I recommend building the tools on your own, because it forces you to think thru the drivers of demand rather than relying on standard algorithms. It also creates a repository of data from which to learn for future purchases. Remember, this is a forecast and will never be perfect. All you can do is focus on building the tools and tweaking formulas so that you get better over time.

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.