Pricing analyst tools: Goal Seek Excel function

This blog post from the Pricing Analyst UK blog series about Microsoft Excel functions is introducing the Goal Seek function. It is one of the most effective and at the same time very simple Excel tools that pricing analyst is daily using. As a pricing analyst, you will be creating pricing calculations in spreadsheets that are known as pricing models. Pricing models are data models, i.e. calculations that take inputs, perform some mathematical operations and then output a result.

Factors such as revenue, fixed costs (overheads), variable costs, semi-variable costs, units of product, discount percentage, profit margin, price etc. can be either inputs or outputs, depending on what you want to model. For example, if you want to know what your profit contribution will be for a given set of costs, sales volume and price, then the inputs will be costs, sales volume and price and the output will be profit contribution. Alternatively, if you know your costs, sales volume and the profit contribution you want, you can work out the price that you would have to charge to achieve this. So the inputs are costs, sales volume and profit contribution, with price being the output. The data models are simply sets of algebraic equations where you seek the required variable based on the values of the other variables which you do know.

STOP! It is all Greek to me, you might say. No worries, I will show you everything in the short video tutorial later.

 

Goal Seek Excel

Goal Seek for pricing analysts

 


This is the time when the Goal Seek Excel function comes handy. It can be used to seek any output you want without having to go into the model and change the algebraic structure of the model. It is basically an equation solver. So let’s do the basic math…

1) Your pricing model is set up as:

(a) Revenue = Units Sold x Price Per Unit

(b) Total Costs = (Units Sold x Cost Per Unit) + Fixed Costs

(c) Profit Contribution = Revenue – Total Costs,

i.e. Contribution margin % = (Revenue-Total Costs) / Revenue

2) You link these equations in your data model by entering the formulas into cells. (plus, minus, multiple, divide… yes, just that, no advanced formulas needed.)

3) Goal Seek Excel function will find the right price for you (clever, isn’t it?) based on whatever costs, profit contribution and/or units sold you enter into the model. You could also, for example, enter price, profit contribution, cost per unit and fixed costs as inputs into the model and use Goal Seek to output the number of units you would need to sell in order to achieve the profit that you entered.

NOW ENOUGH! Go ahead and watch my screen capture video where I walk you through all the steps described above and show how Goal Seek Excel function works. Feel free to post any relevant comments.

PS: Before you click the link: this video is not entertaining and I am not a professional (and not a native English) speaker. But I wanted to show you the Goal Seek in practice anyway. If that’s alright with you and you want to learn how to use Goal Seek, watch the 3.5 mins video. Don’t say I haven’t warned you it would be boring…! 🙂

Goal Seek Excel tutorial

Share the Post or Bookmark:
  • Facebook
  • Twitter
  • LinkedIn
  • email
  • Add to favorites
  • del.icio.us
  • Digg
  • PDF
  • RSS

Leave a Reply

Your email address will not be published. Required fields are marked *

This Blog will give regular Commentators DoFollow Status. Implemented from IT Blögg

Network for pricing analysts in UK