If you manage inventory or stock in your organisation and plan to do a 3-Way 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).



Cashflow Settings for Perpetual Inventory

The accounts involved in Perpetual Inventory and the cashflow settings required are as follows.

Accounts

Cashflow Type

Sales (Income)

Debtor Days or a custom Profile

Inventory (Asset)

Creditor Days or a custom Profile

Cost of Sales (Cost of Sales)

Creditor Days or a custom Profile

While you can choose Creditor Days or custom Profile for the Inventory and Cost of Sales accounts they must be set with the same Cashfow Type.

Nominating Inventory Asset Accounts in the Inventory screen will set the cashflow type to Creditor Days to match the standard Cost of Sales cashflow type. So if you plan on using a custom profile for Cost of Sales accounts then you should not use the standard Inventory nomination and instead set all accounts in the Cashflow Settings (Advanced) menu with the same custom Profile values.


Note: For a full explanation on Cashflow forecasting, including cashflow settings, please refer to the Step by Step Guide to Cashflow Forecasting help article.


Budgeting for Perpetual Inventory

We recommend following these 3 steps:

  1. Budget for Sales

  2. Budget for Cost of Sales

  3. 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.

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.

Option 1 - Budget Factory

If you used the Budget Factory method above to create your Sales budget then you should consider using the percentage of Income (Sales) calculation method provided in the Budget Factory.

Option 2 - Percentage of Sales Budget Formula

Using a Budget Formula you can link individual or multiple Sales accounts to your Cost of Sales accounts. Configure your formula as follows.

  1. LIVE: true

  2. Data source: leave unchanged as the default which is the current budget and business unit

  3. Period: 0 to ensure the calculations are on the same period

  4. Formula calculation: [Sales] * [Percent Of Sales]
    Example below: [Bar Sales] times [0.30]

Percentage of Sales Budget Formula

Step 3 – Budget for Inventory Movement

Option 1 - Simple Budget

A simple approach to inventory movement is to budget 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 no budgets are required.

Option 2 - Last Year Actuals Budget Formula

If your stock movement is seasonal it may make sense to use prior years actual movements. Using a Budget Formula you can link your budgeted stock movement to prior years actuals. Configure your formula as follows.

  1. ONE-OFF: true

  2. Data source: switch to Actuals but keep the same Business Unit

  3. Period: -12 to look back 12 months (last year)

  4. Formula calculation: [Selected Accounts]
    Selected Accounts can be used when applying the formula to multiple Inventory accounts at the same time.

Last Year Actuals Budget Formula

Option 3 - Purchases minus Cost of Sales Budget Formula

To be a bit more accurate, let's consider what makes up your inventory movement. There are two parts, the positive movement for Stock Purchases and the negative movement to reduce the balance when you sell the good which is the Cost of Sales.

To represent this as a Budget Formula you'll need to create a Metric for Stock Purchases and the configure the formula as follows.

  1. LIVE: true

  2. Data source: leave unchanged as the default which is the current budget and business unit

  3. Period: 0 to ensure the calculations are on the same period

  4. Formula calculation: [Stock Purchases] - [Cost of Sales]
    Example below: [Stock Purchases] minus [Bar Beverage Cost]

Purchases minus Cost of Sales Budget Formula
Did this answer your question?