8.2.3 – Organize Inventory and Stock Coolers

The dotFIT Inventory sheet shows the selling price, costs, and all pertinent information for inventory. You use this sheet monthly to do inventory and use as a tool throughout the month to help as a guide for proper order amounts.

Step by Step Inventory order form for dotFIT inventory

The dotFIT Inventory Spreadsheet is also used as a tool to track what we have sold, which links up to our on-hand levels to determine what we need to order and what we should have in our inventory at all times.

Every day run a sold report on what was sold the day before. On the month tab there is a column for each day of the month. Input the number of units of the items that was sold under the corresponding row. The Last column will calculate the total items sold for each unit and automatically link up to the SOLD column in the inventory tab.

For example, if today is the 2nd I will run a report for what was sold on the 1st, then on the 3rd run a report for the second only and place those units the 2nd

Note: you never want to run a sold report with the day you are in because you are still selling therefore it will not be accurate.

If you are keeping up daily with your MONTH tab, what was sold, your ON HAND LEVEL tab will be a useful tool. The first Column B is linked up with your Inventory tab, which is what you have in your inventory.

The on hand level is what you should have in the club at all times. Should order is what you should order. The on-hand levels are determined by your clubs volume and how quickly you go through product. For example, if your on-hand level for Whey Smooth is 20, means you should have 20 in the clubs at all times to satisfy the member’s needs. If you inventory left is 10, then you should order 10.

All other instructions on how to use the inventory tab are the same as the bars and drinks!

Formulas

Formulas often get erased or changed so below is a list of what each formula in each column should be and what it means to assure your inventory information is correct and that you understand it.

  • Column A: Dotfit Product: the name of the product
  • Column B: Selling Price: This is the price we sell the product to the member BEFORE tax – when in doubt if this is correct, scan an item into the POS computer to be sure it is correct
  • Column C: Beginning Inventory: as stated in the step by step guide- the beginning inventory is the END of month count from the previous month
  • Column D: Added Inventory: These are the products that are ordered and delivered to your club – anything that you are physically adding to your inventory throughout the month
  • Column E: Total Inventory: This is the sum of the beginning inventory (C) and the added inventory (D) =SUM(C3+D3)
  • Column F: Sold: The items that were sold throughout month, the quantity of each item sold with be inputted next to the correct product
  • Column G: Total Revenue: take total sold X selling price   =SUM(F3*B3)
  • Column H: Unit Price: this is what the company actually pays for the product – price per unit in case
  • Column I: Total Cost: the total of items sold X the unit price =SUM(F3*H3)
  • Column J: Total Profit: Total Revenues less Total Cost         =SUM(G3-I3)
  • Column K: Inventory Left: this is based on the total amount of inventory we have less what was sold through Datatrak   =SUM(E3-F3)
  • Column L: Counts: These are your actual end of month counts
  • Column M: Added Inventory: this is your added inventory (what was ordered) X the unit price=SUM(D3*H3)
  • Column N: Inventory Amount: Inventory left X unit price         =SUM(K3*H3)
  • Column O: Actual Amount: the value of the amount of inventory we actually have   =SUM(H3*L3)
  • Column P: Markup: selling price – unit price   =SUM(B3-H3)
  • Column R: Difference: count – inventory left   =SUM(L3-K3)

 When Sold Column Doesn’t Link Up

  • Go to the total column, normally AG
  • Put the cursor on the total column and the row of the first product
  • Type =SUM
  • Drag your cursor from the first of the month to the last of the month
  • Press enter
  • Hoover your cursor over the bottom right corner until you see the +
  • Click and drag to the bottom to repeat the formula for all products
  • Go to inventory page
    • Go to the first product in the sold column
    • Type in =
    • Tab over to the month column
    • Click on the first product and press ENTER
    • This will link up the pages together
    • In the sold column hover over the bottom right corner of the cell until you see the +
    • Click and drag to the bottom to repeat the formula for all products

The Counts form is used for EOM Shake Bar Counts. The inventory manager who is assigned by the GM who places the orders for the Shake Bar will be responsible to do a count at the end of the month (1st of the month). They physically count what they have in storage and on the counter and send it back.