The process begins by exporting and importing the following data from LightSpeed into the tool:

• Product List: Your complete product catalogue, including inactive products, provides the tool with a comprehensive list of all items, their categories, and current associated costs.
• Monthly Sales Report: For each month under review, the Sales Report is exported. This report contains detailed information for each SKU sold, including the SKU name, number of items sold, associated revenue, LightSpeed's reported COGS, and Gross Profit (GP).
• Sales History: This export offers a line-item breakdown of all sales within the chosen month, which is crucial for detailed COGS analysis on a per-sale basis.

These reports are then imported into the tool. The tool can be run for any number of months, from a single month's data to a full 12 months, allowing for both focused and historical analysis.

Once the reports are imported, the tool employs a robust logic to determine the Suggested COGS figure. This logic is designed to correct common COGS reporting issues (like incorrect average costs, timing delays, and voided COGS) and to provide a more accurate cost figure for each product and sale line item. Here's the logic it follows:

Detailed Steps of the COGS Calculation Logic:

• Review Product Category:
  • If a product's category is identified as "labour," the tool automatically suggests a COGS of $0. This ensures labour charges, which don't have a traditional COGS, are correctly accounted for via your payroll. 
  • If the category is not "labour,"  the tool moves to checking the sale status. 
• Review Sale Status:
  • If a sales' status is "voided," the tool automatically suggests a COGS of $0. This ensures COGS for voided sales are not accounted for incorrectly in your reports.
  • If the sales' status is not "voided" the tool moves to checking the Gross Profit (GP). 
• Review GP:
  • If the GP is positive, and if the LightSpeed-reported average cost for that item is not $0, the tool will use this LightSpeed figure as the Suggested COGS. This indicates LightSpeed's reported average cost is likely accurate for positive GP sales.
  • If the GP is negative, and/or if the LightSpeed-reported average cost is $0, the tool then looks to the current product cost as recorded in your Product List tab.
• Reviews Avg. Cost Used:
  • If the GP is positive, and if this average cost is not $0, the tool will use it as the Suggested COGS.
  • If the GP is negative, and/or if the average cost is $0, the tool then looks to the current product costs.
• Product List Cost/s:
  • The first field the tool looks to in your Product List is the "Single Unit Cost." This is an entry field in LightSpeed, meaning it's a value you can manually input or update. If a value is present in this field, the tool will use it as the Suggested COGS..
  • If the "Single Unit Cost" field is blank, the tool will then refer to the current supplier price for that product (as recorded in LightSpeed and imported into the tool).
• Review Status:
  • If, after all these checks, the tool cannot determine a COGS value (i.e., all the above fields are blank or unsuitable), it will flag the entry as needing review. This indicates that manual investigation and input are required to determine the correct COGS for that item, ensuring no product is left without a proper cost.

You will use this tab to:

1. Add an average cost price for products that have been flagged as "Cost Input Required" or "Avg. Cost = $0."
2. Correct an average cost price for a product for every sale that product appears in for imported months (especially for negative GP sales). Examples of this would be:
   • A supplier product of a box or packet has been used on a sale instead of the individual bottle or bag (e.g., "Box 12 x 1L 3in1 Protector" has a COGS of $150 and was used to sell a 1L individual bottle, whereas the COGS should be $12.50). 
   • User entry on a purchase order or supplier price has skewed the average cost, and is therefore causing sales to have a negative GP.

Products Flagged as Needing Review

First, ensure the filter option is applied to the header row. You will know it is applied if you can see a dropdown arrow next to each column heading. Need to apply a filter?  Click into the any cell in the header row (e.g., Needs Review cell).  Press Ctrl, Shift + L on your keyboard.

