Protect your spreadsheets!

To reduce the risk of user error, you can protect your worksheets so that users can only change input cells and perform certain other restricted actions such as select cells and use filters. To enable this is a two-step process: (1) protect cells and (2) turn protection on.

1. PROTECT CELLS

All cells where changes will be prevented (i.e., changes will not be allowed) must be formatted as ‘locked’ and all cells where changes will be allowed (input cells and drop-downs) must be formatted as not ‘locked’. To see the status of a given cell, select it, then format cells (shortcut Ctrl 1) and select the ‘protection’ tab, as shown in the screenshot below.

Locked: By default, all cells in a new workbook are locked. That means you need to unlock all input cells, including any drop-downs. It is best to do this as you develop your spreadsheet since unlocking input cells later is a time-consuming, error-prone process – you are bound to miss some and, consistent with Murphy’s law, they will be exactly those cells the user wants to change.

Hidden: The protection tab offers an additional option, ‘hidden’. This allows you to hide the cell formula in the formula bar when the cell is selected. In the interests of transparency and understanding a spreadsheet, I do not recommend this option.

Bonus tip #1: Use cell styles to easily remove protection for input cells

The easiest approach to unlock input cells is to use cell styles. Format an input cell with "Locked" turn off. Then go to the Home ribbon and click on the down arrow at the end of the the Styles section.

Select "New Cell Style..." and save the format as e.g. "01 Inputs" (numbering ensures your cell styles appear in your desired order).

Now when you want to format an input or drop-down cell in your model, simply select the cell(s) and click on the relevant style name in the styles box on the home ribbon. The cells are then given the appropriate colour and are automatically unlocked… simple and effective!

I typically use one cell style for inputs and another for drop-downs with a different colour, both of which are unlocked, i.e., the ‘locked’ option box is not ticked.

See Microsoft support for more details on cell styles.

Bonus tip #2: Use background error checking to identify missing protection

You can turn on background error checking for ‘Unlocked cells containing formulas’ under ‘File’, ‘Options’, ‘Formulas’, ‘Error checking rules’. Excel then automatically marks any unprotected cells with a green corner, and you can then correct the cell formatting, as explained above.

2. TURN PROTECTION ON

This step is hinted at by Excel in the note underneath the protection settings. ‘Locking cells … has no effect until you protect the worksheet.’ This is simple enough to do.

Select the relevant worksheet, navigate to the Review ribbon, click ‘Protect Sheet’.

Then select what you want to allow. ‘Select locked cells’ and ‘select unlocked cells’ are allowed by default – generally sensible – but can be deselected. All other options are deselected by default but you can tick an option box in each case. One I generally allow is ‘Use AutoFilter’, especially for data analysis files or in models with large input sheets.

The final step is to input a password (if desired) and you are done. Obviously, you must remember the password, which can become a problem if you have lots of protected spreadsheets in use. You can therefore consider protecting the worksheet without a password to avoid problems.

Repeat for all further sheets in the model that you wish to protect. If you use a password, I strongly recommend using the same password for all protected worksheets in a single workbook otherwise the pain of password management or the risk of forgetting a password becomes too great.

I am afraid there is no option to turn the protection on (or off) for all worksheets at once, so this task quickly becomes time-consuming, unless you use a macro…

Macro to the rescue!

I use a macro to protect all worksheets in a workbook and another to unprotect them all. To get this, please download my free bonus material for the book "Avoid Excel Horror Stories" at https://www.how2excel.com/en/downloads-en. The VBA macro code is in the main file ‘how2excel – example calculations and macros’.

The ‘protect sheets’ macro also solves another problem with protected sheets: by default, you cannot open and close row and column groupings on protected sheets (an Excel bug), which can be frustrating. As well as protecting sheets, the macro provided specifically enables the opening and closing of row and column groupings.

Extra protection: workbook structure

On the Review tab next to ‘Protect Sheet’ there is an icon for ‘Protect Workbook’. This enables you to prevent users changing the structure of a spreadsheet (e.g., moving, renaming, deleting or adding worksheets), which may be worth considering.

RESULT

In the suitably protected worksheet shown above, users can only enter and change values in the grey input cells. If they try to change any other cells, they get an error message like this.

Don’t confuse these calculations!

Profit percent, percentage changes and average prices – all three are very useful for business but for each one there are two calculation methods so there is a risk of confusion and error.

In this blog I go through all three and in each case I explain the two calculation methods and recommend which one to use.

Let’s go…

 

1. Gross margin % or mark-up % ?

Both of these figures are related to the gross profit, but they serve different purposes and are calculated differently.

Gross margin %

  • Margin tells you what proportion of your sales is left as gross profit.
  • It is useful for comparisons e.g., of two different products, two business units, two businesses, two years, or plan and actual figures. It can also tell you how much you can reduce selling prices by and still make a gross profit.
  • The calculation is gross profit / sales.
  • In the example shown above this is 400 / 1000 = 40%.
    That means that for every 100 Euros of sales, you are left with 40 Euros of profit (i.e. 40%).


