Kick off the new school year with lessons from our Back to School 2021 collection to help students express themselves, build relationships, and stay organized.

3. Use Formulas to Identify Inventories and Margins

Playback Speed:
Transcript

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 1 by 1 using the spreadsheet data.

This might be feasible when you’re first 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.

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.

A high profit margin indicates a big difference between your cost and the retail price -- which means you are 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 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.

Instructions

  1. Add a formula to the Current Stock column.
  2. Drag the formula throughout the entire column.
  3. Add a formula to the Margin column.
  4. Drag the formula throughout the entire column.