It’s that time again – the financial year is drawing to a close and departmental budgets need to be revised for the next year. Expenses need to be forecasted, salaries adjusted by a few percent, revenue predicted and everything mashed together into something sensible that the CFO will hopefully approve. If only we had a forecasting tool. Wait a minute – We do! SkuBrain! Yes, that’s right. You may be mistaken thinking that SkuBrain is only for inventory management. It can forecast just about any time series! In this blog I’ll share with you exactly how to do that.
First, you’ll need to get a CSV extract from your financial or online accounting package. Basically we want all income and expenses, by date. The fields you will need are:
|Account Code||The GL code|
|Account||The meaning of the code|
|Amount||The amount of spend or income|
|Date||The date of the transaction|
|Department||The department responsible|
If you have other field that you’d like to group the result by, you can add those too – e.g. Department, Cost Center, Region, and so on. Next, we’ll need to do some Excel-based data prep to get it into a form that SkuBrain can work with. The first two steps are easy:
- Create a new field called “SKU” that is composed of the GL and GL Code.
- Rename the Date to “OrderDate”
- Rename the Amount to “Quantity”
Before we do the next data prep steps, some explanation is in order: SkuBrain cannot forecast negative quantities, but the output of the ledger systems are often positive for expenses and negative for income (or vice versa, depending on which side of the ledger you are looking at). So we’ll need a workaround for SkuBrain. Fortunately, there is a simple one – the UnitPrice field. Remember that in SkuBrain, your Sales are calculated using the formula : Sales = Quantity x UnitPrice. Therefore:
- Create the UnitPrice column which has 1.0 for income items, and -1.0 for expense items. (In my data, expenses were negative. If your data has it the other way around, just flip the logic)
- Then, reverse the sign of expense items.
BEFORE (expense item has negative quantity)
AFTER (expense item has positive quantity, but negative Unit Price)
Your input data is almost ready. The last thing to do is add a couple of required columns: LineReference and OrderReference. They don’t need to contain any values - SkuBrain will just fill them in automatically if they are blank. When you’re done, your data will look like this (some extra fields are shown for me, but they are not mandatory).
Now, export your XLSX file into a CSV file and upload that to SkuBrain as usual. Mine had 55,000 rows and when imported into SkuBrain, looked like this:
Now, its time to do some Expense and Income forecasting! Since I have a “whole-of-company” extract, and I’m interested in departmental performance, I start with a forecast that has a two-level hierarchy “Department > SKU”. Here are my forecast settings:
I start the job, and a sip of coffee later, we have a forecast! Here’s what expenses of my Distribution department look like (The “$” value is negative because expenses have a UnitPrice of -1.0 in my data.)
In contrast, my Sales Department forecast is positive (since revenue is recorded there with UnitPrice = +1.0)
And the SUM of everything (“All sales”) is essentially my EBIT, forecasted for the next year (we’re not looking too bad)!
I also know that we’re planning a major digital campaign in the coming year, so we’ll just make sure our Marketing expense forecast reflects that.
Indeed, I could go further and create a forecast that breaks down my finances by cost center as well. We’ll need to let Head Office know that their IT team needs to be reined in:
So, there you have it – a financial forecast, with adjustments, to back up my budget for next year, done on a per-GL code basis that’ll have my CFO wanting a SkuBrain account for herself. To feed the forecasted numbers back into Excel, all I have to do is download the data (More → Download) as a CSV file. I’m inspired now. Where can I get some decent historical data for index funds, I wonder…