In AirPOS, you can easily calculate the total value of your inventory by using spreadsheet functions. This article will guide you through the steps to find this essential information. Before we dive in, please take note of the following information in regards to stock valuations generated using this method
IMPORTANT PLEASE READ
Utilizing this approach will produce an approximate value of your stock and should not be regarded as completely precise. It's important to note that this information doesn't account for historical price fluctuations or cost price variations and is not adjusted for depreciation. We highly recommend that you talk to your accountant in order to get accurate figures for your business finances.
Step 1:Download Stock Export
To begin, you should download a stock export of your inventory. Follow these steps:
- Navigate to your Backoffice inventory page.
- Click the "export stock" button at the top-right corner of the page.
Step 2: Open the Exported File
Once you've exported your stock levels, open the file in either Excel or Google Sheets. In this example, we'll use Google Sheets, but the same formulas will work in Excel.
Your file should look similar to the image below:
Step 3: Prepare the Spreadsheet
Before calculating the total stock value, we need to make some modifications to the spreadsheet for clarity. We'll be removing any reserved sections that don't contain information fpr this example but please note that you don't have to remove reserved sections; this is for demonstration purposes only.
Step 4: Add Location Columns
Now, let's add columns to hold the stock total for each of your locations. Follow these simple steps:
- Click on the first cell of a new column.
- Type in a title for the column. In our example, we'll use "Stock Value Donegal Square" and "Stock Value 3-5 Commercial Court."
Step 5: Calculate Stock Value
With the columns in place, you can start calculating the stock value for your inventory. Here's how to do it:
- Select the first cell in the column where you want the new information. In our example, we'll start with "L1" to find the total stock value of Donegal Square's inventory.
In this cell, enter the following formula to multiply the total number of stock by the price of the item, giving you the stock value:
Here's a breakdown of the formula:
=: Indicates the start of a formula.
C2: Represents the cell containing the retail price of the item (e.g., "Apple watch").
*: Signifies multiplication.
I2: Represents the cell containing the stock amount for the Donegal Street location.
Press "Enter" to execute the formula. You may see an autofill button, which can be used to apply this formula to the rest of the column quickly.
If the autofill button doesn't appear, you can still autofill the column by clicking and dragging down the small blue dot at the bottom-right corner of the cell where you entered the formula.
Congratulations! You now have the stock valuation for one of your locations. Repeat this process for any other location by adjusting the cells selected in the formula. For example, to find the stock valuation of 3-5 Commercial Court, use "J2" instead of "I2" as the first cell in the stock amount column for that location.
Step 6: Calculate Total Stock Value
Now that you've filled both stock valuation cells, you can add them together to find the total value for all your locations. To do this, create another column and use a simple formula that combines the two columns you previously created:
Here's the breakdown of the formula:
=: The formula's starting point.
SUM: The function used to add a range of cells together.
(): Brackets to enclose the data you want to add.
L2: Represents the first cell in the "Stock Value Donegal Square" column.
:: Automatically added when selecting cells. If it does not appear, click the first Cell, hold the CTRL button on your keyboard and then click the final Cell.
M2: Represents the first cell in the "Stock Value 3-5 Commercial Court" column.
The same auto fill techniques that you used previously can be used here again to complete the rest of the column.
By following these steps, you have now created a spreadsheet that shows you the stock valuation of each of your locations and then the total of all of those locations added together.
Please remember that this is not an accurate way to value your stock as this is something that should be done by an accountant with the correct software and not through a POS system.