What is the best way to analyze profitability of related, but separate transactions? 7

[montypython1]

Greetings,

What is the best way to analyze profitability of related, but separate transactions?
Specifically, I am trying to analyze the profitability of vehicle sales transactions, but I would like to include the subsequent trade-in transactions.

It is very common for a car dealership to sell a new vehicle and take the customers old vehicle in as a trade-in. They may not make much money on the new vehicle transaction, but they may make a lot of money on the subsequent sale of the older trade-in vehicle. My objective is to connect these 2 (or possibly 3 or 4) separate transactions together to analyze the profitability of entire string of related transactions.

Every vehicle sold, whether new or used, contains a unique stock number (ex: "C77118"). If a customer buys a vehicle and trades in their old vehicle, then that stock number (ex: "C77118A") is included within the original transaction. If the trade-in vehicle is subsequently sold, and there is a trade-in (ex: "C77118B") on the second transaction, then the cycle should continue. I want to collect the profitability of each transaction, then summarize them together.

I am attaching a very small CSV data file that I am using for testing purposes.

If anyone has any ideas, I would certainly be grateful.

Thank you,
Dave

 

[Mike Lewis]

Good morning, Dave.

Completely off the top of my head, it sounds like you need a vehicle table and a separate transaction table. The transaction table would contain the ID of the vehicle being sold, and the ID of the vehicle being accepted in trade (if any). A given vehicle could have multiple transaction records in which it was sold, and multiple transaction records in which it was traded-in.

The transaction table would contain the value of the sale vehicle, the cost of the sale vehicle, and the amount allowed for the trade-in. It should then be possible to take a specific vehicle, find all the transactions that it has been involved in, and then total the relevant values.

As I said, this is off the top of my head. It's possible you have already got this far in your thinking.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>A given vehicle could have multiple transaction records in which it was sold, and multiple transaction records in which it was traded-in.
No actually each vehicle can only be sold once and traded-in once.

The bunch of transactions to sum are the ones with same stock number, excluding the letter suffix, so without looking at your csv you'd need something like
select sum(salesprice)-sum(tradeinprice) as profit, left(stocknumber,6) as mainstocknumber from salestransactions group by 2

Bye, Olaf.

 

[Olaf Doschke]

Well, it's not really the profit I calculate, it's the turnover or revenue. You'll have an initial price to pay to the manufacturer of the initial new car which of course needs to be subtracted, too, and then you have your profit. You'll need to compare the overall sum to the mere revenue you'd have made from the sale of the new car without any trade-in to see if the deal was profitable. You'd also need to subtract any costs in time and efforts, eg loans connected to all the additional transactions. And the space used by the used cars also is hindering to sale more new cars, so you might compute storage/parking costs, too, with some penalty.

Bye, Olaf.

 

[Mike Lewis]

No actually each vehicle can only be sold once and traded-in once.

But a given vehicle can be sold, then traded in, and then sold again. And then perhaps even traded in again. So it would indeed be a one-to-many scenario.

Or maybe it doesn't work that way. Maybe the dealer does not re-sell a traded-in vehicle, or maybe they only allow a given vehicle to be traded in once. I don't know anything about the used-car trade.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well Mike,

Dave explained it quite good.

He wants to summarize related translations with the alternate scenario to not have accepted a trade-in car.

Customer 1 buys a new Mercedes (stock number XX1234) and trade-in hos old BMW (stock number XX1234A),
Customer 2 buys the BMW (stock number XX1234A) without trading in any old car.

These two transactions should be compared against the scenario, if Customer 1 didn't traded in an old BMW, but merely bought the new Mercedes without a discount trade in price for any old car.
The net profit will differ especially if the discount for accepting the old BMW was less than the sale price for it in the second transaction.

The sum(salesprice) here would be the salesprice of the Mercedes plus the sales price for the BMW, sum(tradeinprice) would just be the tradeinprice=discount on the Mercedes salesprice made in the first transaction and 0 for the second transaction, as there was no trade-in.

The two transaction would both refer to stock numbers beginning with "XX1234", the original stock number of the new car in this series of sales. You might also have some transaction of someone just selling his used car without bying a new or used car, but that's off topic in this case, that wouldn't be called trade in.

You'll typically have a higher overall profit, as you'll not sell any car for less than you paid for it, but as said you'd have to also include the efforts in time, work, spare parts, cleaning and parking the car before it's resold. I won't explain your own business to you, Dave, but the query you want won't tell you the whole truth about the net profit you made. Not because I also left out the 0th transcation of your company with Mercedes to get the new Mercedes, but because of the costs you have with the BMW between bying it as trade-in and reselling it and that's not found in the mere transactions.

Bye, Olaf.

 

[montypython1]

Hi Mike and Olaf,

Thank you for your input, as always.

