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|
- 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)
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…