Enquires, Quotes, Sales required to hit a plan. The average premium and strike rate are constant

[leedavis88]

Hi.

I need some guidance in preparing a sheet to calculate Enquiries, Quotes, Sales required report to hit a plan based on current performance.

So If agent A has had 100 enquiries, put out 80 quotes, 7 were accepted at an average premium of £660, how many enquiries, quotes and sold units would be needed to hit £44,000 plan.

Thanks for the help in advance. Lee

 

[Toadstool]

Hi Leedavis88,

So if an average sale nets £660 and the target is £44,000 then you would need to make 44000/660 sales, or 66.66 recurring sales. As you can't make a portion of a sale then you should round up to the nearest integer, 67.

You needed 8 quotes to get 7 sales so divide 8/7 gives the ratio of 1.142857143 which we can multiply by the 66.66 sales needed to get quotes needed, 76.18285714 which again is rounded up to give 77.

Same for enquiries where 100 gets 7 sales so each sale needs 14.28571429 enquiries so for the needed 66.66 sales we'll need 14.28571429 multiplied by 66.66 gives 952.2857143 enquiries which rounds up to 953.

Book1
	A	B	C	D	E	F	G	H	I
1	Enquiries	Quotes	Sales	Average Premium		Target	Enquiries Estimate	Quotes Estimate	Sales Estimate
2	100	8	7	£ 660.00		£44,000.00	953	77	67
Sheet1

Cell Formulas
Range	Formula
G2	G2	=ROUNDUP(($F$2/$D$2)*($A$2/$C$2),0)
H2	H2	=ROUNDUP(($F$2/$D$2)*($B$2/$C$2),0)
I2	I2	=ROUNDUP($F$2/$D$2,0)

 

[leedavis88]

Thank you. I'll give this a run in the morning.