Columns and Rows

It is possible to hide a column or row of data to stop it printing out, or to stop other people from viewing it.

You can hide multiple columns or rows at the same time. Just select all the columns or rows at the same time and follow the steps outlined above.

Sheets

Hiding sheets is also very useful. If you have information on a model you do not want users to see at all (e.g., salary information on budget models), you can hide the sheet with sensitive information, and then protect the workbook. The hidden sheets disappear from view, but links are retained as the sheet has not been deleted from the workbook.

Errors Caused by Hiding

Hidden sheets, rows, and columns may hide complexity from the user and make the model less confusing, but many users don't like using what they perceive to be a black box. It's up to you, the model developer, to decide how much power you will give to model users. If you hide everything and then protect the model so that they can't see how it has been calculated, this makes the model easy to use, but it does not inspire confidence in users. It often depends on who is using the model. If a model is for other modellers to use, they probably won't appreciate being locked out of certain sections of the model, as they'd like to check it for themselves to ensure that it is working properly. If users have limited Excel use, then hiding with protection is appropriate to make sure they don't mess up the formulas or make unauthorised changes.

There are many well-documented cases where hidden rows and columns have been overlooked in financial models and caused significant errors. Barclays Capital was forced to file a legal relief motion relating to its acquisition of Lehman Brothers' US assets after a reformatting error in an Excel spreadsheet resulted in 179 toxic deals being mistakenly included in the purchase agreement.

The spreadsheet contained nearly 1,000 rows with more than 24,000 individual cells and had to be reformatted and converted into a PDF document before being posted on the bankruptcy court's website by the end of the day. A junior associate reformatting the work was unaware that the original Excel document included hidden rows containing contracts that were marked with an n to signify they should not be part of the deal. The document was posted on the website, but the hidden cells had become visible when the employee globally resized the rows in the spreadsheet—without the original n designations. The error was not spotted until Barclays posted a revised list of contracts, with notice to the affected parties.

This story illustrates why it is highly recommended to use the Grouping Tool when hiding rows and columns in financial models. Google “Excel Horror Stories” and you'll find a long list of the latest horrific Excel error stories. For more tips on how to avoid being part of your own Excel horror story, see the section “Error Avoidance Strategies” in Chapter 4.

Advantages and Disadvantages of Using Array Formulas

The advantages of using array formulas are as follows:

• They ensure consistency, because all formulas in the table are exactly the same.
• A model containing arrays will use less memory and be more efficient.
• Because it's not possible to change a single cell on its own within an array formula, it is unlikely that you or someone else will change your formula accidentally.
• Because array formulas are difficult to understand, it means that those with only basic Excel knowledge are less likely to change (and mess up) your formulas.
• Array formulas do make it possible to perform some calculations that would otherwise be impossible. This is really the only reason you should use an array formula.

The disadvantages of using array formulas are as follows:

• Although array formulas use less memory, if you use too many large arrays in one model it can slow down your calculations.
• You cannot use column references (such as A:A or D:D) in your array formulas. This is not best practice in financial modelling anyway, so no great loss!
• They are difficult to audit for many Excel users and require specialist skills, although this can be considered both an advantage and a disadvantage.

Simple Array Formula Example

1. Create two simple blocks of data (arrays), as shown in Figure 8.7.

   

2. Now, instead of simply multiplying Quantity by Price, we are going to create an array formula instead. Highlight the block of cells C1:C10 and type/select in the formula bar, as shown in Figure 8.7.
3. Now, if you are using Excel 2016 or earlier, hold down the Control and Shift keys while hitting Enter at the same time (Control+Shift+Enter). Excel 2019 users can simply hit Enter.
4. The formula will appear in the whole array block, as shown in Figure 8.8.

   

Try deleting one of the lower cells in column C. Note that with the exception of cell C2, you now cannot make any changes to the array block by changing an individual cell.

Please also note that array formulas were sometimes referred to as CSE formulas in Excel 2016 or earlier, because you had to press Control+Shift+Enter to enter them.

Array Formula Uses

Financial modellers generally resort to the use of array formulas only when it is not possible to do something any other way. There are many such instances, but some examples of these situations are as follows:

• Data tables are a form of array formula that is extremely useful for scenarios and sensitivity analysis in financial modelling. Instead of changing inputs one at a time, an array shows hypothetically what an outcome would be simultaneously in a single table.
• TRANSPOSE allows you to transpose a range of data, either vertically or horizontally, and retain links. We'll take a look at how to use this very handy technique in a moment.
• Using a SUM and a VLOOKUP together as an array formula makes a very concise formula. For example, will give you a sum of values in rows 2, 3, and 4 instead of having to do Note that if you are using Excel 2016 or earlier, you need to hold down Control, Shift, and Enter for the array version of this formula to work.

