How to generate vehicle wise profitability

[choudhuryshouvi]

Hello,

I am creating an automobile finance system for retail bike dealer. The system contains features using which inventory, purchase, sales and party (customers, suppliers, manufacturers, financers) details can be maintained.

Here inventory indicates to vehicles only which can be two wheeler, three wheeler and four wheeler. A lot of information is used to store with inventory, among these the following are crutial :-
1. car value, 2. down payment, 3. no. of EMI(s) and 4. EMI value.
(here EMI means Equal Monthly Installment)

when purchases are made inventory gets increase by qty and on sales it decreases as it normally should.

all these are working great and the system is also doing fine. the system is almost ready but I've stucked in a point where i need to generate a report based on vehicle wise profitability. i am using crystal report for reporting services.

the payment procedure in this system is as follows :-

1. customer buys the vehicle of his/her choice paying a lumpsum amount as down payment. the actual value of the vehicle, no. of emi(s) and emi value have been stored in inv. master and these all are getting stored when the sales invoice is created.

2. after this,customer pays only emi month by month to clear the rest of amount. details of emis such as no. of emi (which the customer is currently paying), its value, due date , payment date, latefine (if customer pays the emi after due date is crossed) and net amount (actual emi value+latefine) are generated by the system.

this procedure continues till the full amount for the vehicle is recovered from the relevant customer.

now i am on need to create a report which can show vehicle wise profit/loss the dealer is making by selling each vehicle.

my question is, how can i generate the profitability....can anybody give me idea based on the above scenario to compute profit/loss vehicle wise.....if there is any kind of formula for creating the profitability, that will be much better....plz send if there is any resources regarding this are available....if possible plz send the link to my email :- [email removed]

plz help in this....its very urgent....
any help will be appreciated...

thnks in advance...
regards
Shouvik (India)

[Fortinbra]

When determining profitability, you need to take into account several things, how much overhead there is on the dealer side, how much the dealer pays for the vehicle, and how much customer pays the dealer for the vehicle.
Estimates would show this:
What dealer pays (a), what customer pays (b), what the dealer has from individual sale(c), what it cost the dealership to make the sale(d), and your profit (e)
a-b=c
c-d=e
or
(a - b)- d = e

That is what I think would be the most basic form of how to determine profit.

[choudhuryshouvi]

hello fortinbra....first of all thnks for your quick reply...really appreciated it

i should explain you a few things...

# first of all, i need to generate the profit/loss based on the scenario i mentioned here only...forget what the outside world says or whatever flow is followed in other systems....as i already mentioned this system is meant for retail dealers only who run small and middle size showrooms..not for wholesale dealers....

# u mentioned overheads which signifies cost for the dealer for making purchase of vehicles and prepare the same for sales.....for your kind information, there is no facility of maintaining overhead costs in this system...actually this system is built under supervision of a dealer who doesn't require maintaining overheads....so (d) will never come in the picture...

# so to generate the profit, i have to reply on (a) and (b) only...

purchase of a vehicle and sales made through out the year....each transaction posses original value of the vehicle and a down payment that has to pay when selling is carried out....now for (a)-(b), which amount should i undertake...the cumulative sum of original car value or the sum of down payments....

again each month customers pay the emi...which is also income against the sale of vehicle to the dealer....should this be taken into account or not...

further comments/reply regarding this will be appreciated....

thnks once again for your reply...

regards
Shouvik

[Fortinbra]

From what I understand, and I know business practices are different in different parts of the world, but profit is what you have left after all expenses are paid. Your saying that my original statement, (a-b)-d = e, is now, (a-b)=e, because the dealer overhead is not being considered here. What I was intending (b) to be was the total amount to be paid for the vehicle so, b = (emi * # of months), or the total amount that is going to be paid for the vehicle. So total profits for the car would be this (I'm assigning new letters here):
(a)EMI
(b)# of payments
(c)down payment by customer
(d)what dealer paid for vehicle
(e)profit

For Total profit after vehicle paid off
((a*b)+c)-d = e
For Total profit on a monthly bases
(a-((d-c)/b)) = e
Alternative Monthly profit
Same as above, only (d) is down payment + (a) times the number of complete payments and any applicable fees. (d) = total amount paid so far by customer

[choudhuryshouvi]

again thnks for your reply....i will try the estimates you have mentioned....i dnt need to compute monthly profitability....so i will consider using first estimate only

though i have some confusion in my mind which i need to clarify here....

according to your first estimation, lets take an example....keep in mind this example is based on the flow maintained in my system :-

# purchase transaction : original car value : 80000
down payment : 15000
emi value (with interest) : 2500
no. of emi(s) (within this the full amount is expected to be fully recovered) : 24

now the vehicle is sold to one customer, so the sales transaction becomes
# sales transaction : original car value : 80000
down payment : 15000
emi value (with interest) : 2500
no. of emi(s) (within this the full amount is expected to be fully recovered) : 24

now there we have two points :-
1. no. of emi/ emi value/monthly emi payments from dealer side against the purchase of vehicle (which he made after purchasing the vehicle from some of his suppliers) is not maintained in the system as the dealer doesn't need this....so when any purchase is made, the original car value will be taken as the transaction amount against the same.

2. for sales, no. of emi/emi value/down payments/monthly emi payments will be taken care of. so when the customer purchases the vehicle, he only pays the down payment and rest of the money in EMI(s).

now assume, that the customer has paid two emi(s). both with 2500/- each.

so if first estimate according to your formula is taken, the scenaio will look much similar like this :-
(a)EMI -> 2500
(b)# of payments -> 2
(c)down payment by customer -> 15000
(d)what dealer paid for vehicle -> 80000
(e)profit/loss (which is to be computed)

((a*b)+c)-d = e
i.e, ((2500*2)[phone] = -60000

it results in loss here and it will for every transaction till the full amount is realized.....so profit will never come out.....now plz suggest if this evaluation is correct based on the scenario....plz keep in mind, i have mentioned the word "profitability" here not profit....so i will need to compute both profit or loss against the vehicle whichever will come out

hope i have clearly visualized the fact here which i am trying to say....the major confusion is in mind are the monthly emi payments collected from the customer....this will ofcourse income against the vehicle but when we generate the report, it will show loss against the vehicle....here i have stucked and not been able to find out what should be exact and correct resolution for generating profitability....

kindly sneek in and reply as soon as possible...
looking forward for some early and positive replies...

thnks once again for all your help
regards
Shouvik

[Fortinbra]

that particular formula was for after the 24 months, which based off your numbers still results in a loss of 5000
((2500*24)+15000)- 80000 = -5000
60000 +15000 - 80000 = -5000

If you were to change your EMI to 3000 just for the sake of theory
((3000*24)+15000)- 80000 = 7000
72000 +15000 - 80000 = 7000

Determining what the profit/loss is after 2 payments would be monthly, or accumulatively rather.

[choudhuryshouvi]

never mind about my figures...those are imaginary values....but your last post has opened my eyes...thnks for that....i will use this formula....

thnks for all your efforts....

regards
Shouvik