# Thread: I have no idea what i need to solve this query, WhatIf? SumIf? not sure

1. ## I have no idea what i need to solve this query, WhatIf? SumIf? not sure

OK so I will try to make this simple. I am new and wouldn't have a clue where to look for this. I am a complete novice with little talent in Excel. Let me get to it.

I am trying to create a spreadsheet that allows me to input my data from sales from Ebay and outside of Ebay. Inside Ebay would include listing fees, 10% final value fees, Paypal fees 2% and any extras. Outside of Ebay non of these fees would exist (apart from the purchase price (buy to sell which exists for the Ebay sales part too).

Now i have created a table with simple formulas. So when I change the quantity of stock the sales stock (showing what i have left) changes along with the profit cell and ebay fees and paypal fees cells.

My aim is to have a formula or something (i really have no clue) to where I can enter any price of the sales cell and have the option of adding the ebay and paypal fees. for example....

Further more some times the price paid and import tax can differ by getting better deals which would effect the profit margin if the sales prices stay the same. Obviously if i kept getting the same deal all I would need to do when ordering more stock is simply change the quantity and everything else would stay the same.

Quantity Price Paid Import Tax Price Paid Total Sold Stock Left Price Sold Total Profit Ebay (10%) Paypal (2%)
Quavers 5 £0.30 £0.05 £1.50 £1.75 4 1 £1.00 £4.00 £2.25 ? ?
Skittles 5 £0.30 £0.05 £1.50 £1.75 1 4 £1.00 £1.00 -£0.75 ? ?

so this is the table i have (as shown above) and as i said previously i change the SOLD cell and the QUANTITY changes along with all the other cell. But My problem is when I make sales outside of Ebay these fees dont occur (ebay 10% & paypal 2%). Also some time my purchase price might change so this will change on import tax etc as well. So I need a table where I can have a drop down box or enter a value and have options of a changing values but still keep adding/showing the profit and costs.

I hope I have explained this clearly. I hope someone with vast knowledge can help !!! :/

Thanks for looking guys

2. Paulo,

Welcome to the Lounge as a new poster.

I had trouble lining up your columns and understanding what you wanted exactly but perhaps this will get you started.

Paulo.xlsx

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

alikazmi (2015-01-05)

4. This is what it actually looks like. so have the majority of formuals working. Got to shoot to work but I will add more to this later.

5. here ismy table with the formulas shown also. As I said i need something that will allow me to have options on inserting different prices (price paid, price sold import tax etc will change depending on what i buy them for) but i need it to allow me to show the profit im making.

6. 1. You don't need to use SUM everywhere, it's for adding a range but you have listed individual cells, so "=F18/B18" or "=5.06+25" is sufficient.
2. a. You could add a column between "Price Sold" and "Total" called "ebay fees" and enter "y" to add fees
b. Change "Profit" to "Gross Profit" and add a "Net Profit" field at the end.
c. Change your fees formulas to check for a "y" in the "ebay fees" column. e.g. if(lcase(K18)="y"),H18*-1.28,"")
d. "Net Profit" would be "Gross Profit" - fees.

cheers, Paul

7. Before continuing I would suggest you clean up your formulas using RetiredGeek's model spreadsheet as a guide. For simple arithmetic operations, avoid using SUM() function as that model shows.

If I understand you correctly, when you restock your inventory, the price (& fees) you pay may vary. Likewise, your selling price for a particular item can vary as well. (Both of these would be the typical case.) If this is the case, treat your problem as two lists--one for inventory acquired and one for sales you made to customers . You will need an item ID column in each of these tables. These two lists could be on the same spreadsheet, but I would suggest separate spreadsheets.

Your inventory and sales spreadsheets should have rows that represent a single inventory/sales transaction of a particular item.
Later, a third spreadsheet can be developed to present up-to-date information, such as current inventory levels and your profits.
(Your current design combines inventory, sales, and status items all in one row.)

Focus on the organizing your lists first before worrying about other things, such as automating future data entry.

I, too, are unclear about the definitions of some of the numbers you need to track and I don't know the proportion of Ebay to nonEbay transactions you have. Your Import Tax column currently has some formulas that add 2 or more numbers together. What do the separate numbers represents, e.g., in =8.76+29+25 what do 8.76, 29, and 25 represent.

8. Firstly can I just say thanks guys for replying & putting you're free time into helping me

[QUOTE=DavidS;
[B]
[''If I understand you correctly, when you restock your inventory, the price (& fees) you pay may vary. Likewise, your selling price for a particular item can vary as well. (Both of these would be the typical case.) If this is the case, treat your problem as two lists--one for inventory acquired and one for sales you made to customers . You will need an item ID column in each of these tables. These two lists could be on the same spreadsheet, but I would suggest separate spreadsheets.''][/B]

The price paid (column 2) & import tax (column 3) will vary due to better deals and then the total value of that deal has a knock on effect with the import tax (higher the value more i must pay). The price sold (column 8) would also vary but very rarely as i have set prices for sales on ebay and off (but if i was able to make if flexible that would always help but not a problem otherwise).

[''Your inventory and sales spreadsheets should have rows that represent a single inventory/sales transaction of a particular item.
Later, a third spreadsheet can be developed to present up-to-date information, such as current inventory levels and your profits.
(Your current design combines inventory, sales, and status items all in one row.)'']

So I would have two spreadsheets and the sales spreadsheet should have a list of every single item sold? Would that not become a huge list eventually? I wouldn't have the know how on how to link the two to show me the profit margin. I'm going on formulas I learnt at school! :/

[''Your inventory and sales spreadsheets should have rows that represent a single inventory/sales transaction of a particular item.
Later, a third spreadsheet can be developed to present up-to-date information, such as current inventory levels and your profits.
(Your current design combines inventory, sales, and status items all in one row.)'']

Hi David yes the two numbers added together cover import tax and currently non sterling transaction fees which are incurred from the total paid for the items/stock on that one transaction (which i hope to eliminate with a new credit card, separate quest altogether!)

Paul T

[''1. You don't need to use SUM everywhere, it's for adding a range but you have listed individual cells, so "=F18/B18" or "=5.06+25" is sufficient.
2. a. You could add a column between "Price Sold" and "Total" called "ebay fees" and enter "y" to add fees
b. Change "Profit" to "Gross Profit" and add a "Net Profit" field at the end.
c. Change your fees formulas to check for a "y" in the "ebay fees" column. e.g. if(lcase(K18)="y"),H18*-1.28,"")
d. "Net Profit" would be "Gross Profit" - fees.'']