Once the filter is applied: Click the Needs Review dropdown arrow in the "Needs Review" column.  Untick (Blanks). Click Ok. Start reviewing the products  For products that should have a cost associated, input an entry into the Average Cost column in the Product List Tab. Once the entry is in there, delete the "Needs Review" comment in the Needs Review column for that row.  For products that do not require a cost price (e.g., service charges or consumables that are not tracked as inventory), put $0 in the Average Cost column and delete the "Needs Review" comment. This will allow you to remove that product from the filter. You can (if you wish) add your own comment (e.g., "Service Charge - No COGS") so you can refer back to it.  Once all of these products have been addressed, or you prefer to address some products in the individual months, move to the Individual SKU Sales Tabs to address other identified issues.

You will use these tabs to identify products that are flagged within the particular months because:

• They are reporting an average cost = $0.
• The revenue for the month (divided by the quantity sold) is larger than the COGS (divided by quantity sold) for that month.
• A cost cannot be determined (Cost Input Required) - if you addressed all of the Cost Inputs in the Product List tab, you may not see this comment here.

Average Cost = $0

If a product is flagged as having an average cost of $0, you need to review if this is accurate or not. Common scenarios include:

• The average cost in the system is $0, and the category is not labour. 
• There is no supplier cost on the product, so the logic cannot determine a cost higher than $0
• Its a non-inventory tracked product which was created with a $0 cost, which is used from then on out. This could include products like custom pool covers, ordered per client. 

If the cost of $0 is accurate, simply delete the 'Avg. Cost = $0' comment in the "Manual Review" column for that row. You can (if you wish) add your own comment (e.g., "Correct: Consumables") so you can refer back to it.

If the cost of $0 is not accurate, options are:

• Need to add a price for the product for that month? Add a value into the Suggested COGS column in the SKU Sales Tab. This price will be used for any sales with that product within that month only. 
• Need to add a price per line item or customer? Add a value into the Suggested COGS Column in the Line Items Tab. This could be used for orders with varying costs (e.g., custom pool covers).
• Need to updated a cost for the product for every sale (across all months): Navigate to the product tab and add a value in the Average Cost column. This price will be used for any negative sales with that product within all months covered by the tool. 

Avg. Cost > Revenue

If the product is flagged as the Average Cost (COGS / quantity sold) being higher than the Revenue per UOM (Revenue / quantity sold), this could be due to several reasons:

• The incorrect product was sold (e.g., a supplier bulk pack product rather than an individual bottle).
• Warranty work (e.g., no revenue but COGS recognised).
• Promotional products or campaigns where you get a product for free.
• Product was mistakenly sold at $0 or a lower price than the Cost price. We have seen cases that indicate the product was sold 2 times, however, for one of those it was sold at $0 retail price, and therefore the tool is picking up as the cost is higher than the revenue overall.
• Non inventory tracked product created with $0 cost.

To check: 

Copy the SKU of the identified product from the SKU Sales tab. Navigate to the Line Items tab for that particular month Ensure the filter option is applied to the header row (using Ctrl + Shift + L). Click the SKU dropdown arrow. Paste the SKU in the search field. Click Ok. You need to determine if the cost per line item is correct. If not, here are options to rectify: Incorrect product was sold: Navigate back to the Product List tab and add the individual cost price into the Avg. Cost column on the row of the individual product. This will correct the COGS across all instances of that product. Warranty Work: Depending on how you invoice and claim credits for products used on a warranty job, you may need to put $0 in the Suggested COGS column for the specific warranty invoices on the Line Items tab. Promotional Product:  If the cost is correct (as it was a free item for the client), then delete the 'Avg. Cost > Revenue' comment in the SKU Sales tab for that product and put your own note there if you want (e.g., "Promo Item - COGS correct"). Product sold at $0: Unfortunately, this indicates missed revenue. Since the cost is correct, go ahead and delete the 'Avg. Cost > Revenue' comment in the SKU Sales tab and put your own note there if you want (e.g., "Sold at $0 - Missed Revenue").

Once all of these products have been addressed, or you prefer to address some products in the individual months, you can move to the Individual Line Items tab for more granular adjustments.

