Embedded Files
At times we have high selling products which can be time consuming to enter, process and prepare for orders. Examples are Movember raffle and Moo Brew/Wilderness Society Lobster Ale.
Process for managing these effectively are below:
NOTE: Only do this if there is just one product on the order - for orders where multiple products exist enter separately
For case products that can't be bought in multiple variation e.g. Advent Calendar
1. Change order Status from Manual Verification Required to something reflective of the product e.g Lobster Ale Order
2. Print out orders as normal and separate orders for the specific product
3. Enter all other orders as normal
4. In BigCommerce select all those orders for the specific product and change their status to Lobster Ale Order
5. Go to Orders > Search and select orders by Status and enter in order IDs (From & To) - click search
6. Select Export All - Export as Large Orders
7. Open Excel CSV File
8. Create new column to right of Column I "Store Credit Redeemed" call this Total
9. Create formula for Total = Subtotal (inc tax) - (Coupon Value - 1) - (Store Credit Redeemed)
10. Sum This Colum
11. Sum Column F (Total Quantity)
12. Create Formula which is Sum of Total / Sum of Total Quantity - this is the product price you need to enter e.g. Sum of Total = $5,968.27 / Sum of Total Quantity (48) = $124.339
13. Enter into RSA e.g. 47 * F5 PRDOUCT CODE @ $124.34
then F5 PRDOUCT CODE @ $difference to make total ($124.29) $5,968.27
14. Filter table except formulas at bottom of table
15 Sort on Shipping
16. Work out number of orders with $9.99 shipping and $14.99 shipping - enter these in RSA e.g. 17 x 3511 ($9.99) 29 x 3512 ($14.99) - note there maybe times where people have purchased multiple units which also incurred multiple shipping amounts.
17. Sum 'Column L' Order Total (inc tax) - this should equal the amount in RSA
18. Enter in RSA details as normal e.g BC 50504-50669
19. Stamp all orders with Entered stamp
20. Update any in store pickup orders for awaiting pickup if ready.
Save file as Excel Workbook instead of CSV to keep formulas
For products where multiple variations exist e.g. Single/6Pack/Case
1. Change order Status from Manual Verification Required to something reflective of the product e.g Lobster Ale Order
2. Print out orders as normal and separate orders for the specific product
3. Enter all other orders as normal
4. In BigCommerce select all those orders for the specific product and change their status to Lobster Ale Order
5. Go to Orders > Search and select orders by Status and enter in order IDs (From & To) - click search
6. Select Export All - Export as Large Orders
7. Open Excel CSV File
8. Create new column to right of Column I "Store Credit Redeemed" call this Total
9. Create formula for Total = Subtotal (inc tax) - (Coupon Value - 1) - (Store Credit Redeemed)
10. Sum This Colum
11. Sum Column F (Total Quantity)
12. Sum Column 'Combined Product Weight'
13. Divide the Sum of 'Combined Product Weight' by the weight of the product that is in BigCommerce e.g. 0.625 - this gives you the total units sold.
14. Divide total units sold by the number of units to a case e.g. 427/24=17.79
15. Underneath multiply result round number by number of units to a case e.g. 17 * 24 = 408 - the difference between the 427 and 408 (19) is the number of units outside of a case e.g. 427 = 17 cases and 19 bottles
16. Take sum of Total and divide this by the total units e.g. $1,865.34/427= $4.37 (unit cost)
17. Multiply unit cost by number of unit in case e.g. $4.37 x 24 = $104.84
18. Enter the number of whole cases into RSA at case price derived above e.g. 17 cases
19. Enter the number of singles at the unit cost less one unit into RSA e.g. $4.37 *18 bottles
20. Enter the last unit to at a price into RSA e.g. 4.82 to equal the Total amount.
14. Filter table except formulas at bottom of table
15 Sort on Shipping
16. Work out number of orders with $9.99 shipping and $14.99 shipping - enter these in RSA e.g. 17 x 3511 ($9.99) 29 x 3512 ($14.99) - - note there maybe times where people have purchased multiple units which also incurred multiple shipping amounts.
17. Sum 'Column L' Order Total (inc tax) - this should equal the amount in RSA
18. Enter in RSA details as normal e.g BC 50504-50669
19. Update any in store pickup orders for awaiting pickup if ready.
Save file as Excel Workbook instead of CSV to keep formulas
For Australia Post
1. Go to BigCommerce search and enter the order numbers (start to finish) for which you are dispatching, select order status e.g. Lobster Ale Order
2. Export as Pre Orders template
3. Open file in Aus Post Macro update delivery details
4. Open file separately - the last column displays the combined weight for the order
5. In the column next to this work out the weight you will use for AusPost e.g. a can weighs approx 330grams, but is listed on the website as 625 grams - for this example take the weight divide by 2 and round up to nearest kilogram. this is achieve by using the formula =ROUNDUP(V2/2,0) where V2 is the combined weight column
6. Copy all the weights and paste into AusPost Macro using Paste Special (values).
7. Export and upload to Australia Post.
Page updated
Report abuse