MS Excel - Avg and Number of Sales and Re-Sales over intervals

[BxWill]

Have a voluminous monthly MS Excel worksheet (Over 240,000 records) with equipment sale and re-sale data throughout the
year such as displayed below.


EqNo------- Region------ SaleAmount-------SaleDate
A-250------- 146------- 8,000------- 1/5/2018
A-300------- 146------- 25,000------- 1/7/2018
A-400------- 146------- 6,800------- 1/15/2018
A-589------- 146------- 5,800------- 2/3/2018
A-600------- 146------- 6,004------- 2/15/2018
A-250------- 146------- 10,000------- 3/4/2018
A-300------- 146------- 27,000------- 4/15/2018
A-400------- 146------- 7,500------- 9/15/2018
A-589------- 146------- 5,000------- 8/25/2018
A-600------- 146------- 20,000------- 11/10/2018
B-200------- 147------- 84,503------- 2/1/2018
B-300------- 147------- 25,450------- 1/23/2018
B-400------- 147------- 7,250------- 1/5/2018
B-550------- 147------- 6,250------- 3/15/2018
B-655------- 147------- 16,454------- 1/2/2018
B-250------- 147------- 20,450------- 3/17/2018
B-200------- 147------- 100,000------- 5/15/2018
B-300------- 147------- 28,000------- 6/2/2018
B-400------- 147------- 8,250------- 10/23/2018
B-550------- 147------- 5,000------- 6/8/2018
B-655------- 147------- 30,000------- 6/26/2018
B-250------- 147------- 35,000------- 8/25/2018


Objective is to populate the MS Excel worksheet as displayed in the second image below.

In other words, I need to determine the variance between the Equipment sale prices over time and create "buckets" to display the number of equipment, the average sales price and average re-sale prices for the equipment by month by "buckets."

Note, each piece of equipment can be sold and re-sold multiple times during the year.

The time buckets are as follows;

# 0 - 6 Month Resale
0-6 Months Avg purchase price
0-6 Month avg resale price
# 6-12 Months Resales
6-12 Months Avg purchase price
6-12 Month avg resale price
# 0 -12 Month Resales
0-12 Months Avg purchase price
0-12 Month avg resale price

So far, I have contemplated adding a "helper" column to calculate the difference between the equipment sale dates for each unique equipment and then use a pivot table to group the data by month.

Then considered the use of sumproduct.

Still reviewing/experimenting with various options... objective.

I have attached an MS Excel spreadsheet for reference.

Appreciate any insight as to an efficient method to accomplish the objective.

 

[SkipVought]

Hi,

You have attached nothing!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Please explain what the month spans mean? For instance when does 0 - 6 months start and end?

How do you differentiate the Purchase Price from the Resale Price?

Need these before attempting any solution.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BxWill]

Well, this particular problem is one of the most challenging...

It appears that "0 - 6 months" refers to the case when a particular equipment is sold and then re-sold within 6 months.

So, if equipment A is sold initially on January 1, 2018 and then sold again on March 1, 2018, then it should be counted as "1" in the January 2018 bucket (because it was initially sold during January 2018) under the column "0 - 6 months" because it was sold again within 6 months.

Then, it appears that I would need to average the sale price for all of the equipment that was initially sold during January 2018 and also average the re-sale prices for each equipment that was initially sold during January 2018 while noting the intervals between the initial sold date during January 2018 and the subsequent re-sale date throughout the year.

Perform the same for the equipment that was initially sold during February 2018.

And so on.

 

[SkipVought]

As previously noted...

1) you have attached nothing!

2) How do you differentiate the Purchase Price from the Resale Price?

Another question: Average based on what values as a base? A specific equipment?

Please give formula or logic for a specific example.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BxWill]

As far as the distinction between Purchase Price and Resale Price, I initially thought that there was missing data.

It appears that anytime that the equipment is sold again, then the price is considered a re-sale.

It very well could be that there is equipment that has not been re-sold during the year.

 

[SkipVought]

I purchase a Widget for $1.00 in January.

Then I sell the Widget for $1.25 in February.

How do I sell the widget again, for instance in July unless I re-purchase before re-selling?

I am TOTALLY confused!

Notice that the Purchase price has nothing to do with any sale or resale price.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BxWill]

It appears that the Widget is "repurchased" by the second owner.

For this problem, it appears that "repurchase" does not imply the original owner but only used to designate that the particular equipment is sold again - albeit to a different owner each time.

 

[SkipVought]

Still don’t know a single thing about the purchase price.

All you have are SALE prices!

What? We have data from different “owners”?

This is data for a SELLER. the SELLER must first have purchased an A-250 before he can SELL an A-250.

Where is the purchase price for the A-250?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BxWill]

Apologize for the confusion.

Inadvertently selected data from the incorrect worksheet.

Displayed below is what appears to be the appropriate data.

 

[BxWill]

Thank you all for the pointers.

 

[SkipVought]

No COPY ‘n’ PASTE data?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

“Displayed below is what appears to be the appropriate data.”

Appears? Don’t you know for sure?

Why do we have Purchaser? Are these purchasing agents for the seller? Is this relevant data?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Just looked at your PICTURE closer, and noticed that Sam, Shila, Ted, Kate, Phil are on the SELL side and the PURCHASE side with same dates and amounts???

That makes absolutely no sense!

The SELLER is what this seems to be about. The SELLER must make Purchases in order to have stock. Some Purchases that the SELLER makes are original Purchases and some seem to be re-Purchases at some later date after the SELLER Sells an item.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

The way I ‘read’ this picture is:
Jane had purchased equipment A-250 (from somebody) for $4,000 on 1/1/2018 and she sold it to Sam for $8,000 a few days later on 1/5/2018 (nice mark-up of 100%, BTW). Then Sam had sold this A-250 (to somebody) for $10,000 on 3/4/2018

Or a story of A-600
Mary bought it (from someone) for $1,000 on 1/10/2018 and sold it to Phil for $6,004 on 2/15/2018 (nice mark-up!), and then Phil sold it to (somebody) for $20,000 on 11/10/2018
What is this A-600 because I want to invest in it – 20 times more in 11 months! Bernard Madoff – eat your heart out!

Looks like money laundering enterprise to me…


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

So how do we account for an item that is purchased and added to the inventory and has no Sale at report time; therefore SaleAmount and SaleDate are empty.

How would this be handled?

Suppose this item is sold one time, so this item does have a single SaleAmount and SaleDate.

How would this be handled since there is no resale to compare?

How are month differences to be calculated? Month has no exact mathematical definition. These differences could be calculated in a number of different ways as integral or decimal values.

It also seems to me that purchases and sales need to reside in separate tables. My experience in manufacturing resource systems has tables for requirements (sales or demand) and other tables for replenishments (purchases or supply). They are often joined to display net results (inventory, requirements and replenishments net results showing surpluses and shortages). In order to calculate the month differences, separate tables will be necessary, I believe.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]