Originally published: 12/11/2020 09:20
Publication number: ELQ-89436-1
View all versions & Certificate
Publication number: ELQ-89436-1
View all versions & Certificate
Cash Budget Model
Cash Budget Model generates a simple cash forecast based on a series of assumptions, and compares it with the actuals.
Description
Cash Budget Excel Model generates a simple cash forecast based on a series of assumptions, and compares it with the actual cash figures. The model also generates a series of comparison charts.
So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.
On the same tab, there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.
A color coding is also applied to the various tabs of the model. Yellow tabs are mostly assumptions tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents and checks).
Moving on to the Forecast Inputs tab (in yellow whatever can be amended as an assumption): the user needs to fill the various assumptions as follows:
- The currency, starting month and initial cash balance
- Sales assumptions such as volumes and prices, as well as the receipt of those sales
- The product costs and overheads, as well as their term of payments
- Wages which are split between full time and seasonal / part time
- Capex spending amount and their terms of payments
- The user will also insert the financing inflows (debt and equity) as well as the interest and capital repayments.
- Finally other operating costs need to be filled such as taxation, rent, marketing, insurance, etc...
Everything is calculated in the Outputs tab based on the assumptions you have set previously. Afterwards the user needs to populate the actuals tab as soon as the actual data is available.
The Actual versus Budget tab enables the user to compare the actuals versus the cash budget, for the selected period in the yellow cells. This will allow the user to see the absolute and percentage variance for this period.
For a more graphical representation of the variances, the Actual versus Budget Graphs tab can be consulted where a series of charts allow the user to see forecast plan accuracy.
Finally the checks tab where the most critical checks are aggregated. Whenever you see an error message in any page, you should consult this page to see where the error is coming from.
Cash Budget Excel Model generates a simple cash forecast based on a series of assumptions, and compares it with the actual cash figures. The model also generates a series of comparison charts.
So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.
On the same tab, there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.
A color coding is also applied to the various tabs of the model. Yellow tabs are mostly assumptions tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents and checks).
Moving on to the Forecast Inputs tab (in yellow whatever can be amended as an assumption): the user needs to fill the various assumptions as follows:
- The currency, starting month and initial cash balance
- Sales assumptions such as volumes and prices, as well as the receipt of those sales
- The product costs and overheads, as well as their term of payments
- Wages which are split between full time and seasonal / part time
- Capex spending amount and their terms of payments
- The user will also insert the financing inflows (debt and equity) as well as the interest and capital repayments.
- Finally other operating costs need to be filled such as taxation, rent, marketing, insurance, etc...
Everything is calculated in the Outputs tab based on the assumptions you have set previously. Afterwards the user needs to populate the actuals tab as soon as the actual data is available.
The Actual versus Budget tab enables the user to compare the actuals versus the cash budget, for the selected period in the yellow cells. This will allow the user to see the absolute and percentage variance for this period.
For a more graphical representation of the variances, the Actual versus Budget Graphs tab can be consulted where a series of charts allow the user to see forecast plan accuracy.
Finally the checks tab where the most critical checks are aggregated. Whenever you see an error message in any page, you should consult this page to see where the error is coming from.
This Best Practice includes
1 Excel and 1 PDF