Hello Peeps,
Hope you guys are well, I'm very very new when it comes to Excel, so for some this will be the most basic thing. So by profession I am a doctor, but before Med school I used to sell little stuff on eBay, and Amazon this is something I still enjoy doing despite the lack of real profits.
To really make the help required easily comprehendible I have made a template file of all the stuff in simple format. Would love to hear from you guys how to improve this.
The three formulas I am struggling with are explained below.
Q1 - I have three suppliers for identical products, now within these suppliers the price varies as its subject to the USD rate. So I need in column E, G & I the most recent price based on invoices. I don't know how to write this formula but understand that this will be based on a condition of date and supplier.
| | | | | | | | | | | Date: | | 16/03/2023 | | Date: | | 14/03/2023 | | Date: | | 08/03/2023 | | Date: | | 01/03/2023 | | Date: | | 12/02/2023 | | Date: | | 10/02/2023 | | Date: | | 01/02/2023 |
| | | | | | | | | | | Invoice #: | | 5001 | | Invoice #: | | A212 | | Invoice #: | | A208 | | Invoice #: | | XYZ721 | | Invoice #: | | 4985 | | Invoice #: | | 4722 | | Invoice #: | | XYZ718 |
| | | | Supplier | A | Supplier | B Price | Supplier | C Price | | Supplier | | C | | Supplier | | B | | Supplier | | B | | Supplier | | A | | Supplier | | C | | Supplier | | C | | Supplier | | A |
| Item Title | SKU | Price | Supplier A SKU | A Price | Supplier B SKU | B Price | Supplier C SKU | C Price | | SKU | QTY | Price | | SKU | QTY | Price | | SKU | QTY | Price | | SKU | QTY | Price | | SKU | QTY | Price | | SKU | QTY | Price | | SKU | QTY | Price |
| Banana | FB | 0 | A101 | | FB54 | | X0A056 | | | X0A056 | 4 | £2.00 | | FB54 | 2 | £2.00 | | FB54 | 6 | £2.00 | | FB54 | 6 | £1.00 | | X0A056 | 4 | £2.00 | | X0A056 | 0 | £1.95 | | FB54 | 6 | £1.20 |
| Apple | FA | 0 | A102 | | FB55 | | X0A057 | | | X0A057 | 4 | £4.00 | | FB55 | 3 | £4.00 | | FB55 | 6 | £3.50 | | FB55 | 2 | £1.00 | | X0A057 | 4 | £4.00 | | X0A057 | 0 | £3.85 | | FB55 | 6 | £1.00 |
| Orange | FO | 0 | A103 | | FB56 | | X0A058 | | | X0A058 | 4 | £3.50 | | FB56 | 0 | £3.50 | | FB56 | 6 | £3.00 | | FB56 | 6 | £1.00 | | X0A058 | 1 | £3.50 | | X0A058 | 2 | £3.40 | | FB56 | 6 | £0.99 |
Q2 - In sheet named 'Reorder Sheet' I need to use data from sheet 'Restock Data' to generate reordering quantity. So this is a COUNT formula but because first three items are single units and the rest are bundles of the said three different units (see sheet name 'listings'), I am unsure of how to make it count from the bundles.
| Item Title | SKU | Units Required | Supplier A SKU | A QTY | Supplier B SKU | B QTY | Supplier C SKU | C QTY |
| Banana | FB | | A101 | 0 | FB54 | 0 | X0A056 | 0 |
| Apple | FA | | A102 | 0 | FB55 | 0 | X0A057 | 0 |
| Orange | FO | | A103 | 0 | FB56 | 0 | X0A058 | 0 |
| B0001 | 33 |
| B0002 | 22 |
| B0003 | 15 |
| B0005 | 100 |
| B0007 | 23 |
| Item Title | ASIN | SKU | Number of Units | Price | Unit 1 SKU | Unit 1 Price | Unit 2 SKU | Unit 2 Price | Unit 3 SKU | Unit 3 Price |
| Banana | B0001 | FB | 1 | £5.00 | FB | £0.00 | | | | |
| Apple | B0002 | FA | 1 | £5.00 | FA | £0.00 | | | | |
| Orange | B0003 | FO | 1 | £5.00 | FO | £0.00 | | | | |
| Bundle 1 | B0004 | SET-BA | 2 | £9.00 | FB | £0.00 | FA | £0.00 | | |
| Bundle 2 | B0005 | SET-BO | 2 | £8.50 | FB | £0.00 | FO | £0.00 | | |
| Bundle 3 | B0006 | SET-AO | 2 | £9.00 | FA | £0.00 | FO | £0.00 | | |
| Bundle 4 | B0007 | SET-BAO | 3 | £12.00 | FB | £0.00 | FA | £0.00 | FO | £0.00 |
| | | | | | | | | | | |
Q3 - This I don't know anything to be honest. I hope I can explain properly. So basically I need the values in cost of goods column in Sales - May 23 to be based on the invoices, so once a quantity is used up from the oldest invoice (in catalogue sheet) I need the formula to jump to the next invoice and use the respective price. I gave up on this and just thought using an average will suffice but that makes it more confusing.
| ORDER # | ASIN | QTY | Price Per Unit | Total Price | SHIPPING | COST OF GOOD | PACKAGING | Total Costs | PROFIT | TAX |
| 788782 | B0006 | 1 | £10.01 | £10.01 | £2.50 | | £0.20 | £2.50 | £7.51 | £1.86 |
| 788783 | B0003 | 1 | £5.22 | £5.22 | £2.00 | | £0.20 | £2.00 | £3.22 | £0.97 |
| 788784 | B0004 | 1 | £9.85 | £9.85 | £2.50 | | £0.20 | £2.50 | £7.35 | £1.83 |
| 788785 | B0005 | 1 | £11.00 | £11.00 | £2.50 | | £0.20 | £2.50 | £8.50 | £2.05 |
| 788786 | B0005 | 1 | £11.02 | £11.02 | £2.50 | | £0.20 | £2.50 | £8.52 | £2.05 |
| 788787 | B0006 | 1 | £9.89 | £9.89 | £2.50 | | £0.20 | £2.50 | £7.39 | £1.84 |
| 788788 | B0007 | 1 | £11.95 | £11.95 | £3.00 | | £0.25 | £3.00 | £8.95 | £2.22 |
| 788789 | B0001 | 1 | £4.85 | £4.85 | £2.00 | | £0.20 | £2.00 | £2.85 | £0.90 |
| 788790 | B0007 | 1 | £12.44 | £12.44 | £3.00 | | £0.25 | £3.00 | £9.44 | £2.31 |
| 788791 | B0004 | 1 | £10.20 | £10.20 | £2.50 | | £0.20 | £2.50 | £7.70 | £1.90 |
| 788792 | B0007 | 1 | £13.15 | £13.15 | £3.00 | | £0.25 | £3.00 | £10.15 | £2.45 |
| 788793 | B0007 | 1 | £13.11 | £13.11 | £3.00 | | £0.20 | £3.00 | £10.11 | £2.44 |
| 788794 | B0001 | 3 | £6.07 | £18.21 | £2.00 | | £0.20 | £2.00 | £16.21 | £3.39 |
| 788795 | B0004 | 2 | £10.29 | £20.58 | £2.50 | | £0.20 | £2.50 | £18.08 | £3.83 |
| 788796 | B0007 | 1 | £11.74 | £11.74 | £3.00 | | £0.25 | £3.00 | £8.74 | £2.18 |
| 788797 | B0006 | 1 | £9.85 | £9.85 | £2.50 | | £0.20 | £2.50 | £7.35 | £1.83 |
| 788798 | B0007 | 1 | £11.70 | £11.70 | £3.00 | | £0.25 | £3.00 | £8.70 | £2.18 |
| 788799 | B0004 | 3 | £9.52 | £28.56 | £2.50 | | £0.20 | £2.50 | £26.06 | £5.31 |
| 788800 | B0001 | 1 | £4.99 | £4.99 | £2.00 | | £0.20 | £2.00 | £2.99 | £0.93 |
| 788801 | B0006 | 1 | £11.02 | £11.02 | £2.50 | | £0.20 | £2.50 | £8.52 | £2.05 |
| 788802 | B0007 | 1 | £13.99 | £13.99 | £3.00 | | £0.25 | £3.00 | £10.99 | £2.60 |
| 788803 | B0001 | 9 | £5.00 | £45.00 | £2.00 | | £0.20 | £2.00 | £43.00 | £8.37 |
| 788804 | B0001 | 2 | £5.09 | £10.18 | £2.00 | | £0.20 | £2.00 | £8.18 | £1.89 |
| 788805 | B0001 | 2 | £5.05 | £10.10 | £2.00 | | £0.20 | £2.00 | £8.10 | £1.88 |
| 788806 | B0007 | 1 | £12.28 | £12.28 | £3.00 | | £0.25 | £3.00 | £9.28 | £2.28 |
Any help will be highly appreciated, the file attached is obviously not the real data, this is because I wish to understand how the formulas work in a template so I could recreate it - for me this more of a learning thing. I tried to use Fiverr and the people are on there were prepared to do it for very reasonable prices, but don't want to teach me how the formula is written which I found weird to say the very least.
