In the previous video, you created your inventory and expenses spreadsheet and recorded important data.
Now, it’s time to determine your current stock and profit margins.
You could manually count each item or calculate the differences one by one using the spreadsheet data.
This might be feasible when you’re starting out, but as your business grows, it would get time-consuming.
With Google Sheets, you can determine the items in your inventory with a formula.
A formula is a mathematical relationship expressed in numbers. Using a formula means you don’t have to manually subtract, add, or calculate a list of numbers.
This saves you time and limits errors.
To start, add a formula to Column D: Current Stock.
This will show the difference between your Original Stock and the Total Sold for each item.
Formulas in Google Sheets always start with an equals sign.
Enter the formula to find the difference between the number of products you started with and the number sold.
Subtract the “total sold” cell number from the “original stock” cell number.
The formula is B2 minus C2.
When you press enter, the “Current Stock” column shows the number of items you have in stock.
To copy the formula to the entire Current Stock column, click on the cell containing the formula.
Then, grab the handle in the lower right-hand corner, hold it, and drag it down until you reach the bottom of your list.
When you release the handle, the cell references update to indicate the correct totals, based on the formula.
Some results may be positive; some may be negative.
To make sure nothing was mistyped or miscalculated, double-check your work.
You used a formula to calculate the number of items in your inventory.
This will save time as you build your online business.
Use an equivalent formula to determine the margin for each item.
The margin is the difference between the item’s cost and the retail price.
A high profit margin indicates a big difference between your cost and the retail price -- which means you're charging customers fairly and making your products cost-effectively.
A low profit margin -- with a small difference between your cost and the retail price -- is a signal to re-evaluate your expenses or what you charge customers.
In the “Margin” column, type in an equals sign.
Then, subtract the Cost Per Unit from the Retail Price.
In this case, the formula is F2 minus E2.
Press enter, and then drag the formula through the entire column.
Now, it’s your turn: Add a formula to the Current Stock column.
Drag the formula down the list.
Add a formula to the Margin column.
And drag that formula to the bottom of that list.
1. Introduction to Manage Expenses in a Spreadsheet
2. Build Your Expenses Spreadsheet
3. Use Formulas to Identify Inventories and Margins
4. Calculate Expenses and Profit of Each Product
5. Calculate Overall Expenses and Profit
6. Use Visual Cues to Identify Low Stock or High Costs
7. Manage Expenses in a Spreadsheet Wrap-Up