Macro to use goal seek with criteria

[SRpakse]

Hi All,

I am after a macro that will help me use the goal seek function to determine Gross Margin % and Net Profit % for a very basic profit and loss summary. The GM% and NP% needs to change as they are changed in other cell references (I.e there will be input cells to change these numbers).Once the criteria for GM% and NP% are set the goal seek function should change the sales figure based on these percentages. I would not mind having two separate scenarios as I don't want to change to many variable. So maybe one macro for GM% and one Macro for NP%?.Sample shown below.

Sales $50

Cogs $20

GM $30

GM% 60%

Exp $10

NP $20

NP% 40%

The 60% gross margin and 40% gross margin needs to change with the values in the input cells and the sales number needs to change too. For Example if a GM% of 80% is required then the macro would change the sales figure to $99.58092 and the net profit margin dollar value to $69.58092 and net profit margin to 70%

Is this even possible using a macro?

Thanks
SR

 

[jorismoerings]

Hi,

Not a full proof solution but just some pieces to get you started. Take a look at this;

Book1
	A	B	C	D	E
1	Sales	33,32886		required GM	10%
2				required NP	10%
3	Cogs	20
4
5	GM	13,32886
6
7	GM%	40%
8
9	Exp	10
10
11	NP	3,328855
12
13	NP%	10%
Sheet1

Cell Formulas
Range	Formula
B5	=B1-B3
B7	=B5/B1
B11	=B5-B9
B13	=B11/B1

Sub Change_GM_NP()
'
Dim Response As Integer

    Response = MsgBox(prompt:="Wish to change GM", Buttons:=vbYesNo)
    If Response = vbYes Then
         Req_GM = Range("E1").Value
         Range("B7").GoalSeek Goal:=Req_GM, ChangingCell:=Range("B1")
    End If
    Response = MsgBox(prompt:="Wish to change NP", Buttons:=vbYesNo)
    If Response = vbYes Then
         Req_NP = Range("E2").Value
         Range("B13").GoalSeek Goal:=Req_NP, ChangingCell:=Range("B1")
    End If
End Sub