Ifs and Sums and Nests or something more simple

**CplSmudge** · 03-20-2016, 06:22 PM

Hi all - sorry for lots of text, I am struggling to explain this succinctly, but please read on.

I am trying to find a simple way to calculate the cost of a sale when an item is sold, based upon its purchase price which might vary during the year.
I have a spreadsheet of sales orders which runs into 15,000 over the last year and I am trying to make something more simple. currently, I have a sale per row; column B is quantity purchased, column C is a running count of the total product purchased (so C6=C5+B6 for example) and column D is the cost of this order.

On another spreadsheet page (Purchases) I list the product and any variation in price. From other data that populates with how many have been purchased at each price and the idea is to be specific about the cost of the product for the actual sale.

If sales are more than the total I purchased in order 1 then I need to add my purchase orders 1 & 2 together, but if sales are more than that total then I need to add order 3 and so on. When I get to a total of ordered product that is greater than the total of sold, I can then use the cost of the item in the relevant order to calculate the cost of the sale.

I have attached a simple example of the query and I am looking to automate the process by way off function to resolve column D

Any help towards a sensible formula to work that out so I can copy it into every row in the table would be greatly appreciated, thank you.

regards and thanks for reading

Cpl Smudge

**humdingaling** · 03-21-2016, 03:26 AM

the one way i could of think of doing this is having some sort of running total for stock run along side your running sales

see attached file

**CplSmudge** · 03-27-2016, 02:33 PM

Hi Humdingaling
thanks for your response. I have been considering it and playing around with it a little and wonder if you might give some thought to the following for me;
Your idea works well if there is only 1 product to sell and record. I was hoping to have 32 products and to use your method that would then mean that each column per product would need to do the math etc for all of the rows of sales records which did not actually sell that product, simply because a new record had been added.
I have been trying to get around that because I like the principle of your idea so, I am wondering if the solution would be to have an area of the sheet that recorded the highest row number that currently held a sale record for that particular product and then to use that indirectly to produce your results.
So, if rows 1,3,6,7,8,10 sold product 1 and rows 2,9,11 sold product 2 and rows 4,5,12 sold product 3 when the row did not sell the product in the table being referred to the figures would not recalculate but as soon as the next row sold a product to match then it would recalculate
Every day's a learning day, for me anyway, so if you like this thought, I would use MAX to find the row number each time something like =SUMPRODUCT(MAX(ROW(1:12)*(B1:B12=C3))) but then when I had an answer, I would want to take that to another cell and write it so it wouldn't change. Is that possible?

My thinking is it would calculate the answer for row 1, then a different answer for row 3 and so on but in row 1 I would want to keep the answer that it gave me at that time

thanks again

Cpl Smudge

**humdingaling** · 03-29-2016, 10:55 PM

not sure your solution would work....it could bring across the lines but i don't see how it would allocate the correct costs for a portion of the sale

this is not something i would normally do in Excel
if you had 32 products my solution would be have 32 tabs.....as this needs to be done at a product level
if you want overview you combine the data afterwards

I've asked other experts for advice to see if they can see some other way to do it that i dont know of

**CplSmudge** · 03-30-2016, 02:07 AM

Ok, thank you for your efforts. I'll wait with hope

regards

Cpl Smudge

**tony h** · 03-30-2016, 03:25 AM

CplSmudge in post #3 you describe what you want to achieve but I am having difficulty visualising it.
Could you dummy this up on a spreadsheet for 2 or 3 products.
Also could you describe how you will use the results - sometimes it inspires new ideas.

**JohnTopley** · 03-30-2016, 03:50 AM

I think what you require is "FIFO" inventory management ....

Take a look here ....

https://gallery.technet.microsoft.co...-with-36dafa3c

**CplSmudge** · 03-30-2016, 05:56 AM

Thank you all for looking and thinking. I have attached an example spreadsheet and I hope that my notes are self explanatory but I remain available to answer queries.

regards

Cpl Smudge

**CplSmudge** · 03-30-2016, 06:03 AM

John
I have looked and this is great for an overall perspective but doesn't work on an individual order basis.
If I can have individual order basis, including for multiple product orders, then the user can determine the gross profit immediately and it will allow a margin for price discounting in order to increase business.
At the back office it will also help determine the stats for type of customer against best profit etc and cost of overhead against size of order

regards
Cpl Smudge

**JohnTopley** · 03-30-2016, 08:30 AM

This is a solution (2 actually) close to that proposed by Humdingaling: I think he is correct in believing you might need a sheet per product.

I would avoid using INDIRECT with large data volumes to mitigate potential performance problems.

http://accountingexplained.com/finan...es/fifo-method.

