The Azquo Solution

Joe Browns provided us with six inter-related spreadsheets which represented the core budgeting process.

These sheets were also linked to a number of supplementary spreadsheets. Stage One was to analyse the movement of data. There were three categories of sheets:

1. Base level – with imported data and base level calculation,
2. Intermediate level – ‘holding’ spreadsheets, and
3. Output sheets – the sheets providing the required reports.

Stage One

We started by recreating the main output sheet – the P&L, having first decided what ‘filters’ we would like to have at the top. In this case, these were:

1. Division
2. Financial Year and
3. Version (e.g. ‘Budget’, ‘Actual’, ‘Revised Budget’ etc.).

The rest of the sheet mirrored exactly Joe Browns’ existing worksheet with identical formatting save for the fact that the various ‘sets’ (such as ‘variable costs’) were set up to be expandable should the need arise.

Stage Two

Was to trace back all the figures in the P&L to their point of input which covered

1) Mailings
2) Online Orders
3) Marketing Channels and
4) Wholesale.

Each had a base template, which was copied multiple times (e.g. for each mailing, for PayPerClick, for Ebay UK, or for Australian wholesale etc.) Almost all the calculations in the workbooks referred back to four templates, so we re-created these in Azquo, and specialed sets of channels for each (20 mailings per year, six online channels, about 10 marketing channels and 10 wholesale channels).

We extracted all the import figures to two Import Workbooks – the ‘Historic Setup’ and the ‘Budget Setup’ for the year.

1)  The ‘Historic Setup’ contained definitions of all the concepts in the system – the marketing channels, the chart of accounts, the date structure, as well as historic figures for previous years which might be used as budget input figures.

2) The ‘Budget Setup’ contained a number of dedicated sheets – e.g. for the Mailing input parameters (size of mailing, expected response rate, rate of returns, AOV, inland and overseas orders etc), which together represented the suppositions on which the budget was based. These workbooks contained formulae only to test the validity of the data (e.g. you cannot specify a quantity mailed without specifying an expected response rate).

The Joe Browns Output

Part of the budget setup workbook was an Overheads Sheet with data at a much higher level that would feed directly into the P&L.

Joe Browns’ complicated interrelated Excel workbooks had thus been reduced to two Azquo workbooks filled with suppositions about structure and budgeted quantities and values, which had been uploaded to our system. The templates for each channel (Mailings, Online, Markets, and Wholesale) were then run multiple times each. One additional sheet was required to handle some credit and insurance calculations that were not specific to single channels.

The output from these sheets was fed directly into the P&L.

Testimonials 

“Anyone working in Finance recognises the problems that arise at the end of the year when budgets need revising – the links between cells, the links between spreadsheets and the [resulting] lack of con?dence that you have in Excel as it stands alone today…

What happened for me was when Azquo came along all those troubles were taken away. I could lie awake at night thinking about other problems – rather than the budgetary process!”