Our Latest News

Recently we were looking at the sorts of things that you want kept straight in an Excel spreadsheet, right from the very start (when we were reviewing the smallest Excel financial model in the world).  There we were thinking about things that might seem basic (like...

Maybe like you, I’m regularly asked to look at others’ Excel forecasting work.  Often there’s no large time allowance to pore all over the spreadsheet.  So, in a short amount of time, I’ve realised there’s some things ahead of others I end up looking at.  My aim is to...

The other day, while searching for solutions for a big model I’ve been passed, I came across this piece of Excel technical documentation.  I hadn’t come across it before.  There’s lots in there, some of it relating to the big spreadsheet I’ve been working on. Here’s a...

Today have in front of us the smallest piece of Excel modelling in the world. It only consists of two tabs.The first tab in our Excel spreadsheet has got some assumptions loaded up on it (it only goes down 13 rows).The second tab makes use of those assumptions to...

Recently we’ve seen a few experiments where people have seen how far they can get building a whole Excel model out of ‘dynamic arrays’.  It’s a fun experiment but we're left wondering whether it’s worth the effort.  That’s for the reasons outlined at the bottom of...

Here’s a quick guide to running scenarios in Excel, which collects up recent posts that have touched on this topic. This is your essential guide to making sure your scenarios are running in the best way possible.Gather up the inputs you want to use to run scenarios in...

One of the things you get to think about in Excel modelling is the exact layout and design for your model.  Ideally that’s all done in advance, before you’ve got a big beast of a spreadsheet to manipulate. Here are a couple of helpful design elements (1) and (2) that...

Let’s imagine you’ve got scenario sets nicely laid out in your model and switching through absolutely fine.Now  you’ve got the challenge of how to display the results of scenarios 1, 2, and 3 side by side – when the model only runs one scenario at a time (with the...

Excel has a newer class of functions called ‘dynamic arrays’.  It helps to think of them as acting on a range of cells and also outputting a range of cells. Superficially there’s a potential attraction.  You can create a formula in one cell that will populate the...

Recently we’ve been looking at all the Excel functions that can help you pick a piece of data out of a list (we’re big fans of Index) - for example for scenarios you’re wanting to run through your financial model.Perhaps the model inputs you want to flex are set out...

Recently we were looking at how you could make use of Excel’s logic test as part of a quest to simplify model calculations, and that took us to a list of solutions that could be used to select a piece of data from a row, column or table.Many pro modellers use Index. ...

Recently we were looking at how to use Excel’s Max or Min as a more compact alternative to the regular If function. Excel gives us so many choices!  We only need one that works but, if we can find a shorter solution - that’s generally something to embrace and...

Excel Max and Min are really simple to use.  You point Excel to a set of numbers.  Max will pick out the largest.  Min will select the smallest. Often you’ll see people using Max and Min as an alternative to Excel’s If function.  If you haven’t seen that done before...

With Excel sometimes (in fact, often) it seems we are provided with an embarrassment of riches.  There’s often more than one way to achieve your aim in Excel.  That can make it a bit confusing – it’s hard to know which solution is best for the challenge at hand.As a...

Cross sheet linking is where you create a formula on one tab that’s making use of cell references on far away tabs.It can feel like the most natural thing in the world to build up a formula that’s making use of multiple references on far away sheets.  But if you take...

Adjusting your model’s balance sheet need not be difficult, if (and it’s quite a big if) you’ve invested in a couple of smart features in your model structure.Here’s some detail on a structure where the model composes its financial statements from a ‘sandwich’ of...

Imagine you have source data in an Excel financial model.  But then you suddenly realise that it would make sense to have each of those numbers divided by 1,000 (on the basis that the extra detail is not needed and you'd like to 'clean up' your workings).Sometimes...

There are plenty of line items in an Excel financial model where what’s happening on the profit and loss doesn’t quite match cash flow.  That’s what the balance sheet is there for, to remind you of what the difference is (either cash flow is lagging the profit and...

If you want to analyse your customer (or supplier) base, Excel’s SumIfs function is going to become a firm friend.  But there are a few little tricks that you’ll want to play along the way.  And you may find that some of Excel’s newer functions (Unique and Sortby)...

Keyboard shortcuts are great.  You need a few to help you through.  If you can automate some of what you do in Excel so that it just flows out of your fingertips, you will find yourself thinking ahead (maybe to the next line you’re going to put in place).  Everything...

Sometimes, quite regularly in fact, you might want to shuffle numbers to the right or left in Excel. Imagine a contract that's getting delayed, with all the values pushed out to the right based on a number of months of delay.Offset might be your go-to function for...

Most every Excel modelling project has elements that are a bit unique for you. Those pieces are going to take more time and thought. The solutions are not going to come so easily. After a while you start to notice those model chunks and enjoy them for what they are -...

If you’re a strongly profitable business that has been, say, happily operating spreadsheet budgets (mapping out expected revenue and costs for the next year) you’re going to need a reason for taking your forecasting up to the next level. Here are few thoughts about...

Right at the end of a model build you might suddenly decide that you'd like to run some sets of assumptions (say base, high and low cases) through your spreadsheet.Here's a small mock-up of a structure that could help you do that. You can see some imaginary...

Recently we wrote about the great benefit of control accounts where a balance is changing across a model - setting out calculations row by row, totalling up the result for the period and carrying it forward so that the closing balance for the last period becomes the...

One of the toughest things you can try and tackle as a financial modeller is picking up someone else's work (when it's of any significant scale) and adjusting it. The structures at work may not be immediately clear. It's nice to think that the components are going to...

One of the things that can quickly make Excel models impenetrable to a new user (along with things like spaghetti and unusual non-standard financial modelling practices) is long and embedded formulas.This one can be such an easy fix!  All you have to do is break any...

In our last blog post we looked at how you could structure models to make it quicker and easier to trace through them, mostly just by using the "Ctrl [" keyboard shortcut. But what if the model's not Nirvana?  Not all real-life models are constructed as carefully as...

Here are a couple of quick shortcuts that can speed up your work if you're tracing through a big model.  Sometimes you might find yourself constantly wanting to use these over large models - thankful that they exist!"Ctrl [" (control combined with the left square...