If you want to multi-product orders then it appears to me to be a complex calculation as (on my very limited understanding) you need to "FIFO" the individual products and the summate to get your base costs for a given order.

**CplSmudge** · 03-30-2016, 10:00 AM

Thanks John
If I end up having to have a sheet per product then that would necessitate a separate workbook I think with the product purchase data and the calcs in one and the sales and cost per order in another.
I've looked at your link and the perpetual version is exactly the right calculation I am looking for and I can see why it seems easier with that layout to have 1 product per sheet.
In terms of multiple product orders in my "perfect vision" I would have 32 columns to record the cost per product and to the right of those would be a sum total of the row across those columns so that would take the nastiness out of that

regards

Cpl Smudge

**humdingaling** · 03-31-2016, 12:01 AM

you had this in your original sheet but i fought it was an oversight but seing you have it again, i feel the need to point out

your assumption for cost of product in stock is not the same way you are applying costs of goods sold

ie for Tablets - 25kg
there is 36 in stock
you are taking 3.6 as price per unit (and also your average price per unit) and multiplying by 36 giving you 129.60

in your sales line you ware wanting to exact
6x3.87
10x3.70
10x3.65
10x3.60
which equates to 132.72 total costs of goods remaining

i dont think you should be using two varying ways of cost assumption in the same spreadsheet
as your projected revenue vs actual will not align (and will get even more confusing if the sale price changes)

**CplSmudge** · 03-31-2016, 01:53 AM

Humdingaling - thanks.

You are right of course but I am not wrong (yet).
If I can resolve the issue for cost of order then I was intending to be using that as a basis for finding the cost of balance of stock. In the meantime, I had just used the formula to make sure I was getting everything accounted for and I have populated some cells with purchases to show in the example that the purchase price could go up or down rather than leave it to imagination.
Of course, in the real world with the figures I expect to be used, the volumes will be great enough that the stock left after sales will be the last purchase quantity plus a remainder of the penultimate purchase quantity at that time but not leaving anything to chance (or trying not to), it will be changed.

regards

**JohnTopley** · 03-31-2016, 08:08 AM

Another site (thanks to Jason: similar post today on FIFO)

Have a look at this http://www.sumwise.com/blog/fifo/

**CplSmudge** · 03-31-2016, 02:32 PM

Thanks to Jason and you again John

I've opened the link and had a glance at this and it being an array formula leads me to think I would use this in a protected sheet and then take values of cells to another.
While I was out today I had the thought that maybe it might be easier if I identified the row that the last order was placed in and was then able to use the row, perhaps as an INDIRECT way of arriving at putting the answer to the calculation in the right place. Maybe I am just tired but I think the calculation to get the right cost figure is potentially solved now so I need to concentrate on getting the latest answer to that calculation in the correct cell whilst retaining the previous answers for earlier orders?

**CplSmudge** · 04-02-2016, 09:58 AM

I have attached the latest draft of "Looking for FIFO"

I have simplified things for now with only 3 products and 2 spreadsheets. I am hoping to improve on the formula in the columns Y through AA to be more exact

**humdingaling** · 04-03-2016, 10:49 PM

in your example you aren't really doing FIFO for quantity brought....but rather consolidated bought quantity at set price
you have 15 receipt lines for product 1 but 5 of which have numbers
for FIFO to work this shouldnt happen
what this will give you is a quasi average costings and will actually not give you want you are requesting

taking your first line
your formula is working on the basis - 3000 x 3.87 = 11610

however if you are using FIFO
3000 sold it should equate to 11729.07
562 x£3.87
1078 x£3.87
1029 x£3.87
49 x£6.30
282 x£3.87

for the second sales order
1325 units sold, costs 5035 which is 1325 *3.8
however
you have 796 units left at 3.7
796 x 3.7 = 3080.52
549 x 3.8 = 2010.2
Total 5090.72

Major changes in purchases sheets and additional columns added to demonstrate costs with helper to calculate this

**CplSmudge** · 04-04-2016, 02:01 AM

Hi Humdingaling

This link shows where I am at now, which is a lot further on than the post to which you just replied

http://www.excelforum.com/showthread.php?t=1133715

I feel that I am very nearly there - with FIFO

Ifs and Sums and Nests or something more simple

LinkBack

Thread Tools

Rate This Thread

Display

Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Re: Ifs and Sums and Nests or something more simple

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] URGENT - how do you do simple sums with cells set to date format

[SOLVED] Pivot or VLOOKUP or INDEX - simple sums across rows...

[SOLVED] Too Many "Nests"

Too Many Nests

Nested IF functions not working for more than 2 nests

Simple Sums?

Posting data from a simple form to a simple database

Bookmarks

Bookmarks

Posting Permissions