Cost Input Required

If a product is flagged as the 'Cost Input Required', this means that a cost cannot be determined for the product as there is not sufficient information on the product record.

It is generally best to address these entries in the Product List tab, as updates there will apply to all months where the product appears.

If you would prefer to do this per individual SKU Sales Tab:

• Need to add a price for the product for that month? Add a value into the Suggested COGS column in the SKU Sales Tab. This price will be used for any sales with that product within that month only. 
• Need to add a price per line item or customer? Add a value into the Suggested COGS Column in the Line Items Tab. This could be used for orders with varying costs (e.g., custom pool covers).

• Warranty Sales: Where a product is used as part of a warranty claim and has no associated revenue, you might adjust the Suggested COGS to $0 if the cost is covered by a supplier credit or warranty process. 
• Early Buys / Special Pricing: If a product was purchased at a unique price point for a specific sale, you can adjust the COGS for that particular line item. 
• Non-Tracked Inventory Item Cost Updates: For items like custom blanket covers, where costs might vary significantly per order and are not tracked via standard inventory, you can manually input the precise COGS here. 

How to Adjust a Line Item: 

1. Navigate to the relevant Line Items tab for the month containing the sale you need to adjust. 
2. Use the filters (Receipt, SKU, or Customer Name) to locate the specific line item you wish to amend.
3. In the Suggested COGS column for that line item, directly input the corrected COGS value. 
4. Once you input a value, the "Suggested COGS" column will override the tool's calculation for that specific line item.

Q: Why is a product flagged as "Cost Input Required"?

A: This means the tool could not find any cost information for the product in LightSpeed (e.g., no average cost recognised, no supplier price). You need to manually input the correct average cost in the Product List tab, or if it's a unique item, directly in the SKU Sales or Line Items tabs.

Q: My average cost is showing as $0, but it's not a labour item. What should I do?

A: This usually indicates a missing cost in LightSpeed for that product. Refer to the "Average Cost = $0" section under "SKU Sales Tabs" for steps to add the correct cost, either broadly in the Product List or specifically for that month/line item.

Q: Why is "Avg. Cost > Revenue" flagged for a product?

A: This suggests the product was sold at a price lower than its cost, or there's a discrepancy in the quantity sold versus the cost applied. Common reasons include sales of bulk packs as individual items, warranty work, promotional items, or accidental $0 sales. Follow the steps in the "Avg. Cost > Revenue" section under "SKU Sales Tabs" to investigate the specific line items and make appropriate adjustments.

Q: I made a change in the Product List tab, but it's not reflecting in a specific SKU Sales tab or Line Items tab.

A: If you've already made a manual override in the Suggested COGS column on an SKU Sales or Line Items tab, the tool will stick to your manual input for that specific entry. Double-check where you made the initial change and if there are any conflicting manual entries.

Q: Can I share this spreadsheet with my accountant?

A: Yes, this tool is designed to provide comprehensive data that can be valuable for your accountant for reviewing your monthly COGS and gross profit. Ensure they understand the "Suggested COGS" figures and the logic applied.

Drop-in Sessions

In addition to the guide, the Evolution Team will be hosting weekly drop-in sessions to provide live support.  During these sessions, we will walk through the sheet and offer an opportunity to ask any clarifying questions you may have. These sessions are designed for both new participants and those seeking a refresher. You are welcome to attend whichever weekly session best suits your schedule. 

You can register for a drop-in session via this Calendly link: Register for COGS drop-in session.

Seeking Expert Advice

If you have any questions or wish to discuss the adjustments needed and their impact on your financial reporting, we strongly recommend consulting with your accountant.

For specific guidance on the impact of these COGS corrections, especially concerning your EOFY financials and how they relate to the Poolwerx setup, you are welcome to book a complimentary consultation with Dan from Accolution. To schedule your free-of-charge consultation, please click here: Book a Consult with Accolution