Taking stock of inventory calculations in Excel

If there is one area of financial modelling that confuses people, it’s inventory. To lift some of this fog, this article shows how to calculate movements in inventory and associated costs of goods sold (COGS) in a simple financial model so that financial statements will reconcile.

To make this clear, I will illustrate the concept by using a walkthrough model. Therefore, please download this Excel file. Using this example, I will run through a step-by-step process that details how to construct an integrated model forecasting the flow of inventory.

In this example, the model assumes that money is received on credit sales (days receivable) 60 days after the sale is made and that bills/creditors are paid 30 days after the invoices have been received (ie, days payable is 30) for all periods.

I am going to need some further assumptions, as laid out in the screenshot below.

The first task is to imagine that you have the assumptions above, and now you have to model the sales and correctly insert those amounts into your financial statements accordingly. This has to be calculated first, as this has knock-on effects on COGS and hence inventory.

Therefore, I will create a Revenue segment where we pull the relevant data to calculate the sales:

Notice that when I first create a Calculations section, I simply refer to assumptions from elsewhere, rather than perform calculations on these figures at the same time. This makes it easier for end users to follow model logic.

Next, I calculate the price per unit from the initial unit price, and then adjust for inflation each period. The projected sales amounts are pulled directly from the Assumptions projected sales row 26 in the example file:

In the screenshot below, the Days Receivable section pulls the assumptions from row 51, then calculates the Closing Receivables. The Closing Receivables is a simple formula that multiplies the amount received by the fraction 60/365 (if not a leap year) to calculate the amount that is still due at the end of the year. Essentially, this assumes that all of the payments are distributed evenly throughout the year.

Knowing the sales for the period and the receivable for all periods, I can construct the control account, where the opening/closing receivables and revenue are linked directly from the calculations above. We have three of the four lines for the account: the Cash Receipts is then simply a balancing figure:

Now that revenue has been computed, we can move on to considering the inventory account itself. This comprises two key movements: additions (the purchases of stock made) and deductions (both wastage and the stock used for sales — the latter is why I had to calculate sales first).

Again, I have cited the necessary data from the Assumptions section, which includes the Purchases and Price data:

The calculations for Payables are relatively straightforward, with the Purchases being the product of the Purchases made and the Price. The Closing Payables formula is similar to the Closing Receivables formula (above):

=J88*J91/J92

As before, this assumes that the payments for the products are distributed evenly throughout the year. It should be noted that this only works when the number of days receivable is less than or equal to the number of days in the period. If this assumption does not hold, then I suggest you revisit my April 2020 article on working capital.

For the current example, I must now create the control account here, Opening Payables linked to the blank cell, to ensure that the previous period is considered. The Purchases cells reference row 88, the Closing Payables reference row 93, and hence the Cash Payments are the balancing figures.

But that’s not the end of the calculations. The calculations in this section get a little complicated now. Bear with me though as it will all make sense in the end …

Two COGS calculations must be made here: The first concerns weight, and the second concerns pricing. We need to separate the two since we need the weight of the COGS to help work out the inventory amounts in order to ascertain pricing.

The Amount Used Per Sale (row 118), Projected Sales (row 119), and the COGS (row 120) are relatively straightforward, with COGS just being the product of the two earlier rows.

Moving on to the Inventory Balance Pre-Wastage (row 122), this row is calculated from the sum of the Opening Inventory, Purchases, and the COGS (rows 139–141). This is so the later calculations will take the previous period into account, as the Opening Inventory line draws figures from the previous periods.

The Inventory Balance Pre-COGS Transfer (row 126) is calculated from the Opening Inventory and Purchases from the Inventory at Hand (kg) section (rows 139 and 140):

The next line down may appear to be the same, but notice the units, delineating that the input should be a dollar figure. Thus, this figure is calculated from the sum of the Opening Inventory and the Purchases rows (rows 148 and 149) in the Inventory ($) section:

An IF statement that calculates the dollar value of the COGS:

=IF(J126, J127*J128/J126,)

ie, this IF statement states that, if there is a non-zero weight for Inventory Balance Pre-COGS Transfer, calculate the dollar amount of this balance multiplied by the proportion of the COGS weight divided by the Inventory Balance Pre-COGS Transfer (essentially the proportion of units left). So, if COGS is 200, that means 200 units have been sold, so there are 400 – 200 = 200 units left. Therefore, I multiply the total value of the stock on hand, 1,596, by 200/400, which is 798 (row 132):

Wastage is calculated simply by referencing the relevant inputs. Inventory Balance Pre-Wastage (kg) is just the Inventory Balance Pre-COGS Transfer (kg) less COGS (kg) (rows 126 and 128), and the Inventory Balance Pre-COGS Transfer ($) is just the Inventory Balance Pre-COGS Transfer ($) less COGS ($) (rows 127 and 129).

The wastage formula is very much like the COGS calculation above:

=IF(J131, J132*J133/J131,)

It’s now just a matter of calculating the control accounts for inventory, both in terms of weight and costs.

The opening inventory line has to be linked to the previous period’s closing. This ensures that the closing balance of the previous period is taken into account. The purchases are linked from row 108. The Wastage and COGS amounts are pulled from the previous calculations, rows 134 and 120, respectively. You should remember to negate these two cell references:

The same steps are repeated with the Inventory ($) section; just note the difference in references to match the unit ($):

So will it balance? Do you think I’d have written this article if it didn’t?

Notice the indiscreet “BS”, “IS”, and “CFS” scattered about the model in Column O of the Excel workbook (eg, at the end of rows 148 and 150–152 in the screenshot above). They are not there merely for aesthetic purposes: They are the control account line items to be entered into the financial statements, which are shown in the screenshot below.

There are no tricks here, just a simple linking of all of those lines to the respective financial statement segment. Do note the two “Excluded” line items (rows 99 and 149 in the example): They will always be equal and opposite so may safely be ignored.

Furthermore, if you inspect the Excel File, you will see that row 171 has this formula to calculate cash:

=MAX(SUM($J197:J197),)

Anchoring column J allows the formula to provide a running total. Also note a similar formula is used in the Bank Overdraft row:

=-MIN(SUM($J197:J197),)

This just ensures positive cash balances are displayed as Cash whilst negative running totals are displayed as an Overdraft.

Word to the wise

This example calculates COGS on a weighted average basis. Other techniques include last in, first out (LIFO) and first in, first out (FIFO) — the latter I will cover next time. These approaches require different computations. However, once the costs have been computed, the rest of the method is similar to the approach described above.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.