Calculating funds owed by Brewquets to Beer Cartel

Currently all labour is paid through Beer Cartel and all alcohol stock is purchased by Beer Cartel. An invoice needs to be sent to Brewquets quarterly to account for this. Steps to determine amounts are below:

1. Do export of orders from Brewquets in Shopify

- Choose Fulfilled > Paid/Partially Paid/Partially Refunded

- Choose Dates: Aim to go for a full quarter

- Export CSV file selecting all orders, this will be sent via email. Save this in G:\Shared drives\Geoff & Richard Private Folder\Beer Cartel\Banking\Xero\Brewquets Suppliers\Brewquets Kitting Costs owed to Beer Cartel\DATE

2. Open file and add filter

3. Sort on Lineitem name

4. Save as Workbook

5. Replace with nothing for ’ - Express / Specific Day Delivery (+$10)’, ' - Express / Preferred Day Delivery (+$10)', ‘ - Specific Day Delivery (+$10)’, ‘ - Standard Delivery (FREE)’.

6. Create new tab Sale by Products

7. Copy and paste data from Lineitem name

8. Remove Duplicates

9. Delete all the extra products which weren’t purchased through Beer Cartel

10. Add in columns: Sales Number of beers/cider/wines Units

11. In sales column add the formula =COUNTIF(First Tab,A2)

12. Go through each product and add in the number of beers/wine/cider then sort on this column high to low – checking the right amount of stock is allocated to each product

13. In the column Units do a sum for Sales x Number of beers/cider/wines

14. Create a Total at the bottom of each column which is the total of that column

15. In Units at the bottom add in a unit price of $2.70 and multiply this by total units to work out total stock owed to Beer Cartel.

16. Create new tab called Kitting Costs

17. In first tab go to Shipping Method and only select Courier and work out total for this.

18. Look at staff calculations from a previous sheet and add these to kitting costs.

19. Calculate costs for Kitting and add these to Product costs to work out amount to invoice Beer Cartel.

20. When invoicing Beer Cartel itemise products and kitting (labour hire) separately.