Transposing Data Using an Array

One of my favourite uses of an array formula is to create a TRANSPOSE function, which will transpose data, allowing a link to be maintained within the model.

Let's say you have some city and temperature data in a table, as shown in Figure 8.9, but you'd like to transpose the data so that months and cities are switched around, as shown in Figure 8.10. You can create this sheet yourself, or a template can be found along with the accompanying models to the rest of the screenshots in this book at www.plumsolutions.com.au/book.

Before you start cutting and pasting, try the transpose values tool first:

1. Highlight the original table, and copy it to the clipboard using Control+C.
2. Right-click in the cell that you want the data to appear, and select Paste Special.
3. Check the Transpose box as shown in Figure 8.11, and click OK.

This will paste all the transposed values as hardcoded data in a new table. Using this method creates two separate, independent tables of data.

Using PivotTables in Financial Models

Useful as they are, PivotTables are not used very widely in building pure financial models. They are a very popular tool, however, and so it's difficult to be good at using Excel for business and financial modelling (which is the title of this book) without knowing how and in what context to use PivotTables. For this reason, I have provided a brief overview of PivotTables in this chapter.

The reason I don't recommend the use of PivotTables in pure, dynamic financial models is that they are static until refreshed, and this does not fit very well with the methodology of most financial models. As discussed in earlier chapters, the whole point of financial modelling is that you are able to change the inputs, and the outputs change! It's far too easy for a user to change the inputs, and then forget to refresh the PivotTable. This results in using incorrect outputs from the model.

As an alternative, I recommend the use of an alternative live, dynamic formula, such as SUMIF or SUMIFS, in place of a PivotTable when building a classic financial model that depends on inputs being changed. See “Aggregation Functions” in Chapter 6 for how to use the SUMIF and SUMIFS functions.

Whilst it is possible to change the settings so that the PivotTable will automatically refresh whenever the file is opened, there is no guarantee that the user will open and close the file before using the data, and it cannot be relied upon. It's not a bad idea to change the settings to automatically refresh in any case, however. You can do this by right-clicking on a PivotTable, then go to PivotTable Options as shown in Figure 8.17. On the Data tab, select “Refresh data when opening the file”.

Other Things You Should Know About PivotTables

• You can apply any formatting to the PivotTables. Excel has some very good PivotTable formats that can be easily accessed via the Styles on the Design tab.
• You can easily change the PivotTable summary formulas. Right-click on the PivotTable and select the Summarise Data By option from Sum to Count or Average, for example.
• You can also apply conditional formatting to PivotTables, although you may want to be a bit careful as PivotTables scale in size depending on the data.
• Whenever the original data from which PivotTables are constructed changes, just right-click on the PivotTable and select Refresh Data.
• Recommended PivotTables, as shown in Figure 8.18, is useful as it gives you a preview of what your data will look like in a PivotTable.

Building a PivotTable

If you need to create a report summarising sales by customer, for example, you can follow these eight instructions:

1. Highlight the entire database, ensuring that the headings are at the top of the range, or just click somewhere in the block of data.
2. Select PivotTable from the Insert tab, in the Tables section. See Figure 8.19.

   

3. By default, it will put your PivotTable on a new page. If you prefer, you can change the options to locate it on the existing or a different page.
4. Press OK. The PivotTable Field List will appear on the right-hand side.
5. If you drag Customers into the Row Labels field and Price into the Values field, it will summarise your data and create a PivotTable report that looks like Figure 8.20.

   

6. If you now drag the Product field to the Rows field below the Customers field, it will split your report into subcategories.
7. Try slicing and dicing your data in different ways. Swap the customer and product fields, or try moving products from the Rows field to the Column field.
8. The PivotTable can now be quickly formatted using the drop-down styles on the Design tab.

Note that in versions later than Excel 2016, PivotTables automatically group dates in the column or row fields, as shown in Figure 8.21. If you'd prefer to group the dates manually, follow these steps:

1. Click on the first date in the column field, as shown in Figure 8.21, and select Group.
2. Choose Months, Quarters, Years, or however you'd like the dates to be aggregated. In the example shown in Figure 8.22, we have chosen to group by Months, so we can see a forecast of fees by month in this PivotTable.

   

Filtering and Using Slicers

Using the data from the previous example, we could drag the Product field into the Filters field in order to filter the data by Product, as shown in Figure 8.23.

This filter can be used to show DVDs only, CDs only, or a combination. Note, however, that if we select “Multiple Items” as we have done in Figure 8.24, it ceases to show which items have been selected, which is not very helpful from a reporting perspective. Using a slicer in this instance will make the report more user-friendly.

