Free Practice Workbook
You can use Excel´s Goal Seek feature (under What If Analysis) to find out what Sales you need to achieve in Q4 in order to meet your Net Profit goal of $200,000.
Download excel workbookGoal-Seek-Budget-Values.xlsx
STEP 1: Select the cell that you want to achieve your goal of $200,000 which is the Total Net Profit in cell F7, which is a Sum formula (Important: This cell must be a formula for the Goal Seek to work)
STEP 2: Go to Data > What If Analysis > Goal Seek
STEP 3: SET CELL: This is the cell that contains the goal we want to achieve, F7, and is selected automatically
STEP 4: TO VALUE: Type the goal value that you want to achieve. In our example, it will be 200,000
STEP 5: BY CHANGING CELL: Enter the reference for the cell that contains the input value that you want to adjust. In our example it is the Q4 Sales forecast in cell E5
STEP 6: Press OK and Goal Seek will run and produce a result. Press OK to keep the results or Cancel to discard
With Goal Seek we need to achieve Q4 Sales of $437,730 in order to achieve our Net Profit goal of $200,000. Over to the Sales team then to make it happen!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
