Exploring the Model Base

Quick reference

• 00:04 Time then to start looking at an Excel model that we're going to prepare.
• 00:08 We're into it now, this is the application.
• 00:12 We've done all the prep, we want to now look at a model and build one up.
• 00:16 And the model we're going to look at, it's going to be pretty straight forward.
• 00:20 The idea is to look at the concepts, not necessarily the calculations.
• 00:24 Think about how would I build this,
• 00:25 because every model that we ever build will be different.
• 00:28 And more complex than the one that I'm gonna show here.
• 00:31 But the ideas, the rudiments, the basics of it, will all remain the same.
• 00:35 So concentrate on the ideas, not the calculations.
• 00:39 Don't get hung up on, why have I put a dollar sign in there?
• 00:42 Or, why did you use index when I might have used offset?
• 00:45 Now, do you understand why I'm doing what I'm doing?
• 00:48 That's the point.
• 00:50 And the key principles to remember, we're going to build it consistently,
• 00:55 robustly, flexibly, and transparently.
• 00:58 We're going to have thought about the model layout.
• 01:01 We'll put checks in, we'll use the key functions.
• 01:04 And we'll employ the financial statement ideology that we've just been talking
• 01:08 about before.
• 01:09 Let's get looking at excel.
• 01:10 Lets take a look at the template
• 01:14 we're going to use through out this modelling exercise.
• 01:16 You'll notice it's a little bit more sophisticated than
• 01:19 the template will develop in the basis course.
• 01:22 But it's not that much more sophisticated it's been pretty dope.
• 01:26 And a couple of little things, added up a few tweaks which we'll explain as we go.
• 01:29 Including a cover sheet.
• 01:31 Now look at this cover sheet.
• 01:32 It's got a title.
• 01:33 It's got the project name.
• 01:34 A little logo.
• 01:35 It even says who built it.
• 01:37 Goodness you don't want to actually take ownership for a model do you?
• 01:40 And even a hyperlink to email the person to say hey,
• 01:44 what does this actual model do?
• 01:46 You can actually put a notes and other things on here.
• 01:49 But look, it's a front cover.
• 01:50 You see, when you open up Word or when you open up PowerPoint
• 01:55 you're always on page one of the document, right at the top.
• 01:59 Whereas Excel, it's the poor cousin.
• 02:01 It's the proverbial dog with no legs.
• 02:03 Where do you find it?
• 02:04 Where ever you left it.
• 02:05 And that's not good enough.
• 02:07 It should look professional.
• 02:08 We're not looking to keep our armature status for the Olympics.
• 02:11 So what you should actually be doing is always resetting the sheet.
• 02:15 Remember what we did in part one of the course?
• 02:17 Ctrl+Home and checking for any work in progress down column A.
• 02:21 What we should be doing is resetting it and all supporting it on the front page.
• 02:25 So when somebody else opens it up, it looks like it hes been reset and
• 02:29 it's all good to go.
• 02:30 And that's what we've got.
• 02:32 Now, then is a second thing here.
• 02:34 In cell A3 do you see there is a hyperlink that says navigate to.
• 02:37 If I click on this, it brings us up to the table of contents.
• 02:42 Now I've discussed hyperlinks and table of contents before.
• 02:45 And in the preparation course, we actually looked at hyperlinks manually.
• 02:49 Now what I've done here is I've cheated and I've put in a macro.
• 02:53 Now this course is not about macros so
• 02:55 I don't plan to go into this in great detail.
• 02:58 But essentially all you have to do is
• 03:01 right click on the navigator tab of your actual workbook.
• 03:04 Go to view code and it brings up this macro that I've put in here.
• 03:11 Now anything in green is a comment.
• 03:13 I'm not planning to go through Visual Basic for Applications.
• 03:17 But if you want to peruse this for yourself.
• 03:19 You see, that what it actually does,
• 03:21 it creates a simple little table of contents, which is nice.
• 03:26 If i take sheets here at the timing sheet, and I actually move it so
• 03:30 that it's here straight after the navigator sheet, then back here.
• 03:35 Its now cover, time and star guide.
• 03:37 While if I go back here and actually put it back to where it was.
• 03:44 It's moved back again.
• 03:46 And if I insert new sheets, then we put in with hyperlinks installed already.
• 03:50 That's what you want to do.
• 03:51 You want to cut out all the monotonous stuff using macros, so
• 03:54 you can concentrate on the real work.
• 03:56 And that's what this is doing.
• 03:58 We have already created Some styles over here.
• 04:02 Remember, on the Home tab, we can go to Cell Styles.
• 04:05 And we can actually create them in here the custom ones.
• 04:07 What I've actually done is to created a sheets here that highlights all of these.
• 04:12 Now, if you were to go to Cell Styles and right-click and
• 04:15 go Modify like we did in the preparation course.
• 04:18 You change the actual colors to your own company's finding.
• 04:22 It will automatically update here because these have all been styled.
• 04:28 We've got a Model Parameter sheet.
• 04:30 We have a formula that actually gives the calculation for
• 04:33 working out what the final name is.
• 04:35 We have talked about that before and the actual client name, project name,
• 04:39 whatever you want to call it, here.
• 04:41 And then technical assumptions,
• 04:43 these are numbers that get put into a model that don't change.
• 04:46 So we have two types of number in an Excel workbook.
• 04:49 We have what's called a constant.
• 04:51 They're things that don't change like months and year, 12, hours and day, 24.
• 04:55 And then we have other things Which all things that could change.
• 05:00 Number of working days per week.
• 05:01 Could be five, six, seven for example.
• 05:04 They would be variables and imports.
• 05:06 These constants they're not yellow cells, they're not assumption cells and
• 05:12 each one that you may notice has a range name days in year.
• 05:19 This one here, months in month, months in quarter and so on.
• 05:24 Another things that you might need, this is an important one.
• 05:27 The currency, whichever model you use,
• 05:31 you'll probably have a different currency from time to time.
• 05:34 You might not work in U.S. dollars at all.
• 05:36 You just change it here and it will flow through the model.
• 05:39 And we've got financial statement abbreviations as well for
• 05:41 income statement, balance sheet, and cash flow.
• 05:45 The timing sheet set-up, we need to put our dates and
• 05:48 if we're going to do some forecasting we're going to look at that next time.
• 05:51 We have error checks ready to go so that we have a hyperlink created.
• 05:55 When we click on this, it takes us to the error check sheet so
• 05:58 we can build in our checks here.
• 06:00 And even a change log where we can keep track of the changes that we made.
• 06:05 That's the basics.
• 06:07 I'm going to talk next time about getting the timing sheet worked out.
• 06:10 So have a good look round this model first and
• 06:13 when you're happy, let's move on to the next session.

Lesson notes are only available for subscribers.