Mark-up %

  • Mark-up on the other hand tells you how much you add to your cost of goods sold (COGS) – i.e. what it costs you to buy or produce the products or services you are selling – to get your selling price.
  • It is useful for calculating or assessing selling prices and is typically applied to individual products or groups of products.
  • The calculation is gross profit / COGS; if your COGS figure is shown as a negative number, you can add a minus sign to your calculation so that the result is a positive number.
  • In the example shown above this is 400 / 600 = 67%.
    That means that for every 100 Euros of costs, you add 67 Euros on top to get your sales price. For 600 Euros of costs you would add 600 * 67% = 400 Euros, which represents your gross profit.


Recommendation

In general, I recommend the margin %. Sales prices are often set in comparison to competitors (market-based pricing) or what you believe the value to be for your customers (value-based pricing). The concept of mark-up is then not relevant, but of course, you should still consider your costs.

The margin % is also consistent with a “single size profit and loss account (P&L)” analysis where all P&L items are calculated as a percentage of sales. This extends the approach for comparing e.g. two years or two businesses from comparing just the gross profit to comparing all P&L lines.

 

2. Change in percentage points or percent?

A change in percent is often used to plan or assess e.g. costs. If you are analysing monetary values, there is usually no confusion. If costs have risen form 1,000 Euro to 1,200 Euros then we have a 20% increase = 200 Euros rise / 1,000 Euros.

Things can get confusing however when you compare the change in a percentage figure, such as (i) cost of goods sold (COGS) as a percentage of sales or (ii) interest rates, as shown in the example below. Here the interest rate has risen from 10% p.a. to 12% p.a. (unrealistic but simple figures for the purpose of illustration).

Note: I took this example from the website https://www.mathsisfun.com, so thanks to them!

Here we can calculate and talk about the change either in percentage points or in percent and we must be clear which one we are using.

Change in percentage points

  • The interest rate has risen from 10% to 12% so the change is simply 12% - 10% = 2% i.e. 2 percentage points and not 2 percent.


Change in percent

  • The change in percent however is 20%, because the rise of 2% points is on top of the old rate of 10% = 2% / 10% = 20%.
  • If we examine the interest costs, these have risen from 1,000 Euros p.a. to 1,200 Euros so we also have a 20% increase = 200 Euros rise / 1,000 Euros.


Recommendation

Both figures are correct, but you must be clear about which one you are using. In general, we are interested in the impact in monetary terms, which the change in percent figure tells us.

As the mathisfun website says, “When in doubt, use both. For example, say 'Interest rates increased by 2 percentage points today, meaning a 20% increase in interest payments.'"

 

3. Simple or weighted average?

In this final example we are selling two products: a small widget and a large widget. The small version sells for 10 Euros each and the large version for 20 Euros each. What is the average selling price?

Simple average

  • If you have no information on numbers of units sold (or you choose to ignore them), then you can calculate the simple average = (10 + 20) / 2 = 15 Euros
  • This is really only true if you sell the same number of units of each product, which is probably not the case.
  • In most cases, the simple average gives an incorrect understanding of the average price as we have not considered the numbers of units of each product sold. When we take these into account, this can have a dramatic impact on the average, as we will now see.

Weighted average

  • The correct way to calculate the average selling price is to take the total sales divided by the total number of units sold.
  • In this case we have 1,000 Euros total sales divided by 95 total units sold which gives a weighted average selling price of 10.53 Euros.
  • This figure is much lower than the simple average of 15 Euros because it has been weighted by the number of units sold: we have sold many more units of small widgets than large widgets, so the weighted average price gets weighted towards the small widget price of 10 Euros.

Recommendation

Use the weighted average. To calculate this, you need to use total figures, as shown in the example above.

If you don’t have enough data to calculate the weighted average, state that the figure you show is a simple average. And don’t use it for calculations.

Custom lists help you enter and sort data

Standard Excel

In Excel you can easily create a list of months by typing in the first month and then dragging the cell down (or to the right) using the small square (handle) in the bottom right of the cell. This also works for abbreviated month names (first three letters off the name) as well as days of the week. This is possible because Excel has suitable lists available for such purposes. You can also sort data using columns containing days or months.

Customised Excel

Wouldn't it be great if you could do the same with a customised list of e.g., brands, regions or priorities (high, medium, and low) in use in your company?

Well, you can!

Set-up

First type in your customised list in Excel, then go to File, Options, Advanced, General section, Edit Custom Lists…

You can then import your custom list. Alternatively, you can type in your list directly in the “List entries” field.

Use

Back in Excel you can type in one item in your custom list and then drag the handle to complete the list.

You can also sort using your custom list.

1. Select the column in your data containing the items in your custom list.

2. Next, click on the main sort icon

3. Under “Order” choose “Custom list…”
Finally select the relevant list and click ok.

Your data is now sorted according to your custom list.

Fantastic!

An inexact match can be exactly right!

Exact or inexact?

Firstly, let us be clear about the difference between exact and inexact matches.

Exact matches

