Locked lesson.
About this lesson
Explanation of what should be considered as OPEX.
Exercise files
There are no related exercise files for this lesson.
Quick reference
OPEX Part 1
Understand Operating Expenditures.
When to use
When constructing a basic Financial Model.
Instructions
There are only four ways to enter data into a financial model. The actual method may incorporate combinations of the following:
- Amounts: Data is entered in as numerical (absolute) values. It may be in units, thousands, millions, GWh and so on, but ultimately it is a number. This is an ideal entry method where data is copied from elsewhere or may eventually be linked to another model (say).
- Percentages: Data is typed in as a percentage or ratio of another value, be it input or calculated. This is often the approach employed for variable costs, for example.
- Amount and growth rates: Data is entered in two forms, an amount in at least the first period and then a percentage thereafter. This is often the method used for sky blue forecasting and sometimes leads to what the modelling connoisseur may describe as “hockey stick projections”.
- Combination: Usually perceived as a more sophisticated version of the amount and growth rates approach, this method combines two or more input methodologies. The combination may be selected by use of a switch (manual input) or a trigger (calculation). This is often used for reforecasting or replacing forecast with actuals, etc. IF and CHOOSE are functions often associated with this methodology.
When looking at OPEX we will focus on the Combination method which looks at growth rates and amounts.
Login to download
- 00:04 Congratulations, we finally made it past cost of goods sold, so we can move on.
- 00:10 I wonder what's next.
- 00:11 Back to that slide,
- 00:13 a model is best thought of broken down into its constituent parts.
- 00:20 So, we go from A to B in as straight a line as in arrows as we can.
- 00:25 We build in the checks, first of all.
- 00:27 Then we build in the operational segment, which is where we are presently.
- 00:30 We'll factor in the working capital adjustment as we go along,
- 00:33 and any adjustment system required for assets, too.
- 00:37 That's all going to need financing.
- 00:39 Everything gets taxed and reported through the financial statements,
- 00:43 before we put through other key outputs, so where are we?
- 00:47 Well, we finished revenue and cost to consult.
- 00:50 So, in our little table, we've completed the revenue and cost of operational.
- 00:54 We've always got a bash at both working capital adjustments, debtors, and
- 00:58 creditors.
- 00:59 So, we need to move on now.
- 01:01 Next up is Operating Expenditure.
- 01:04 Let's go take another look at our Excel file.
- 01:08 Before I actually go through the model,
- 01:10 let's just return to the four types of inputs.
- 01:13 We've got the amounts method here, where we type an amount in.
- 01:17 We've got the percentages, where we take the percentages of a given number.
- 01:21 And we've got the amount in growth rates, which were used for
- 01:24 calculating things like revenue earlier.
- 01:26 The method I want to look at now is the combinations method, and in particular,
- 01:30 one that's often colloquially known as the amounts and
- 01:33 growth rates method, which is this one down here.
- 01:37 What I've got is an amount for the first few periods, and
- 01:40 then it's a percentage there after.
- 01:42 This is very common when you actually want to add in new data as you get it.
- 01:47 As you get the actuals in, you put those in and
- 01:49 then grow the actuals by the forecast percentages instead.
- 01:53 So, for instance, in that period two, I'll just have an amount in period one,
- 01:57 and then I'll go up by 4% then 5 then 6.
- 02:02 But in period three, I've now got better data, it's 200.
- 02:04 And I've gone up by 5, 6, 7, 8 This is the method we're going to employ
- 02:09 when we're looking at this opex example now.
- 02:12 Let's go take a look.
- 02:15 Let's supply all this then to my model that continues to grow.
- 02:19 We're at the Opex Start now.
- 02:20 So let's look at the Navigator sheet and see what I've actually added in now.
- 02:25 I've got Assumptions, so let's go here and add some new stuff in rows 51 to 58.
- 02:30 Opex and cash payments.
- 02:33 Now note what I've written in row 52.
- 02:36 All expenses are assumed to be paid as they are incurred.
- 02:40 What does that mean?
- 02:41 It means the PNL numbers are the same as the cash flow numbers.
- 02:44 There's no timing differences.
- 02:46 And if there's no timing differences,
- 02:48 that means there's no need to have any working capital adjustments.
- 02:51 What does that mean to the model?
- 02:53 Well, But more on that next time.
- 02:55 For now lets concentrate on stuff going on in rows 56 to 58.
- 02:59 The plan is I'll have a drop down box here, which will have a year in it as
- 03:03 a result either these cells or these cells will be grayed out.
- 03:07 Simply the choose an amount, or a growth rate.
- 03:09 But it can be for more than one year, so
- 03:11 this what's colloquially known as the amounts and growth rates method, which
- 03:15 is based on the combinations approach I showed just a moment or two ago.
- 03:19 Now to do that, I'm going to require data validation in here, so
- 03:22 I'll go Data Validation > choose list I pick a range, but
- 03:26 I don't want it to go from this sheet.
- 03:27 I want it to all be on one sheet where.
- 03:30 all my source data are lists.
- 03:32 So we can find it very easily.
- 03:34 Now in order to do that, I actually need to get all those lists range names.
- 03:38 So it works with all versions of Excel, otherwise the data foundation just
- 03:42 won't work in certain versions of Excel Therefore, let's go create a new sheet,
- 03:46 we'll call it the Lookup sheet where we have our lookup data, and
- 03:49 give it a range name.
- 03:53 Well, I've already created a Lookup sheet here, so here it is.
- 03:58 And to lookup data, sort of semi-prepared.
- 04:01 I've got four cells colored blue.
- 04:03 Now if you actually look at my styles This means that parameters,
- 04:08 let things that would be brought out the model, and the last cell will be an input.
- 04:13 Guess what it might be?
- 04:13 Well Iet me give you clue by typing in what should be in the input cell.
- 04:22 Actuals throughout.
- 04:24 So what's going to happens is, first of all I want you to refer to the years,
- 04:28 these four years here.
- 04:30 Because there's no choice to be made for the first year.
- 04:32 It's got to be an actual, but then after that I can choose either an actual number
- 04:36 All eight percentage and that's what we'd like to get to choose.
- 04:40 So I want pick up the last four years from here, but that's transposing.
- 04:44 How do I do that?
- 04:45 Well if you remember from the preparation tools, we use the offset function.
- 04:49 So that equals offset up from brackets click on
- 04:54 this cell to the left unless you want to actually transpose the time so January 7,
- 04:58 make- Absolute, I'm gonna put comma comma in, close brackets and press Enter.
- 05:03 So that if I'm going to have any references on this sheet,
- 05:06 it is just for the cell reference, not the sheet name as well.
- 05:09 Now, I don't want it to go down any rows, but I do want it to go across columns.
- 05:13 So I'm going to put the cursor back in after the second comma, and
- 05:16 use the Rows Function to count the number of rows in the range.
- 05:21 Cell C 11 here.
- 05:23 And press the full stop.
- 05:25 So go C11 to C11, close bracket.
- 05:27 And for the first C11 I'm gonna press F4 button once to make it all absolute.
- 05:31 And for the second C11 I'm gonna press it now three times.
- 05:35 So that I only keep the column absolute.
- 05:37 That has the effect that when I copy this down, it goes C11 to C11 in the first one,
- 05:43 C11 to C12 in the second, C11 to C13 in the third, and C11 to C14 in the fourth.
- 05:47 So it goes across one column, two columns, three columns, four columns.
- 05:51 Ie, it picks up the last four periods, what we want.
- 05:55 Now I'm going to give this a range name, as I explained.
- 05:58 Let's call it, I don't know, LU_Future_Years, the LU standing for
- 06:03 lookup.
- 06:06 So I'll just type that in.
- 06:08 Make sure you press enter.
- 06:09 Starts there. Always like to see it's working happily.
- 06:12 And then I'll name it here so people know what this is called.
- 06:16 LU Future Years.
- 06:20 And I've even formatted it with a style for
- 06:23 it called range name description, done.
- 06:28 Now I've done that.
- 06:29 I'm gonna go back to my assumptions worksheet and I'm gonna put the date
- 06:33 of validation into cell I56 which will create the drop down list.
- 06:37 So on the data tap, I'll go to Data Validation, List,
- 06:42 and rather than type the range name in I'll push the F3.
- 06:46 Function key which brings up place name.
- 06:48 This is why I have the prefix of LU and you'll see it's easy to find them.
- 06:53 I click OK. Voila, I have my drop down box.
- 06:58 And look, it works already.
- 07:00 Isn't that impressive?
- 07:02 And that's because I have put conditional formatting in here.
- 07:05 If I go to the home tab Additional formatting and go to Manage Rules,
- 07:09 you'll see that the in here, I've made it all white on gray when this happens,
- 07:13 is when I56, the growth rate use form date Is less then or equal to the end date.
- 07:20 So let me think about that.
- 07:22 When this date is less then that.
- 07:25 That means that we should have been using the growth rate in that period.
- 07:28 So it should be greying these out.
- 07:30 A conditional format has the opposite way around for this one.
- 07:33 So I've got my numbers highlighted which I'm going to be using for my model.
- 07:36 Makes it cool, makes it easy for the end-user to understand.
- 07:39 And that means we can make it easier when we do the calculations next time out.
Lesson notes are only available for subscribers.