A Life of Varied Endeavors

Tag Archives: forecasting

Spreadsheets: What-ifs or Wishful Thinking?

I’ve been working with a client in an advisory function for several years.  I love these people!  Lately, we’ve been focusing on trying to re-visualize certain functions and processes of their business.

In part of that process, we have developed a new profit and loss forecasting model in a spreadsheet.  It identifies certain assumptions about the volume of their business and the related revenues.  Their business is seasonal.  With changes to just 1 revenue cell and 12 monthly volume cells, they can forecast their revenue for each month and the whole year. 

The expenses are forecast by categorizing them as either fixed or variable.  For purposes of forecasting, they’ve decided that all the “cost of goods sold” expenses are variable, and certain costs of selling are variable.  To keep it simple, they are treating everything else as fixed, and evenly spread over all twelve months of the year.

In this scenario, the COGS and expenses have only six variable items, and they are all a function of revenue.  So with changes to 6 cells, they can change the total expenses forecast for the year, and display it by month. 

The result of all this is a display of the projected profits, by month and for the whole year. 

It’s actually a pretty good spreadsheet for what they are trying to accomplish!

So together with the 13 variable cells for revenue, there are a total of 19 input cells that can be jiggered around.

Which brings me to my point.  A forecasting spreadsheet should be used for planning, budgeting, and evaluating results.  But when faced with changing circumstances, changing assumptions, or changes in desired outcomes, the spreadsheet can become little more than a board game.

If the spreadsheet is used in a disciplined fashion, it really can be useful.  If the goal is to reduce cost of goods sold to 30% of revenue from 32%, and be able to project the impact on the bottom line, then you project the results and measure your actual results against your goal.

But if, instead, you start playing with ideas and goals that are more wishful than realistic, then perhaps you are just fantasizing.  And with 19 cells to play with, you can spend hours messing with each one of them, always ending up with magnificent profits as a reward.  Oh, sweet spreadsheet!

That’s when the spreadsheet, as helpful as it could be, becomes sort of an enemy.  Because you are sitting there playing a game, and you aren’t on your feet doing the business of your business.

You can’t become more efficient and reduce COGS by changing the input in a cell. I know this is obvious, but sometimes the elixir of the spreadsheet can become an avoidance technique.  Don’t let that happen.

Get up, and get to it!