How to Find Total revenue and Profit using circular reference with only profit margin

[burchconner]

I have a Profit Margin in mind and want that to be the only number that can be imputed into that equation. My example is Revenue =586,120 Cost of Goods= 31% or .31*586,120 Bakery Labor= 22% or .22*586,120 The Delivery labor can be changed but its 86,881, Total Vehicle Cost can be changed but its 43,008, Operating Expense =20% or .22*586,120, Profit= Revenue (586,120)- Fixed Cost (.73*586,120) - Delivery Labor (86,8810) - Vehicle Cost (43,008), The profit = 28,364, Profit margin= Profit (28364) /Revenue (586,120) 4.84%, I was wondering if I can use the circular reference or another way to do this, to change the Profit Margin Percent to lets say 7% and have it tell me how much Revenue I need to produce. so far I have this equation in the revenue cell, =0.27-((VC+DL)*Profit Margin)/Profit and I don't know if that's even correct

 

[J.Ty.]

Make the following spreadsheet

Book1
	A	B	C	D	E	F	G	H
1	Rev	CoG	Bak Lab	Del Lab	Tot Veh Cost	Op Exp	Profit	Profit margin
2	586,12	181,6972	128,9464	86,881	43,008	117,224	28,3634	4,83917969
Sheet1

Cell Formulas
Range	Formula
B2	=31%*A2
C2	=22%*A2
D2	=86.881
E2	=43.008
F2	=20%*A2
G2	=A2-(B2+C2+F2)-D2-E2
H2	=G2/A2%

Then in the tab Data there should be menu Solver. If it is not, it can be turned on in Excel options: File -> Options -> Add-ins
Then choose "Solver add-in" and activate it.

Once you have Solver ready, click it and you will get a window to specify the options.
Set:

Set Objective to $H$2
To Value Of to 7
By Changing Variable Cells to $A$2

Then click "Solve" and it will do for you what you need.

J.Ty.

 

[burchconner]

[TABLE="width: 871"]
<tbody>[TR]
[TD]Revenue
[/TD]
[TD]Cost of Goods
[/TD]
[TD]Bakery Labor
[/TD]
[TD]Delivery Labor
[/TD]
[TD]Total Vehicle Cost
[/TD]
[TD]Operating Expense
[/TD]
[TD]Profit
[/TD]
[TD]Profit Margin
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$416,895
[/TD]
[TD="align: right"]$129,238
[/TD]
[TD="align: right"]$91,717
[/TD]
[TD="align: right"]$79,328
[/TD]
[TD="align: right"]$33,225
[/TD]
[TD="align: right"]$83,379
[/TD]
[TD="align: right"]$9
[/TD]
[TD="align: right"]0%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Steps to find deserved Revenue
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]1) Enter Total Vehicle Cost
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]2) Enter Delivery Labor
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]3) Click on Data tab and go to What-If Analysis
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]4)Click Goal Seek
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]5) In Set Cell, Click H2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]6) In Value, Set what profit margin you want to achieve (ex: 0.07)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]7) By Changing Cell, click A2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]