Simple Financial Calculations using Simple Javascript (part 1) - Inventory Valuation Methods

• Download InventoryValuation.zip - 10.5 KB

Introduction

The aim of this article and project as a whole is two-fold: to provide an easy and interactive graphical interface providing insight into selected financial calculations and to understand the calculations by reading simply written code. Both of these objectives are difficult in their own right and come pre-packaged with their own amount of intricacies enough to fill two libraries. Depite this I will try to tackle these problems head-on and offer you, the consumer, as much insight as insightfully possible.

Background

For this article, all you need is a basic knowledge of Javascript, Inventory Valuation Methods and intermediate street-smarts (optional).

Base Requirements

The primary objective of this project is to graphically represent the characteristics of the three main Inventory Valuation Methods: First-In, First-Out (FIFO), Last-In, First-Out (LIFO) and Weighted Average Cost (WAC).

Therefore the derived requirements are:

• Correctly calculate the value of inventory given a Valuation Method
• Provide a platform for comparison between the Valuation Methods
• Enable user manipulation of inventory - different inputs to provide different results
• Present the valuations in the simplest and most effective way possible

With our requirements now somewhat concretely set we can begin.

Calculations of Inventory

I will split up the calculations of inventory value into three parts: Firstly we will setup a Warehouse, we will then transact with this warehouse and finally, display the results of these transactions

Setting Up a Warehouse

Before we can start calculating the value of our inventory we need a place to store it. This is where the Warehouse object comes in. I have setup the Warehouse as below. Essentially, the Warehouse object holds three TransactionTables (one for each valuation method), a TransactionTable will hold a list of TransactionItems and other values specific to the valuation method.

function Warehouse(initialItems) {

//... some other properties

    this.LIFOTransactionTable = new TransactionTable();
    this.FIFOTransactionTable = new TransactionTable();
    this.WACTransactionTable = new TransactionTable();

//... some methods to be discussed later

}

function TransactionTable(){

    this.ItemsList = [];
    this.Total = 0;
    this.BeginningTotal = 0;
    this.EndingTotal = 0;
    this.COGSTotal = 0;
    this.TotalSell = 0;
    this.GrossProfit = 0;

//ratios
    this.GrossMargin = 0;
    this.InventoryTurnover = 0;
    this.DaysOnHand = 0;
}

function TransactionItem(txType, quantity, price){

    this.Timestamp = ticker;
    this.TxType = txType;

    if(txType == TransactionType.IMPORT)
    {
        this.Price = price;
    }

    this.Quantity = quantity;

    ticker++;
}

Introducing Warehouse Transactions

Now that we have setup our Warehouse, we can start importing and exporting items. Ultimately we only want to add one TransactionItem to the Warehouse at a single point in time. This item will then be replicated across the three TransactionTables. Let us start with the importation of Transaction Items

The only two questions we need to answer when importing a TransactionItem is how much of that item do we want (quantity), at what price and are we actually adding the item (TransactionType). We can easily group this under one heading as seen in the below screenshot.

Any Price/Quantity entered here will create a TransactionItem and add that item to the respective TransactionTables. Items will be added in a sequential manner, starting at t0 and incrementing everytime a new item is added. The Timestamp is important when we get to viewing the LIFO, FIFO and WAC tables side-by-side. The Timestamp of the Weighted Average Cost table will always be t_avg.