After reading your suggestions, I realized that I forgot to include an important bit of information: We are acquiring our data from a system that already contains a transaction table. My goal is to link together those trade-in transactions. The raw data is below:

DealNum	DealDate	Sold_StkNum	Veh_Profit 	TradeIn_StkNum
91054	1/23/2015	C77118	 	  500.00 	C77118A
92214	2/23/2015	C77118A	 	3,500.00 	C77118B
77467	3/15/2015	C77118B		1,500.00 	C77118B
92151	3/26/2015	C77118C		1,000.00 	C77118C

92205	1/29/2015	K4376A	 	  700.00 	TI1234
92359	2/23/2015	TI1234	 	 (300.00)	TI1356
92459	3/21/2015	TI1356	 	  500.00 	TI1401
92559	3/30/2015	TI1401	 	   50.00

If you look at the first 4 transactions, it seems logical that they should be grouped together (the TradeIn_StkNum from the first transaction becomes the Sold_StkNum on the second transaction; then the TradeIn_StkNum from the second transaction becomes the Sold_StkNum on the third transaction; then the TradeIn_StkNum from the third transaction becomes the Sold_StkNum on the fourth transaction; etc).

Below is how I envision the report to appear, using the first 4 transactions above as an example. The report output should be controlled by the stock number of the original (Orig) transaction and also display the appropriate details of each subsequent transaction (Trans), and should look something like this:

Orig     Trans     Profit
C77118   C77118    $  500
C77118   C77118A   $3,500
C77118   C77118B   $1,500
C77118   C77118C   $1,000
Total              $6.500

The problem that I am struggling with is two-fold:
(1) How do I link the first transaction with the third or fourth transaction? I understand that I can link the first to the second, and the second to the third, but how do I link the first with the third or fourth?
(2) The TradeIn_StkNum designation is Not always a subset of the original Sold_StkNum (see transactions 5,6,7,8).

Does this make sense?

Thank you again for your input and ideas.
Dave

 

[Olaf Doschke]

>How do I link the first transaction with the third or fourth transaction?

Could you please read my answer?

You group by the part of the stock number, which is equal for all related transactions.
In what you envision the summing is even simpler the sum(veh_profit).

So you are posting and asking all this for

SELECT left(Sold_StkNum,6) as Orig, sum(Veh_Profit) as TotalProfit from yourtable group by 1

This'll just give you a total, but you don't need all the intermediate sums.

Bye, Olaf.

 

[mjcmkrsr]

Hi Dave,

(2) The TradeIn_StkNum designation is [highlight #FCE94F]Not always[/highlight] a subset of the original Sold_StkNum (see transactions 5,6,7,8).

Well you have to make up your mind - and the design of your tables/input of your data. If you want to track the transactions you need some data which allow you to do this.

hth

MK

 

[Olaf Doschke]

By the way, even though I notice your remark (2) now, as MK refers to it, your other sample case seems to be wrong:

[pre]DealNum DealDate Sold_StkNum Veh_Profit TradeIn_StkNum
91054 1/23/2015 C77118 500.00 C77118A
92214 2/23/2015 C77118A 3,500.00 C77118B
77467 3/15/2015 C77118B 1,500.00 C77118B
92151 3/26/2015 C77118C 1,000.00 C77118C[/pre]

a) why would the tradein_stknum for Dealnum 77467 be the same as in dealnum 92214
b) why would the last deal have a tradein_stknnum being the same as the Sold_Stknum?

Correct should be:
[pre]DealNum DealDate Sold_StkNum Veh_Profit TradeIn_StkNum
91054 1/23/2015 C77118 500.00 C77118A
92214 2/23/2015 C77118A 3,500.00 C77118B
77467 3/15/2015 C77118B 1,500.00 C77118C
92151 3/26/2015 C77118C 1,000.00[/pre]

If you don't have a same prefix for all stock numbers, you have to track in rverse order from last sale without any tradein stock number back to original stock number. You don't do that with SQL, you iterate and SEEK for tradein_stknum of one record being sold_stknum of another record.

Bye, Olaf.

 

[mjcmkrsr]

Hi Dave,

Besides what Olaf suggests, please keep it simple and rethink the design of your table. You may want to insert a field named TransActionNumber (TAN) and get rid of the two STKNum's since they are redundant and don't give you any more information. You may then GROUP by TAN and ORDER by DealDate to get the desired results

DealNum DealDate TAN Veh_Profit
91054 1/23/2015 C77118 500.00
92214 2/23/2015 C77118 3,500.00
77467 3/15/2015 C77118 1,500.00
92151 3/26/2015 C77118 1,000.00

92205 1/29/2015 K4376A 700.00
92359 2/23/2015 K4376A (300.00)
92459 3/21/2015 K4376A 500.00
92559 3/30/2015 K4376A 50.00

hth
MK

 

