In this blog I will present goal seek functionality and how it can be used in cost profit volume analysis.

First, let’s check goal seek with a simple example.

B2 plus C2 equals to D2. If you want D2 to be four, which value should be in C2 ?

Choose data – what if analysis – goal seek.

D2 should be four by changing the value in C2.

Excel counted correctly.

This is basic cost volume profit analysis. Sales is counted by sales volume multiplied by sales price. Variable costs are sales volume multiplied by variable costs. Margin is sales in money minus variable costs. Result is margin minus fixed costs. The result is slightly in negative territory. Margin percentage is margin divided by sales. Result percentage is result divided by sales.

How much should price per unit be that the result would be to reach the zero result (break-even) when all the other parameters are remaining as they are ?

The result is in F6 cell, that should be zero by changing the price in B3.

The result.

Let’s take bit more complex case.

What the sales volume should be that the result percentage is +5 % ? When the sales volume increases, also the variable costs are increasing linearly.

The values are with several decimals.

You can create other examples.

I have written blog about OFFSET earlier. Recently, I found one good application for OFFSET.

When you load data from system to Excel, the data needs to be rearranged.

Five columns and many rows. This is the way how the data looks in the system.

When you download the data, the data is in one column, between the rows in the system there are two empty cells.

The data should be modified as it is in the system, five columns and many rows. You can copy cells (A2:A6) and paste with transpose to have five cells horizontally. This takes time if you have 10000 values.

This can be tackled with OFFSET function.

Let’s have a short summary about OFFSET.

OFFSET consists of five arguments.

1. Reference. What is the starting point in the top left hand corner of the area.
2. Row. How many steps do you take from reference downwards, if the value is positive.
3. Col. How many steps do you take from reference to right, if the value is positive.
4. Height. How high is the selected area.
5. Width. How wide is the selected area.

Here is an example. The start cell is B3. Then we go two steps downwards, that is B5. From B5 we go two steps to right. The area is just one cell ( one times one). That is five.

The function in B10 is =OFFSET(B3;C10;D10;E10;F10) .

The OFFSET can be framed with another function like SUM.

The function in B10 is =SUM(OFFSET(B3;C10;D10;E10;F10)).

The area is two steps down from B3, that is again B5. Then we go three step to right to E5. Then area is two times two, from E5, (E5:F6). SUM(E5:F6) equals to 28.

Let’s move from theory to practice.

To use OFFSET function, we define A2 as the reference, it could also be A1, but I just picked up A2.

The second argument in OFFSET, row, is varying argument as the all the values are vertically.

The first function to extract the first value is =OFFSET($A$2;0;0;1;1) .

When the first value is taken, we don’t have to go anywhere from A2. Both row and col are zeroes. The area is just one cell, the height and width are one.

To extract the second value, all the arguments are same except row which bust one higher than previous time =OFFSET($A$2;0;0;1;1) . As the data is in one column, we don’t have to adjust col. Each time we are looking for one value and one cell, therefore height and width are always ones.

Between 5^th and 6^th values are two empty rows. After the 5^th, the row value must equal to previous value plus three.

In cells (I2:M4) I have inserted the row values. J2 is simply I2+1. When row the changed I3 value is M2+3.

The row values are taken into the function from (I2:M4) area. Other argument values are static.

Data here is pretty small, but same solution works for 10000 cells, and it does not take too much longer time than for three rows.