Sales Estimator Calculator

[BLuse]

Hi,

I'm trying to create a calculator to help determine the most profitable price point for an item.

Let's say I have 1000 units of a lotion at $3 cost per unit. I know that at $10, I'm averaging about 100 sales/month and my profit is $2 and it will take 10 month to sellout. That gives me an effective annual ROI of 0.8 (.6667% profit in 10/mo (1000 units/100 per month) is 0.8 in 12/mo).

I want a formula that will calculate the target sales/month for any price point. So in example above, how much does sales need to increase if I lower the price to $9.60, to maintain the same annual ROI.

 

[etaf]

I may not be understanding ROI correctly -

if you have 1000 units , and costs of £8, you say selling at $10 and making $2

then if you reduce the price, you wont get the return you want, as you are limited to the number of units to sell

If you want to make 200 a month , with the reduced price then you would need to sell 125 at 9.6 to make 200 a month - but would sell out in 8 mths = 125*8

As i say I maybe completely misunderstanding your example

But below in XL2BB - i have a calculator to give the volume needed

Total Profit = 1000*2 = 2,000
Total Revenue = 1000*10 = 10,000
Total cost = 1000*8 = 8,000

ROI = would be the total gain / total investment
2000/8000 = 25%

if you reduce the price to $9.6
cost the same
so
Total Profit = 1000*1.6 = 1,600
Total Revenue = 1000*10 = 10,000
Total cost = 1000*8 = 8,000

ROI would be
1600/8000 = 20%

ROI = (Ending value / Starting value) ^ (1 / Number of years) -1.

Anyway - here is a table showing Current and New
and i have a section showing - What I think you are doing

any way you can change various parameters - BUT if you just change the sale price - it will show - the New volume needed for a 200 monthly margin - cell highlight BLUE
then it works out the time to sell out -
some ROI values based on various financial websites - and then what I think you want

BUT as i say , i cant quite understand how you would make the return on investment in total as you only have 1000 units and the costs are the same
$3 to buy and $5 costs internal - fixed / variable - dont know
so for every unit - it cost $8 to sell

anyway

ROI-ETAF.xlsx
	A	B	C	D	E	F	G
48		CURRENT			NEW
49
50		Current selling price	10		New Sales Price	9.6	<-- Enter price here
51		Cost of sale	3			3
52		cost of sale	8		Total cost of sale + fixed costs	8
53		margin value	2		Margin Value	1.6
54		Margin %	20.0%		Margin %	16.7%
55
56
57		current monthly volume	100		new vol needed to maintain Margin	125
58		monthly margin Achived	200		monthly margin Required	200
59
60		Total Quantity Available	1000		Total Quantity Available	1000
61		Time to Sell out Mths	10		Time to Sell out Mths	8
62
63		ROI = Net income / Cost of investment	25%			20%
64
65		This is what I think you are doing
66		(Price * sales vol)-(profit * sales Vol)/(Price * Sales)	80.0%			83.3%
67		Above as a poportion of months in year	66.7%			55.6%
68
69
70			1year			2year
71		ROI = (Ending value / Starting value) ^ (1 / Number of years) -1.	25%			20%
Sheet1

Cell Formulas
Range	Formula
C53,F53	C53	=C50-C52
C54,F61,C61,F54	C54	=C53/C50
F57	F57	=F58/F53
C58	C58	=C53*C57
C63,F63	C63	=(C53*C60)/(C52*C60)
C66,F66	C66	=((C50*C60)-(C53*C60))/(C50*C60)
C67,F67	C67	=C66*(C61/12)
C71,F71	C71	=((C50*C60)/(C52*C60))^(1/(1))-1

I have added to dropbox , but will only be onsite for a week or 2 - so if you need a copy , then you need to save locally

ROI-ETAF.xlsx

Shared with Dropbox

www.dropbox.com

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[Logit]

I find this thread very interesting and would like to add to etaf's initial question. Lowering the price of an item usually means you will sell MORE (a larger quantity of the item) in a shorter time period. If you purchase a larger quantity of the item to sell at the lower price, you will actually realize a higher profit margin. Finding that "sweet spot" of the lowest price that derives the highest profit is the formula to seek.

But then your original question may only be homework ?