[Olaf Doschke]

Well, MK,

I think you oversimplify this here. Since the trade in cars are new stock they should have their own stock number.
But you might add a constant value like the original stock number throughout all records to have a simple column to group by, and that might be called TAN, though that term is bad, if you ask me, comes from banking and simply means transaction number, which should differ from transaction to transaction. The new column could be as simple as rootstocknumber.

Bye, Olaf.

 

[Mike Lewis]

And of course TAN is the name of a VFP function. Not that it matters very much.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Dave,

Starting from the data you posted (I modified them slightly) I would write something like this

CREATE CURSOR crsCars (cDealer C(25), cStockNumber C(6), dDealDate D, cDealNumber C(6), cNUO C(1), cCarSold C(25), ;
						cCarTradedIn C(25), nBought N(8,2), nSold N(8,2))
						
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","91054",{^2015/1/23},"C7711","N","2012 BM 328CIC","2004 BMW 325CIC",14000,15000)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92214",{^2015/2/23},"C7711","U","2004 BM 325CIC","2002 BMW 323CIC",10000,9500)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","77467",{^2015/3/15},"C7711","U","2002 BM 323CIC","2001 CHEVROLET MALIBU",7500,9000)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92151",{^2015/3/26},"C7711","U","2001 CV MALIBU","2000 MITSUBISHI GALLANT",4000,5000)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92205",{^2015/1/29},"K4376","U","2010 JE COMMANDER","2007 JEEP WRANGLER",13500,12000)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92359",{^2015/2/23},"K4376","U","2007 JE WRANGLER","2004 JEEP LIBERTY",10000,9500)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92459",{^2015/3/21},"K4376","U","2004 JE LIBERTY","1999 TOYOTA CELICA",500,500)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92559",{^2015/3/30},"K4376","U","1999 TO CELICA","",0,0)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92192",{^2015/2/25},"47006","N","2012 VW PASSAT WAGON","2001 HYUNDAI ELANTRA",2000,2500)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92348",{^2015/1/28},"47175","N","2012 VW JETTA SEDAN","2004 CHRYSLER TOWN&COUNTRY",12000,12000)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92172",{^2015/1/16},"67173","N","2012 JE COMMANDER","1997 JEEP GRAND CHEROK",2500,2500)
INSERT INTO crsCars VALUES ("BULLWINKLE MOOSE MOTORS","92196",{^2015/3/10},"67247","N","2012 JE PATRIOT","2006 SATURN 2 ION",6500,7500)

LOCATE 

BROWSE NOWAIT  

SELECT cDealNumber, SUM(nSold - nBought) as Profit FROM crsCars GROUP BY cDealNumber ORDER BY cDealNumber INTO CURSOR crsProfit

SELECT crsProfit

BROWSE 

CLOSE ALL

btw TAN is indeed a bad name for a field - it is a mathematical function and has also a financial/banking connotation - sorry

hth

MK

 

[montypython1]

Hi Mike, Olaf, and MK,

Thanks for your input.

My apologies for screwing up my sample data ... I had manually changed it in an attempt to simplify my example. It SHOULD have read as follows:

DealNum	DealDate	Sold_StkNum	Veh_Profit 	TradeIn_StkNum
91054	1/23/2015	C77118	 	  500.00 	C77118A
92214	2/23/2015	C77118A	 	3,500.00 	C77118B
77467	3/15/2015	C77118B		1,500.00 	C77118C << not B (sorry)
92151	3/26/2015	C77118C		1,000.00 	C77118D << not C (sorry)

92205	1/29/2015	K4376A	 	  700.00 	TI1234
92359	2/23/2015	TI1234	 	 (300.00)	TI1356
92459	3/21/2015	TI1356	 	  500.00 	TI1401
92559	3/30/2015	TI1401	 	   50.00

MK, you bring up a good point about standardizing the trade-in stock numbers. Unfortunately,
since the data is coming from another source, I do not have any control of how they designate a trade-in stock number. I have no choice but to use the stock number that was assigned by the dealership (since we are pulling data from another system, we cannot force the input to follow any kind of format; we have to use what is already in the source database).

Olaf, I like your logic of pulling the 6 left characters, but unfortunately it won't work because we do not control the entry of the trade-in stock number (see previous paragraph). A good example of this problem is in the above record numbers 5 through 8. This particular dealership enters a "TI" (for trade-in) followed by a sequence number, while other dealerships may use a totally different set of conventions.

