For Budgeting Geeks!... A Simple Analysis Tool

Problem: As a budgeting department, have built your company's P&L, meticulously calculating each and every figure, starting with the target end result then backwards to target sales, production quantities, and costs. But what if, after all this hard work, your CEO becomes hungry for more generous profits? What if the regional office has subsequently posed some new restrictions, limiting your, say, administrative expenses to only 5% of sales? How would you be able to deal with all these different scenarios after you have built your perfectly-studied P&L?


Solution: Here comes the role of Goal Seek. This tool is part of the What-If Analysis Data Tools on MS Excel, and what it does is simply perform trial and error (but a bit faster than you by a few trillion times!) until it reaches a target value (set by the user) by changing the values in another dependent cell.

Sounds gibberish? Let's see how...

Assume that you have the following simple budget for P&L:

	A	B	C	D	E
1		$		QTY	Unit Price/Cost
2	Sales	13,875,825		185,011	75
3	Cost of sales	(12,950,770)			(70)
4	Depreciation	(234,054)
5	Admin expenses	(650,432)
6
7	Net income	40,569

The P&L has been calculated based on certain target sales quantity at a certain unit price and unit cost, then depreciation and admin. expenses are estimated using their relative drivers and entered manually as fixed figures, after which net income is calculated.
So, you receive the hungry request from your CEO to target a net income of 100,000. 
To do so, you need to determine how many units you need to sell in order to achieve the target profit. You will have to guess, is it 200,000 units? 201,000? or 220,000? What if this tedious trial and error is done automatically? Welcome to Goal Seek!
Go to Data tab --> Data Tools --> What-If Analysis --> Goal Seek
The following dialogue box should appear:


a. Set cell: Select the cell that you want to change to a specific desired value, in our case net income, or cell B7.
b. To value: What is the value that you are targeting? Enter 100,000.
c: By changing cell: Which factor do you need to change to arrive at the target value? Select the quantity, or cell D2. Press OK.
Easy isn't it? But make sure of the following whenever using Goal Seek:
1. The target cell (Set cell) should always be a formula, and that formula should be dependent on the cell you want to change.
2. The cell you want to change (By changing cell) should always contain a value.
In no time, the P&L will have changed to give you the targeted end result as follows:

	A	B	C	D	E
1		$		QTY	Unit Price/Cost
2	Sales	14,767,290		196,897	75
3	Cost of sales	(13,782,804)			(70)
4	Depreciation	(234,054)
5	Admin expenses	(650,432)
6
7	Net income	100,000

Voila! 

If you liked this exercise, please do not hesitate to share!