Tips & Tricks: Get ready for Budgeting Time!

Tips & Tricks: Get ready for Budgeting Time!

By Salomi Kruger, MD for Applico Training

Planning your budget can be a daunting task in itself without adding the pressure of understanding spreadsheets and formulae in Excel.

Here are some practical tricks to assist you with data management when controlling the budget layout and calculation.

Tips:

1. Keep raw data like transactions and historical figures separate to sheets with calculations

Design your budget document to allow for future change easily. If you keep your raw data you are basing your calculations on separate, you can update the raw data in future and  pick up calculations from this through your document to adapt to changes in the year.

2. Use consistent cell references to variables that might need updating in the year

Absolute referencing is an invaluable function to support this design of a budgeting document but how does it work?

In the example below, we have created a simple formula to use the variable placed in cell B2 to calculate together with our Last Year Sales in another data sheet the total forecasted for sales.

Our reference to cell B2 has a dollar sign in front of the 2 (which refers to line 2) so that we can easily copy down the formula and keep looking at row 2 for our % increase. The $ sign is our “sticky tape” to keep the formula from relatively updating as it is copied down and moving to row 3, 4 and so forth.

You will however notice that the formula has no $ sign in front of the B. This is to allow us to copy it to the other months and the B to become a C, D, E, F etc and update with different percentages for the different months.