Macro Settings

Before we begin creating macros, there are a few settings in Excel you'll need to change:

• Showing the Developer tab in the ribbon is not strictly required to record and run macros, but it contains tools that make it easier to run and edit macros, as well as some handy control tools and buttons.
• Changing the security settings is required, and you must change this setting if you want to run macros.

The default installation version of Excel does not have these settings. You will need to change these settings only once, and you will not need to do it again unless you reinstall Excel, or move to another machine.

Showing the Developer Tab in the Ribbon    Whilst it is possible to record a macro by clicking on the launch button in the bottom left-hand corner of your screen, as shown in Figure 8.27, the Developer tab contains other functions that are useful when developing macros and other tools, so it's best to install the Developer tab.

If you do not see the Developer tab, click on File—Options—Customize Ribbon. Check the Developer box. Excel for Mac users go to Excel— Preferences—Ribbon & Toolbar and check the Developer box. For more detailed instructions on how to do this, read “Showing the Developer Tab in the Ribbon” under “Form Controls” in Chapter 7.

Creating Macro Buttons

You can assign a macro to almost any object or shape on your Excel worksheet, including shapes, buttons, and even combo box drop-downs. The most commonly used object is a rectangular-shaped button. There are two ways of creating these buttons, either as shapes or traditional form buttons.

Macros in Financial Modelling Case Studies

There are hundreds of situations where macros can save time and increase accuracy. They might not necessarily be built and saved within financial models, but they could be used in the collection of the data that goes into the model. Here is a small sample of a few of the most common problems in financial modelling that are solved by macros. You can download some of these example models from www.plumsolutions.com.au/book.

• A company is doing a salary comparison between different countries and wants to know how much they would need to pay in Malaysian ringgit (MYR) so that their salary is the same as someone working in Singapore. If a worker in Malaysia earns the equivalent of 100,000 MYR after tax, how much would the company need to pay the worker before tax? The problem is that tax is calculated on pre-tax salary, so our formula gives us a circular reference. We can use a macro with a goal seek to repeatedly set the pre-tax salary to the amount entered by the user.
• Fifty identical budget templates have been created, and you discover a formula needs to be changed. Instead of making the change 50 times, record a macro. It will be much quicker and far less prone to error.
• A nonprofit organisation sets its pricing so that all costs are fully recovered to break even. The costs keep changing though, and the user is a senior account manager who doesn't know how to do a goal seek in Excel. The modeller creates a simple macro using a goal seek so that all the user needs to do is change the costs and press the button to find out how much the pricing needs to change under the new costing.
• A dump of information containing several thousand rows is exported from a database into Excel every day. The data needs to be formatted and manipulated manually to be used in a daily takings report. Automating this with a macro (or with Power Query) can literally save hours of manual data manipulation.
• A reporting model is built using a PivotTable, but when new data is entered, the user sometimes forgets to refresh the PivotTable. The modeller builds a button to refresh the PivotTable every time it's pressed.

Dangers and Pitfalls of Using Macros

Handy as they are, macros should always be used with caution. Quite often formulas or filtering will achieve the desired result, without the need for building a macro. Always try a formula or standard Excel solution before considering the inclusion of a macro in your model.

• Unless ranges are defined properly using relative cell references, macros don't move their references the way that formulas do, and therefore named ranges are recommended when building macros. If any data has moved since the macro was written, the macro will refer to the wrong cell. A badly written macro can copy and paste over data, and you won't know what it has done!
• Visual Basic is a complex programming language and a skill that many people don't have. Therefore, many people are not able to edit or follow what a macro is doing—especially if it hasn't been documented properly.
• Macros are time-consuming to write, and often a much quicker and more transparent solution can be built just as easily with a plain formula.
• Running macros requires security settings to be changed (as shown above), and if your model is used by other people, they may not realise they need to change their settings, causing the model to not work properly.
• Macro-enabled files need to be saved in .xlsm file format. If they are accidentally saved as a .xlsx macro-free workbook, the macros will be removed from the file. If this happens, the macros have been deleted and you'll need to either rewrite the code or copy it back in from a backup copy!
• Unless you will be maintaining the model yourself, most business Excel users have more than enough to do without also trying to learn how to program.
• If you're the one running the macro each time the model needs to be updated, that's fine, but what will happen after you're gone? If the process stops working due to a macro problem, it can hold up monthly procedures significantly.

Basically, there are more things that can go wrong if a financial model contains macros. When it comes to building financial models, consider whether a VBA solution is absolutely necessary before including it in your model. When building a financial model, macros should not be used unless there is no other option to achieve the required result.