Normally when you are doing a lookup using MATCH, XLOOKUP or VLOOKUP, you want to find an exact match in your look up table. For example, you want the cash discount percent for a specific customer. When you look up the customer account number in your lookup table, you want to find the result for the specific customer and not for another customer which happens to have a similar account number.

In such cases you need to use the optional argument of 0 for MATCH and XLOOKUP or FALSE for VLOOKUP. The lookup table data can be in any order, Excel keeps looking until it finds the first match, or reports an error if it cannot find the searched item at all.

 

Inexact matches

In some cases, however, it may be necessary to use an inexact match if the item you are looking for may not be exactly found in the lookup table, for example a list of sales discounts based upon the quantity sold (see example 1 below).

In such cases you need to use the optional argument of 1 or -1 for MATCH and XLOOKUP or TRUE for VLOOKUP. The lookup table data must then be in ascending or descending order, per the tooltip shown (note that VLOOKUP can only cope with lookup data in ascending order). Excel then searches until it either finds an exact match or returns the result that is “not too far”. This is best illustrated with some examples.

In all four examples there is an input cell (grey background) and an output cell (green background). The output cell uses the powerful combination of the INDEX and MATCH functions to get the correct result from the lookup table using an inexact match. You could use XLOOKUP instead (or possibly even VLOOKUP but I do not recommend this function as it has a number of weaknesses and risks).

You can download the Excel file with all four examples here: Inexact match download

  1. Sales discount
  • Task: Input the quantity sold to get the discount percent.

  • Solution: We need to find the largest “quantity sold” in the lookup table which is less than or equal to the input quantity to get the corresponding discount percent. We therefore use the inexact match argument 1.
  • Lookup table: This requires that the look up table is sorted in ascending order.

 

  1. Exam grade
  • Task: Input the exam result in percent to get the exam grade.

  • Solution: Similar to case 1. The inexact match argument is also 1
  • Lookup table: Per case 1, sorted in ascending order.

 

  1. Payment date
  • Task: Input the invoice due date to get the next payment date.
    This can be very useful for cash planning tools: use an accounts payable report to generate payment dates.

  • Solution: If the lookup table is in descending order, the solution is relatively straight forward, but in reality a list of dates will probably be in ascending order. This makes the solution a little trickier but with a bit of testing you can find the correct approach.
    > Search date: We must deduct 1 from the input due date in order to also get the correct result when the input date exactly matches a payment date, otherwise we will get the following payment date.
    > MATCH finds us the largest (i.e. latest) payment date which is less than or equal to the search date, i.e. it gives us the prior payment date. We must therefore add one to the MATCH result to get the next payment date.
  • Lookup table: The lookup table is sorted in ascending order (because that is probably how it will be) but has only one dimension. The formula searches for the invoice due date (input) in the list of payment dates and returns the next payment date from the same column.

 

  1. Fruit sorter
  • Task: Lastly for a bit of fun we have a fruit sorter 😊
    Input the fruit size in centimetres to find the smallest box that is big enough to hold it.

  • Solution: Because we are looking for the smallest box that is greater than or equal to the fruit size we need to use the inexact match argument -1.
  • Lookup table: In this case, the lookup table must be sorted in descending order. There is an extra column here to clearly show the box in which the fruit ends up. This is determined using a simple IF formula with a fruit symbol… this is an emoji selected using the Windows key and . and then typing fruit to see a selection of fruit symbols. After my selection, I changed the text colour of the "Fruit" column to a dark orange.
  • And action! Here's the solution in action...



You want more results?

These approaches are not restricted to a single output cell but can be used in a large table of calculations with results calculated for each row, as shown below for the fruit sorter with a chart of results for analysis.

 

I hope this blog helps you in your work!

 

More help

For more explanations please see my videos on INDEX and MATCH or XLOOKUP,  Here you will also learn the problems and risks associated with VLOOKUP.

Use a BASE corkscrew to plan fixed assets and loans

In a financial model you need to plan balance sheet positions. There are various techniques to do this, and the most suitable technique depends upon the balance sheet position. For fixed assets and loans, a BASE corkscrew can be ideal.

BASE stands for

  • Beginning balance = End balance of prior period
  • + Additions
  • - Subtractions
  • = End balance

For fixed assets, the additions represent CapEx, and the subtractions represent depreciation. Subtractions could also represent sales of assets but as a general rule, we do not plan to dispose of assets, but simply replace them (CapEx) as they become fully depreciated.

For loans, the additions represent loan drawdowns, and the subtractions represent loan repayments. Note that interest does not affect the loan balance (unless you do not pay the interest but add it to the loan balance, a so-called PIK loan = payment in kind). The interest is a cost which flows into the P&L.

So what does a BASE calculation look like in excel and why is it called a corkscrew?

The screenshot shows an example for fixed assets. I selected the closing balance figure for the first year and used the “Trace Dependents” button repeatedly (this can be found on the Formulas ribbon under “Formulas Auditing”). The zigzag corkscrew effect of the data flows is clearly visible.