Yea I can see from RetiredGeek's simple table that he has made it much more simpler and easier to understand then using SUM everywhere.
Would the ''y'' method work if i have different items with different fees (based on their sale value)? so change the 1.28 to what ever the 10% of the value is. (think i answered my own question there).

thanks

9. So I would have two spreadsheets and the sales spreadsheet should have a list of every single item sold?
Yes, that is the idea as you can set individual prices / costs per sale, much as you do now.

the two numbers added together cover import tax and currently non sterling transaction fees which are incurred from the total paid for the items/stock on that one transaction
This is why you need one line per purchase as these amounts are likely to change each purchase.

The 3rd sheet will perform a "sumif" on the stock and sales sheets to collect the total costs and income from each stock item. e.g. sumif(\$H\$10:\$H\$2500,"widget",\$J\$12:\$J\$2500)
You will need to ensure you always use the same stock item name, maybe via a dropdown, or just by using the built-in auto-fill.

cheers, Paul

10. Yes, that is the idea as you can set individual prices / costs per sale, much as you do now.

This is why you need one line per purchase as these amounts are likely to change each purchase.

The 3rd sheet will perform a "sumif" on the stock and sales sheets to collect the total costs and income from each stock item. e.g. sumif(\$H\$10:\$H\$2500,"widget",\$J\$12:\$J\$2500)
You will need to ensure you always use the same stock item name, maybe via a dropdown, or just by using the built-in auto-fill.

OK I don't mean to sound ungrateful or lazy in any sense but this above me by a long hall.
So the first spread sheet should have what on it? All my bought items listed with Price Paid, Import Tax, Non Sterling Fees, Total (would I include in this spread sheet Ebay Fees, Paypal Fees?) Basically all minuses/money im spending.

The second sheet will have ?

The 3rd sheet will have all the sales recently made entered on to this sheet?

I dont mean to sound like an idiot but this so complicated to me!!!

thanks Paul.M

11. You have two things to track, outgoings in the form of purchases, taxes etc. and incomings in the form of sales, minus fees etc. Having each on a separate sheet allows you to record each transaction as part of the group out or in, then use a third sheet to add the things up to find out how much you've made / lost. For example, you could work out average cost of widget A and average sales price of widget A on the third sheet.
It's worth setting up the two sheets for stock and sales as it will track exact costs and encourage you to do more things with your spreadsheet.

cheers, Paul

12. Paulo,

If you want to manipulate the data, maybe something like this might help.

Your data is entered via a form that pops up when you click on the "New Item" button. There are combo boxes for certain fields and text fields for others. As data is entered calculations are performed on successive textboxes. The percentages for Ebay and PayPay can be any percentage you select as well as selecting 0. When your data entry is complete and you are satisfied with the inputs, click the Enter button and it will write it to the next available record.

If you want to adjust a previous record, select any cell in that row and load it using the Load button. Make your adjustments and click enter. The adjusted record will be rewritten back to the same line. There is also a clear button to clear the form.

The form's modal is set to false meaning that the focus is not locked on the form. You can work around it. If you find this an acceptable solution, I will write the validation for the textbox entries.

Paulo1.png

In your example (highlighted in yellow) your total profit was -189.22 because you did not apply the value of your inventory (46 x 4.29 per unit = 197.34) which is normally kept on a balance sheet. If you add it to your Profit of -189.22 your actual profit is 8.12

Here is the proper way to set up your statement:
Paulo2.png

HTH,
Maud

13. Above and beyond, as usual!

cheers, Paul

14. ## The Following User Says Thank You to Paul T For This Useful Post:

Maudibe (2014-12-19)

15. Thanks Paul. Still needs some tweaking to update textboxes when changes are made

16. Thanks PaulT & Maudibe I've had a manic house with the 14 nieces & nephews. Let me have a quick look at this and get back to you. I am more then grateful for this help!!! It will make a huge difference for me

17. I will be honest Maudibe I read you're comment and didn't have a clue what was being explained but now I have viewed the attached sheet, the pictures and re-read you're explanation it makes sense. This sounds exactly what I need with the ability to change all details add or change ebay and pay pals fees (or zero from sales away from ebay) and edit if any mistakes made.

screenshot1.png
hopefully this picture comes out. This is the full table I have now. Its a bit messy as you can see.

In regards to my example highlighted in yellow I set the formula in minus to show that when I have sold enough it will eventually come out of minus into positive showing me an eventual profit. As I say my knowledge it very poor so I went with what I was taught at school.

Page 1 of 3 123 Last