I would think that there must be a way to link all appropriate transactions together (since transaction #1 is linked to #2, and #2 is linked to #3, and #3 is linked to #4; even though #1 is not directly linked to #3 or #4).

I hope this clarifies the problem.

Thanks,
Dave

 

[Olaf Doschke]

Well, you can't control what comes from external data sources, but you can add as many fields as you like after importing the data into your own tables.

Bye, Olaf.

 

[montypython1]

Olaf, I agree with you, but surely there must be a way to link together the 4 transactions in my example.

If transaction #1 is linked to #2, and #2 is linked to #3, and #3 is linked to #4 ... then somehow I should be able to create a common (and automated, not manually entered) index to link them all together ... I can't be the only person needing something like this.

Any ideas are certainly welcome!

Thanks,
Dave

 

[Olaf Doschke]

Why do you still don't get it?

Read in the data, add a field rootstocknumber

Scan the records, if Sold_StkNum is equal to the TradeIn_StkNum of the previous record, store the same rootstocknumber.
If not you have your new rootstocknumber as the Sold_StkNum of that record. That's especially the case in record #1 as there is no previous TradeIn_StkNum.

Finally do the group by on rootstocknumber.

Bye, Olaf.

 

[mjcmkrsr]

Hi Dave,
If you need to group data, you need at some point a group identifier. If you don't have it, you'll have to create it. Please have a look at the code below. Maybe it points into the desired direction.

Please be aware that this code assumes that the information you gave is right, that the data is "pregrouped" in the way you say and that the number of records is reasonable (cf. size of array in VFP)

LOCAL ARRAY laCars[1]
LOCAL liKey, i

CREATE CURSOR crsCars (cDealNumber C(5), dDealDate D, cSold_StkNum C(7), nVeh_Profit N(8,2), cTradeIn_StkNum C(7), cTradeInGroup C(5))
						
INSERT INTO crsCars VALUES ("91054",{^2015/1/23},"C77118",500.00,"C77118A","")
INSERT INTO crsCars VALUES ("92214",{^2015/2/23},"C77118A",3500.00,"C77118B","")
INSERT INTO crsCars VALUES ("77467",{^2015/3/15},"C77118B",1500.00,"C77118C","")
INSERT INTO crsCars VALUES ("92151",{^2015/3/26},"C77118C",1000.00,"C77118D","")

INSERT INTO crsCars VALUES ("92205",{^2015/1/29},"K4376A",700.00,"TI1234","")
INSERT INTO crsCars VALUES ("92359",{^2015/2/23},"TI1234",-300.00,"TI1356","")
INSERT INTO crsCars VALUES ("92459",{^2015/3/21},"TI1356",500.00,"TI1401","")
INSERT INTO crsCars VALUES ("92559",{^2015/3/30},"TI1401",50.00 ,"","")

INSERT INTO crsCars VALUES ("92559",{^2015/3/30},"TI1402",-250.00 ,"TI1403","")
INSERT INTO crsCars VALUES ("92559",{^2015/3/30},"TI1403",510.00 ,"","")

INSERT INTO crsCars VALUES ("92559",{^2015/3/30},"TI1404",590.00 ,"TI1405","")
INSERT INTO crsCars VALUES ("92559",{^2015/3/30},"TI1405",540.00 ,"TI1406","")

INSERT INTO crsCars VALUES ("92559",{^2015/3/30},"TI1407",-50.00 ,"TI1408","")
INSERT INTO crsCars VALUES ("92559",{^2015/3/30},"TI1408",250.00 ,"","")

INSERT INTO crsCars VALUES ("92559",{^2015/3/30},"TI1409",150.00 ,"","")

LOCATE 

SELECT cSold_StkNum, cTradeIn_StkNum, .T. FROM crsCars INTO ARRAY laCars

liKey = 1

FOR i = 1 TO ALEN(laCars) - 3 STEP 3
	IF laCars[i + 1] = laCars[i + 3]
		laCars[i + 2] = PADL(liKey,5,"0")
	ELSE
		laCars[i + 2] = PADL(liKey,5,"0")
		liKey = liKey + 1
	ENDIF 
ENDFOR

laCars[ALEN(laCars)] = PADL(liKey,5,"0")

i = 0

SCAN 
	Replace cTradeInGroup WITH laCars[i + 3]
	i = i + 3

ENDSCAN 

LOCATE 
BROWSE NOWAIT 

SELECT cTradeInGroup, SUM(nVeh_Profit) FROM crsCars GROUP BY cTradeInGroup INTO CURSOR crsProfit

SELECT crsProfit

BROWSE



CLOSE ALL

hth

MK

 

[Olaf Doschke]

Now that's thinking quite alike. k has given code doing what I described.

MK, just one or two things why defined a c(5) field for an integer liKey?

I wouldn't go via array at all, you can SCAN/ENDSCAN and replace in the cursor, but otherwise the idea is the same.

Dave, you have to have a common value to group by, and if nothing is equal there is no sql helping to find a series of records making a concatenated list.

While you fill in rootsticknumber or cTradeingroup you could also create a running sum, by the way, then you wouldn't need to do a final group by query.

Bye, Olaf.