If you manage inventory in your organisation and plan to do a Cashflow or Balance Sheet Forecast then you will need to consider and budget for your inventory. There are two generally accepted principals used in accounting for inventory and they are Perpetual and Periodic.


In this help note we will help you identify what type of inventory you are using and highlight the required cashflow settings, plus suggest some general budget steps.

 

Perpetual Inventory


With this method the inventory and cost of sales are updated continuously on each purchase and sale. Purchases are directly debited to the inventory accounts meaning your purchase invoices are raised against the inventory (Asset) accounts. At the time inventory is sold the inventory account is credited to reduce the inventory on hand value and cost of goods debited to record the cost of sales expense. If your accounting system or some other add-on manages inventory then you are most likely using the perpetual inventory method, with the above records being recorded automatically.

 

Cashflow Settings for Perpetual Inventory

For a full explanation on Cashflow and Balance Sheet forecasting including cashflow settings please refer to the following help articles:

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
Note: Nominating Inventory Accounts in the Default Accounts screen will by default use the Creditor Days cashflow type.
Cost of Sales (Cost of Sales)
Creditor Days or custom Profile
Must be the same Cashflow Type as your Inventory accounts. Therefore Creditor Days or a custom Profile are appropriate. If you use a custom profile, the same profile values must be used on matching Inventory and Cost of Sales accounts.  

With perpetual inventory the Cost of Sales adjustment to the inventory account does not have a cashflow effect. Ensuring both accounts have the same cashflow type will ensure the budgets related to the cost of sales adjustment are negated in the Net Cashflows, while any differences represent inventory purchases.

 

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 here 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 Budget Shortcuts to bring in last year actuals or last year’s budgets with some percentage adjustments.

    Sales1


  • Build sales budgets in Excel and import them into Calxa. Check out the example using Excel below.

 

Step 2 – Budget for Cost of Sales

Under a perpetual inventory system Cost of Sales has a direct relationship with Sales and hence the suggestion to budget for your Cost of Sales after budgeting for Sales. Once again you can create your budgets however you like but here are a few suggestions.

  • If you used the Budget Factory suggestion above you may also set the Cost of Sales as a percentage of Income (Sales) during the same process.
  • Use Budget Shortcuts to calculate Cost of Sales as a percentage of Sales.

    CostOfSales2


  • 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 below.

 

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.


If you wanted to be a bit more accurate you would need to consider what inventory movement is. There are two parts, the positive movement for Purchases Added and the negative movement Less Cost of Sales. For demonstration purposes in the example below we have separated the Inventory into two accounts by these names. The difference between these two budgets is the net movement on inventory. 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 should help with the general concepts.

 

Example using Budget Shortcuts

  1. Calculate the Cost of Sales portion of the inventory movement. This is just the inverse of the Cost of Sales and therefore we setup the shortcut as -100%.

    InventoryMovementCostofSalesShortcut3


  2. Enter your purchases budgets. In the example we have just keyed the values manually and used the next month’s projected Cost of Sales as our purchase added budget.

    PurchasesAdded4

 

Example using Excel Import

Have a look at the help note for details on how to import budgets from excel, but 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 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.

ExcelExample5

 

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 months purchases. The important takeaway from this example is that you can leverage importing from Excel if you have more complex calculation requirements than Budget Shortcuts can provide. Additional Information can be added after the empty rows or even into a separate worksheet that the primary sheet references.

 

Periodic Inventory


Conversely to Perpetual Inventory explained earlier, with this method the inventory and cost of sales accounts are not updated continuously. Instead purchases are recorded against a separate Purchases account which is generally categorised as cost of sales. Inventory Movement and a true Cost of Sales value are determined at the end of an accounting period via a closing entry. 


Cost of Sales is calculated as:
Cost of Sales = Beginning Inventory + PurchasesEnding Inventory


Accounting for inventory with the periodic system is therefore easily identifiable by the Beginning/ Opening and Ending/Closing accounts. Most commonly we see that the Purchases account is classified as Cost of Sales. Purchases are then recorded directly against Cost of Sale throughout the accounting period. At the end of the accounting period the closing entry accounts for the stock movement. There are many variations on how you might journal this exactly but in all cases the same basic principal applies to Calxa budgeting and cashflow.

 

Cashflow Settings for Periodic Inventory

For a full explanation on Cashflow and Balance Sheet forecasting including cashflow settings please refer to the following help articles.

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

Accounts
Cashflow Type
Inventory on Hand (Asset)
None
This is the balance sheet account that records your stock on hand.
Opening Inventory (Cost of Sales or Asset)
None
Closing Inventory (Cost of Sales or Asset)
None
Inventory Movement (Cost of Sales or Asset)
None
These accounts are used to balance inventory movement and adjust Cost of Sales. There are numerous combinations and ways to record this but most commonly we see Opening and Closing Inventory as Cost of Sales accounts. In this case the purchases account below will also be a Cost of Sales account. In some cases the Opening and Closing accounts are combined into a single account like Inventory Movement.
The journals on all of the above accounts are a book entry only, designed to record the inventory movement and adjust cost of sales. Therefore they have no cashflow effect and must be set with a cashflow type of None. You should not nominate Inventory Accounts in the Default Accounts screen.
Purchases (Cost of Sales or Asset)
Creditor Days or custom Profile
With periodic inventory this is the only account that has a cashflow effect. Creditor Days is therefore the default cashflow setting but you may also choose a custom Profile.

 


Budgeting for Periodic Inventory

We recommend following these 3 steps.

  1. Budget for Sales
  2. Budget for Purchases
  3. Budget for Inventory Movement

 

Step 1 - Budget for Sales

Please refer to the Budget for Sales portion of the perpetual inventory section for a few suggestions.

 

Step 2 – Budget for Purchases

You can create your purchases projections, however you like but here 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 Budget Shortcuts to bring in last year actuals or last year’s budgets with some percentage adjustments.

    PurchasesPeriodic6


  • Build purchases budgets in Excel and import them into Calxa. Check out the example using Excel above. 

 

Step 3 – Budget for Inventory Movement

Budgeting on accounts that have a cashflow type of None in Calxa require the values are balanced. Please refer to the help note - Balancing your Budget with the Discrepancy Analysis Report for more details.


The monthly budgets to the Inventory on Hand Asset account should be the expected increase or decrease in stock. You may wish to use last year’s movement as a guide in which case you could use Budget Shortcuts to bring in last year’s actuals.


InventoryMovementPeriodic7

 

Now that you have the Balance Sheet side we just need to enter a balancing entry into the Cost of Sales accounts. In terms of budgeting in Calxa there is no need to enter all the opening and closing values, just the overall movement is sufficient. We recommend a single balancing entry using Budget Shortcuts. Note that the Cost of Sales value is the opposite movement to Plant Stock so the shortcut is negative 100%.


CostOfSalesPeriodic8