By nature, the importation of items into inventory are easy to handle as they will always be added at the same time irrespective of the method of inventory valuation. The differences start to appear when we sell items. For the purposes of this project, this is classed as exportation. When we export items, in the case of FIFO, we will remove items that were added first (starting at t0). In LIFO we will remove the items added last. This results in differing Cost of Goods Sold (COGS) in the three transaction tables. An example for how the calculation for COGS in FIFO is shown in the below code snippet. We need to check how much quantity is left in the current TransactionItem that is being depleted (CurrentTx). The CurrentTx of the FIFO calculation will always be the first item in the list (currentIndex = 0), whereas in the case of LIFO the first item to be sold is the last item in the list (currentIndex = Warehouse.LIFOTransactionTable.ItemsList.length - 1;).

    function CalculateCOGSFIFO_Perpetual(sellQuantity){
    var neededItems = sellQuantity;
    var itemsCalculated = 0;
    var currentIndex = 0;
    var COGS_total = 0;
    var alterList = [];


    while(neededItems != itemsCalculated){

        var currentTx = new TransactionItem(TransactionType.IMPORT, Warehouse.FIFOTransactionTable.ItemsList[currentIndex].Quantity, Warehouse.FIFOTransactionTable.ItemsList[currentIndex].Price);

        currentTx.Timestamp = Warehouse.FIFOTransactionTable.ItemsList[currentIndex].Timestamp;

        if(currentTx.Quantity == 0){

        }
        else if(currentTx.Quantity > (neededItems - itemsCalculated)){
            var used = (neededItems - itemsCalculated);
            COGS_total = COGS_total + used * currentTx.Price;
            currentTx.Quantity = currentTx.Quantity - used;
            itemsCalculated = itemsCalculated + used;
        }
        else if(currentTx.Quantity == (neededItems - itemsCalculated)){
            COGS_total = COGS_total + currentTx.Quantity * currentTx.Price;
            itemsCalculated += currentTx.Quantity;
            currentTx.Quantity = 0;
        }
        else if(currentTx.Quantity < (neededItems - itemsCalculated)){
            COGS_total = COGS_total + currentTx.Quantity * currentTx.Price;
            itemsCalculated += currentTx.Quantity;
            currentTx.Quantity = 0;
        }

        alterList.push(currentTx);
        currentIndex++;
    }

    Warehouse.FIFOTransactionTable.COGSTotal += COGS_total;
    AlterTransactionTable(CostMethod.FIFO, alterList);
}

In order to get COGS for WAC, the calculation is simple owing to the fact that the WAC table will always only hold only one TransactionItem. This is seen in the below code snippet.

function CalculateCOGSWeightedAverage_Perpetual(sellQuantity){
    var WACItem = Warehouse.WACTransactionTable.ItemsList[0];
    Warehouse.WACTransactionTable.COGSTotal += (WACItem.Price * sellQuantity);
    WACItem.Quantity -= sellQuantity;
}

Putting it all together in a visually friendly manner

We now have a Warehouse and import and export items for a profit/loss. We will now work on displaying this interaction by building these tables whenever an item is added/removed. Hopefully, this will give a good depiction of the current state of the Warehouse in it's three forms.

A generally good way of obtaining a comparative view is by displaying results side-by-side, thus the decision to have the three transaction tables next to each other was born. In this manner, a decent cross-sectional view of inventory is gained. The same timestamps are visually on one line as seen in the below image.

We now want to see what happens when we sell items. Because of the nature of FIFO/LIFO, this needs to be effectively represented in order to understand the differences between the two methods. This is done by changing the color of completely depleted items (quantity = 0). Metaphorically speaking, the result is clear that an event is happening at different ends of the Warehouse. This is seen in the below.

This view is built up in code in the following manner: First, any current inventory is removed from the view. Then for each of the TransactionTables in the Warehouse items are added using the code snippet below. If a TransactionItem has been depleted, it is then given a different color (I chose the bootstrap "danger" class for this). After this, the total values are displayed.

Warehouse.FIFOTransactionTable.ItemsList.forEach(function(addItem){

    var time = "t" + tick++;

    if(addItem.TxType == TransactionType.IMPORT)
    {
        var desc1 = addItem.Quantity + " items @ R " + addItem.Price;

        if(addItem.Quantity == 0)
           var addRow = new String(`<tr id="FIFOItemRow" class="danger">`);
        else
            var addRow = new String(`<tr id="FIFOItemRow">`);

        addRow = addRow + tdText + time + endtd;
        addRow = addRow + tdText + desc1 + endtd;

        $("#FIFOTable").append(addRow);

        addRow = "";
    }
});

A Word on Simulation

"Back in my day we had to do add inventory items manually," said someone who used this application a couple of months ago. There is a nifty "Simulation" section so user can set whether he wants to add items in an inflationary/deflationary environment. After one press of a button, five items of random quantities are added to the inventory at a constant rate of change in price. The rate of change is also set randomly between 1 and 10%. This section is seen in the below.

Conclusion

Inventory Valuation Methods are sometimes a tricky concept to wholly understand and sometimes a visual representation will make gaining an intuition much easier. This project aimed to provide that representation using easily developed techniques so an understanding can be gauged by using the web interface or by reading through the code. I hope you have enjoyed working your way through this article and will appreciate any and all feedback.

History

20 August 2017: Initial Version