If you manage inventory or stock in your organisation and plan to do a 3-Way, Cashflow or Balance Sheet Forecast then you'll need to budget for this inventory.
In this help note we'll look at how to budget for inventory using the Perpetual Inventory accounting method, as well as identify the required Cashflow Settings.
There are two generally accepted principals used in accounting for inventory - Perpetual and Periodic.
For help with identifying what accounting method you use please refer to the Inventory Financial Settings help article. If you use the Periodic accounting method please refer to the help article Budget for Inventory (Periodic method).
The accounts involved in Perpetual Inventory and the cashflow settings required are as follows.
For a full explanation on Cashflow forecasting, including cashflow settings, please refer to the Step by Step Guide to Cashflow Forecasting help article.
|Sales (Income)||Debtor Days or a custom Profile|
|Inventory (Asset)||Creditor Days or a custom Profile|
|Note: Nominating Inventory Asset Accounts in the Inventory screen will set the cashflow type to Creditor Days.|
|Cost of Sales (Cost of Sales)||Creditor Days or a custom Profile|
|The Inventory and Cost of Sales accounts should be set with the same Cashfow Type. Creditor Days or a custom Profile are appropriate, however if you use a custom profile, the same profile values must be used on matching Inventory and Cost of Sales accounts.|
Budgeting for Perpetual Inventory
We recommend following these 3 steps:
- Budget for Sales
- Budget for Cost of Sales
- Budget for Inventory Movement (Purchases – Cost of Sales)
Let’s look at the full process in detail.
Step 1 - Budget for Sales
You can create your sales projections however you like, but below are a few suggestions.
- Use the Budget Factory to create your full P&L budget based on last year’s actuals with some percentage adjustments.
- Use the Budgets & Actuals screen to enter some sales projections.
- Build sales budgets in Excel and import them into Calxa. Check out the example using Excel at the bottom of this article.
Step 2 – Budget for Cost of Sales
Under a perpetual inventory system Cost of Sales has a direct relationship with Sales. In many cases your Cost of Sales can simply be budgeted as a percentage of your sales. If you used the Budget Factory method above then you should consider using the percentage of Income (Sales) calculation method provided in the Budget Factory.
If more complex calculations are required for your Cost of Sales budgets you can use Excel and import them into Calxa. Check out the example using Excel at bottom.
Step 3 – Budget for Inventory Movement
A simple approach to inventory movement is to budget based on last year’s actuals or based on your own expectations. A positive budget represents an increase to inventory while a negative budget represents a decrease. If your inventory does not change much from month to month then zero budgets will be sufficient. Once again you could consider the Budget Factory to bring in last year's movements.
To be a bit more accurate, let's consider what makes up your inventory movement. There are two parts, the positive movement for Purchases Added and the negative movement to reduce the balance for Cost of Sales.
For demonstration purposes in the example below, we have separated the Inventory into two accounts which represent these two parts. The difference between these two budgets is the net movement on inventory, shown as Total Plant Stock.
Separating the accounts like this can make the budgeting process a bit easier, so you may wish to consider adopting this approach in your accounting system. If not you will just need to make these calculations manually.
If you have more complex requirements or many inventory accounts you may wish to consider doing some of the calculations in Excel, but this example below should help with the general concepts.
Example using Excel Import
Have a look at the help article on Export and Import budgets for tips. Essentially the first step with creating an import template is to export your budgets from Calxa. Once you have exported the budgets you can delete all the rows you don’t want to update with the import.
By leaving an empty row in the template you can add any additional information required in your calculations below that, as Calxa will ignore all rows after the empty row when importing. In the example below you can see there are only 3 accounts that will be updated by this import template: Sales, Cost of Sales and Inventory.
In this simple example we are calculating sales based on items sold and sale price. Similarly, purchases are based on items purchased and the purchase price. Items purchased are in line with the following month’s forecasted sales and therefore Cost of Sales is relative to the previous month's purchases.
The important takeaway from this example is that you can leverage importing from Excel if you have more complex calculation requirements. Additional information can be added after the empty rows, or even into a separate worksheet that the primary sheet references.