Guide to business modelling download

An asset life, for the purpose of depreciation, should be both greater than zero and less than a 47 Useful functions maximum value. For the outcome of an AND to be true the answer to every test must be true. For the outcome of an OR to be true only one of the tests needs to be true. The function is illustrated in Chart 7. Continuing the example above, the function could be used to count the number of invoices issued to each country.

Extending the volume of data would only change the ranges defined rather than the methodology. The range is the cells with the currency identifier. The criteria is a reference to a specific country which is identified in cell A16; for this row it is FR.

The sum range is the set of amounts. In cell C19 the summarised data is totalled. When this summarised total is compared with the total of the individual invoices it provides a helpful audit check to ensure completeness of the analysis. OFFSET For a model to have maximum impact it needs to be a highly flexible engine through which input data and scenarios can be passed.

It would take a long time to record all the inputs for each scenario and enter them individually for each use of the model. Hence it can be useful to park all the scenarios in separate columns of the model and use a switch to select between them. Alternative scenarios are set up in columns E to H. As the dataset number in B3 is changed so are all the inputs in column B that are used to drive the model.

The benefit of this function is that it adds and counts the number of items included and therefore is flexible to the number of items in a range of data. For example, 7 MOD 3 is 1. For example, if a computer was replaced every three years MOD could be used to trigger the purchase by being applied to the year number.

A much more effective way of operating is to use the keyboard throughout. This can be achieved by using keyboard shortcuts that replicate mouse functions. It would take 51 Useful features ten pages to list all the available options in Excel, whereas a simple use of the HELP function will reveal all of them. On the HELP index, select shortcuts, keys and work through the available items. Some of the most useful shortcuts are as follows.

The ripple effect will cause all dependent cells to show similar division by zero errors, making it difficult to find the source of the problem. The trace error button on the auditing toolbar will identify each incidence of the error as follows. To use the function, place the cursor in an error cell and press the trace error button, which is a yellow diamond with an exclamation mark. The cursor will move to the cell creating the problem and highlight all connections from the errant cell over to the cell from where the function was called.

It can be used to pick up and apply formats from one cell to another. This is particularly useful for number formats that have been created with defined styles and number of decimal places. To use the function follow these steps: Click on a cell that has the format you want to copy. The format painter picks up every attribute of format — colour, style, font, size, protection, and so on.

Before using the feature, be sure you want all these attributes. Conditional formatting In addition to fixed formatting, which is constantly displayed, there is a facility to have conditional formatting that is displayed only when defined criteria are met.

An example would be to have a red warning appear if there is an error or a highlighted cell to mark the highest number in a range. The condition rules are properties about the conditionally formatted cell. Text boxes require cell references or absolute values to be entered. For example, the conditional format can be invoked when the cell is equal to 5 or is between the values of two other cells. The condition rules are attributes of cells other than the conditionally formatted cell. The condition is defined by a formula that yields a true or false answer, for example if one cell is greater than another.

A common conditional formatting for use with warning messages is a white font on a red background. One difficulty of using conditional formatting is that it is invisible until the condition is met. Select the special button. Select conditional formats. Under data validation, select either all which will highlight every cell that has conditional formatting or same which will highlight every cell that has the same conditional formatting as the cell where the cursor is currently placed.

Although this function can be used to give clear highlight to conditions such as errors or maximum values, it has most impact when used infrequently. Thus when it is invoked it is significant and obvious.

A sea of changing cell colours will disguise significant issues and lessen the attention they command. Using graphs Often the most user-friendly way to display numerical information is graphically. Graphs make trends in the data easy to discern. They also make it easier to examine relationships and to spot potentially erroneous data inputs. In Excel the creation of a graph involves the following steps. From the insert menu select chart or press the chart wizard button.

Select the appropriate chart type column, bar, line or a host of others by clicking on one of the options. The type of chart to use will depend on the data being displayed. Revenue and cost information on an annual basis is often best displayed in column or bar charts. Macroeconomic variables, such as interest rates, exchange rates and inflation, and microeconomic variables, such as product prices, are often best represented in a line graph. When the information focuses on a particular year, such as a summary of expenditure types, a pie chart can often be the most effective.

Select the sub-type of the preferred type of graph: vertical, horizontal, with or without markers, and so on. The choice is often a case of personal preference. Press next. Identify the information to be presented in the graph. Having pressed next in the previous step the user is automatically presented with the choice of entering a data range or block of data or series several separate blocks of data.

Select series to allow more control over the information that is presented. To add a data series click add. Select the values required either directly from the spreadsheet by highlighting them or by typing in the data range. Select a name for the series by either manually entering a name or linking to a name on the spreadsheet.

Steps 5—7 can be repeated until all the data series have been identified. The same section also allows you to select a series to represent the scale for the x or horizontal axis usually years for most models.

The next step takes you to the options for the graph which appear on several tabs: Titles describe the attributes displayed. It is helpful to enter a main title and a vertical axis title. The horizontal title may not be required especially if the values are years or dates. If the scale on a vertical axis is monetary, include both a currency indicator and the scaling, such as thousands, millions, and so on.

This will enable the scales to be resized automatically to fit the range of data to be displayed. If a data table is provided to support the graph, then the gridlines could be excluded. Usually, graphs look better without gridlines. It is usually better to position the legend at the side as this allows the maximum height for the graph itself.

The legend should enable an efficient interpretation of the data. Once the options have been selected and ok has been clicked, you can choose where you would like the graph to appear. Once the selection has been made the graph will appear in the appropriate sheet of the model. Improving the appearance of the graph By right clicking at any point on the graph you can gain access to location-sensitive menus.

Depending on whether you have clicked on a data line, background, gridline, axis or labels the following option s will appear: chart options — changes can be made to legends keys and axes.

The presentation of the graph is usually improved if bright primary colours are used rather than the defaults. It can also be helpful in presentation to increase the weight of the line to make it stand out more significantly. If the graph is to be part of a blackand-white document, the use of dotted and dashed lines will be easier to interpret than a series of grey tones.

Useful features 55 Chart 7. To create eyeball lines follow these steps: Beside the actual data for a line, manually enter a series of numbers that are approximately the same. Show this on the graph by adding it as a series. This is known as a trendline and it can be linked to the manually entered numbers. To do this, right click on the line and click add trendline. Select the type of graph on the type tab. To do this left click the mouse to highlight the individual data points.

The graph will change, the trendline will change and the trendline formulae will change. Back in the spreadsheet the data series will also change. Eyeball lines can be used to support the generation of assumptions with the trendline formulae being used to project the assumption into the future. One graph suits all To support the model output it can be tempting to show a whole series of graphs, each on its own separate sheet. A proliferation of sheets can make navigation through a large model unduly complicated and time consuming.

A solution is to have only one graph 56 7. Display data and data table are the working areas to make the graph menus work. As these two working areas are not required by the user, they could be put on their own sheet.

The sequence for building the graph sheet is as follows. Data table The area is filled with data that is typically drawn from other output sheets, such as: Operational data — customer numbers — staff numbers Financial data — cash flow numbers — balance sheet items — profit levels Performance data — ratios The data lines in the table are linked to its source with cell references.

This ensures changes in the assumptions will follow through into the data table. Useful features 57 Display data This is the source area for the lines to be shown on the graph. Typically, a sheet like this would allow for between two and four lines to be displayed. This method enables users to select the information they want displayed; the OFFSET function will pull the relevant information from the data table ready for the graph to display the contents of these cells.

Graph The graph will have an x axis based on the number of years in the model. The lines to be displayed will be those defined in the display data area. Selection area This is where the user selects the information to be viewed on the graph.

This can be done either by a simple entry of a number selected from a list of available options or, more effectively, by using a drop-down menu see Chapter The x axis is linked to row To keep the dynamic nature of these graphs the 58 7.

When users change their list box selection the number of the item chosen will be displayed in these cells. The first item in the list will result in 1 being placed in the cell link. In the example line two has been selected as operating profit, hence the row offset value in B24 of 3 will link the cell to the row of operating profit data E The first row is set up as blank. This enables the user to deactivate some of the display lines.

Therefore if you only wanted a graph of operating profit, you could select this as line one and then set line two as blank. This would remove line two from the graph. The overall aim is to: create a clear and logical structure that will enable easy adaptation and development; be able to change any one assumption and still have a valid answer; create a dynamic environment in which to test scenarios and sensitivities; create legibility that will allow users to read the cells as well as the answers offered.

The features of these are shown in Chart 8. Chart 8. The logic may seem simple, but it requires discipline to apply. Take, for example, an inflation rate that could be applied to costs as they are projected into the future. If the inflation rate were coded as a number in each of the cost cells, a change in rate would take considerable re-coding with potentially one or more uses of the percentage being missed and remaining at the previous value.

However, if an inflation percentage is entered once as an input, applied to costs with formulae, it can be easily changed. This layout should be applied without exception, even to the extent that the start year number or the number of days in the year should be placed on the input sheet. For inputs, all numbers means all numbers! This is a fatal error that may go undetected for many a subsequent scenario, thus invalidating the use of the model. The structure can be developed to match the scale of the model.

For large models it may be helpful to create separate input and working sheets for types of assumptions such as revenue, capital costs and operating costs. Output sheets can be set up for profit and loss, balance sheet, cash flow, valuations, ratios and key graphs. Hence the number of sheets can easily rise.

However, the most important attribute to remember is the status of each sheet whether input, working or output and then stick rigidly to the principles of that style of sheet. In setting the design the golden rule is to develop a model that is fit for the purpose — the model should not be over-engineered.

The point of the model should be kept clearly in mind and thus the component elements should only be those that are needed to achieve the objective. All data should be entered into one column with each variable typically consisting of seed and behaviour attributes. In Chart 8. Where possible enter this once on the input sheet and refer back to it for all other uses.

Should the title change, one amendment will update the whole model. Columns A and B: The names and values of the inputs that will be used to drive the model. Row 5: Subheading to group inputs together by type. Sheet layout: inputs 61 Column C: The range names see Chapter 7 given to the inputs for when they are used in formulae throughout the model.

Columns E to H: Data scenarios that can be pulled into column B to drive the model with alternative assumptions. An alternative style of input is to have data validation as part of the input with the scenario datasets starting in column I, as shown in Chart 8.

Rows 9 to 35 are omitted as they contain other input assumptions. Column E: This is used to test the validity of the data driving the model in Column B. All cells other than headings should be formulae built up from numbers entered on the input sheet. Rows 3 and 4: Timelines to reference each column. Row 3 can be created on the sheet, but the value in E4 should be drawn for the input sheet to allow it to be changed.

It can often be helpful to have this as a permanent header to the page. To do this select Row 5 and from the window menu select split.

This will put a bar across the screen, allowing you to leave the top part fixed as you scroll down the working sheet. If you need to swap between the two halves of the sheet, press f6. Row 6: It can be helpful to create common indexes at the top of the sheet that will be referred to on several occasions.

Row 8: Subheading. It is worth having lots of subheadings to ensure that the build-up of logic is clear to the user. Note that the heading in column A includes details of the units that the numbers are based in.

Column B: The range names given to the rows so they can be used elsewhere on the working and output sheets. When modelling on the working sheet it can be helpful to have two windows into the model to save having to continually refer back to the input sheet. To create this display, from the window menu select new window. This will create a second window of your spreadsheet. This puts the two windows one above the other. Select the input sheet for the top window and the working sheet for the bottom window.

To move between the two windows press ctrl f6. Few calculations other than perhaps totalling should be required. Rows 3 and 4: Column titles created by using the range names from rows on the working sheet where these were set up.

Note that all sheets should start with the first year in the same column. In this example, year 0 is set up in column E for both the working and output sheets. Rows 6 to 8: Heading, subheading and display units. Although many models will be written and used in one country, it is important that the units of currency are clearly shown to make it obvious to the reader of any output.

The main rule with currency is to be consistent throughout the model to avoid adding together financial values of mixed currencies. Row Data drawn from the working sheet with references rather than calculation.

Column B: Range names for use in other parts of the output sheet. Some modellers will develop complete components on one sheet, covering inputs at the top and then developing down through workings to create outputs.

The benefit of this style is that when using the model the cause and effect of changing assumptions can be seen instantly without switching between sheets. It also enables modularisation, whereby completed components can be transferred and used in other models with little development and minimal adaptation. This type of layout can create appearance difficulties because column widths have to stay constant in all sections, thus compromising visual presentation.

When starting out in modelling, it is recommended that separate input, working and output sheets are used to help apply a structural discipline to the model. Examples throughout this book use both styles to illustrate how they can be applied. When formulae become longer than one line ofthe screen, try to breakthem up into smaller components that are individually labelled.

For example, instead ofa long formula to workout revenue, breakit up into a formula for number ofcustomers, a formula for spend per customer and a third formula that multiplies the two former calculations. Time spent by the developer spreading out the model is more than compensated for by time saved in the testing process as well as by the users should they try to understand the logic.

One of the clever results of applying range names to rows is that each number stays loyal to the column in which it was created. This is particularly useful on the working page. Hence the need to ensure that every sheet of the model has its first year starting in the same column, in this example year 0 is in column E.

To retain the structure and flexibility of the model, these events can be triggered with a year number, entered on the input sheet. By using IF statements on the working sheet the event can be aligned to the correct year s. Take, for example, the introduction of a tax that is anticipated to become payable in Rather than coding the working sheet for and as two years of zeros followed by a tax formula for onwards, it would be better to put these details on the input sheet.

The objective on the working sheet is to have the same formula every year and any timed events being triggered by dates entered on the input sheet. Also the results would have been better presented in thousands and not millions.

To avoid rounding errors, choose the number of significant figures to display on the output sheet and consistently apply rounding to ensure that all the schedules add up. Although this could be seen as a desire for accuracy, it does help when validating outputs such as whether a balance sheet balances as well as other audit tests.

The number of decimal places can be negative and thus rounded to hundreds or thousands. To allow flexibility, it may be useful to set up the number of places of rounding on the input sheet and thus enable changes to be made quickly if the scale of the project should change. For example, the number There are a few alternative conventions, but the most common is to have income, receipts and assets as positive and costs, expenditure and liabilities as negative.

One alternative is to have income as negative and costs as positive as this is then consistent with traditional debits and credits from book-keeping. As well as having a logical and consistent sign convention running through the workings and outputs, it is important to give users guidance with the sign for inputs.

Normally, all inputs should be positive regardless of whether they are income, cost, asset or liability. Formulae can then be used to convert the number to a negative for display or calculation. As explained earlier in this chapter page 61 , appropriate input validation can also be used to test that the correct signs are being used.

Although there is all this graphical power at your fingertips, there are several rules with formatting that will enhance the look and feel of the model. Whatever selection you make for the design it is helpful to be consistent. So headings are always in capital letters and bold.

Inputs are always shaded in the same colour. In this way the navigation round the model is made easy as well as looking professional. Be aware that the model may be used on different types of hardware. Screen sizes vary so a visual impact may not be replicable on all machines.

The amount of memory and the processing speed will also have an effect. Extra formatting takes up memory and time when recalculating. Thus on older, slower machines time can be at a premium. Colours Less is more. Restrict the spreadsheet colour scheme to three or four colours. Remember that outputs are likely to be printed and potentially photocopied.

Most organisations need the output to look good in black and white. A typical colour scheme might be as follows: Black text on a white background for the majority of the spreadsheet. Fonts Try to stick to one font throughout and use bold and larger font sizes only for titles and headings. Lines For subtotals and totals it is clearer to insert lines round the numbers, as shown in Chart 8. Number styles Some basic rules in displaying numbers are as follows: Numbers should stay in black even if negative.

Replace the minus sign with brackets as they are more enduring when photocopied. Put separators between thousands to enhance legibility. Choose an appropriate number of significant figures. The syntax works as follows: The format for positive numbers is on the left of the semicolon and negative is on the right.

The benefit of this is that the user can see a calculation has been done and the answer is zero, rather than there being some potential numbers missing from the model if the cell is left blank. This will ensure that positive and negative numbers align vertically down the sheet when right justified.

Formatting 69 Alternative formats that may be useful are as follows: Ignore. Be careful because arithmetic calculations will hold true to the full number and rounding errors may occur. It may be helpful to round to visible see pages 65—6 when using this feature.

Column widths Some modellers like to have all the columns of the model visible at once and hence use the zoom and column width features to create this. At the end, it is aesthetically better to have identical column widths for all years, so check the maximum needed and set all the columns to this width note that the largest numbers are often in the final year when the revenue and value has peaked. When this has been done it is obvious to the user as the column and row reference sequences have elements missing.

Another way to hide data is to convert the text to white so that the background is the same as the foreground and therefore invisible. Instances of this are hard for the user to find, particularly when located away from the main development area. If you apply the principles described in this chapter and have a working sheet, there is no need to hide anything.

The output sheet is used to tidy up workings for printing. It is recommended that for clarity everything used to generate the model output is visible. Recalculation The default setting in spreadsheets is that every time a cell is changed the whole workbook is recalculated.

For most spreadsheet applications this occurs instantly. However, as a model grows the amount of calculation to be done can be huge and there will be an obvious pause for recalculation. Thereafter press f9 every time you want the model updated. This can be dangerous as some users may not be aware of this step in processing scenarios through the model and may not have pressed f9 before taking a decision on an output.

If auto-recalculation is turned off, make sure that warnings are clearly shown on the output sheet. Advanced modellers may write a short macro so that whenever the user moves to view an output sheet the model will automatically recalculate see Chapter Microeconomics, in contrast, concentrates on the individual components within an economy. Examples of macroeconomic variables include inflation, exchange rates and interest rates.

The definition can also be extended to include variables that are harder to measure and describe, such as the regulatory environment, company law, social trends and technological change.

This chapter looks at the forecasting of some of the variables commonly used in business models: Gross domestic product gdp Inflation Base interest rates Exchange rates Population These five variables provide the building blocks from which many other macroeconomic variables can be derived. There is no consensus among economists over how best to forecast macroeconomic variables. Any technique that a business modeller employs will be a simplification of reality.

Small businesses may rely on forecasts from banks or brokers or reports by industry bodies and research agencies. In both cases, modelling macroeconomic variables is simply a matter of entering the relevant figures under the appropriate year headings in the spreadsheet. Published reports usually do not extend far enough into the future to meet the needs of a ten-year cash flow forecast. In such cases the model must be flexible enough to accommodate the existing forecast and to generate the figures for the remaining years.

On other occasions the model may have to be designed to produce a full forecast from year 1. Numerous variables determine the growth of gdp, and to produce a formula that explains gdp growth is beyond the scope of this book. However, Gross domestic product 71 many years of observation have revealed that gdp growth rates do not change dramatically from year to year and that they generally follow a cyclical pattern — sometimes described as the business cycle. These two observations can be used to produce a simple forecast for gdp growth and gdp.

The approach used assumes an average underlying gdp growth rate that has a linear trend in either a positive or a negative direction. The actual rate of growth in any one year, however, will depend on the position within the business cycle. The business cycle is modelled as a sine curve where assumptions entered by the user determine the length and amplitude of the business cycle.

The use of a sine curve represents a simplifying assumption, as business cycles do not usually exhibit a constant cyclical pattern. Forecasting gdp is useful as it provides a building block for forecasting imports and exports which can be expressed as a percentage of gdp and for providing an indicator of economic prosperity.

Seed In Chapter 4 the current, actual level of a variable was defined as the seed, the starting point from which future values are forecast.

In Chart 9. This represents the value of gdp at year 0. Behaviour The term behaviour is used to describe the expected future path of a particular variable. In this example it is assumed that no alternative forecasts are available and that the modeller must forecast the behaviour of gdp for every year. The expected behaviour of gdp growth is modelled as a sine curve and is defined by the user inputs below. This example assumes a five-year business cycle.

This represents the difference between the highest and lowest points of the cycle. This represents the average growth rate over the length of the business cycle. Over time the underlying growth rate may change, and this is represented by the input in cell E9. The underlying growth rate may change over time if an economy is moving from being a developing economy into a modern state.

Each input has been given a range name, which is displayed in column B. Chart 9. The example reveals the results of a ten-year forecast. The formulae in each column are identical. The workings have been broken down into a number of stages: Rows 4, 5 and 6 are intermediate steps in producing the gdp growth rate that is calculated in row 8. The adjusted growth rate of row 6 is altered up or down by the multiplication of the position within the business cycle row 4 and half of the amplitude row 5.

Unfortunately, great business ideas do not always receive the support they deserve. The business models that provide the commercial justification are often poorly structured and, in some cases, simply inaccurate.

The result is a misleading view of financial strength. This book provides a guide to the development of business models that is relevant to all sizes of organisation and business modelling situations. Think of it as a journey in which you evolve and iterate how you design your business until you find the best one. Start with an open mind. Use these tools to rethink and reimagine your business.

Let go of previous ways of thinking and try new models. A good place to start is by taking a look at business model patterns and deciding if adopting any would give you an edge in your market. Customers have more choice now than ever before.

Start with the customer and then build outwards. However, you can also simply document the results of a workshop using the free Word or PowerPoint templates. Either way, these business model templates can be adapted to suit a variety of individuals and teams. Download this easy to use business model template created as Microsoft Word docx file. Simply click on the image to the right to download the file.

Download this business model template created as an Excel file xlsx. Out of the business model templates, the PowerPoint in my experience is probably the most versatile. Download this business model template created as a PowerPoint file. If you want to edit the file and change things then use Abobe Acrobat. The value proposition was developed as a second book by Yves Piigneur and Alexander Osterwalder. The most important part of the business model is the value proposition.

Creating a unique value proposition is one of the hardest parts of creating a strong business model. It involves considerable amounts of time, lots of care, iterations and tests. However, a strong value proposition will quickly bring in more revenue and profits than a weak one. Download value proposition template as a Microsoft Word Docx file.

Simply click on the image or the button below to download the file. Download this value proposition template as an Excel Xlsx file. Click either on the image or the button to get your copy. It is designed to have a Master template that allows you to create multiple versions of your value proposition.

Download this value proposition template created as a high-quality PDF file. Click on the image to the right or the button below to download. This is an ideal size for workshops. The business model canvas is more suited to established businesses that have established resources, customers and revenue. On the other hand, the Lean Canvas template is designed to help startups who are at the beginning of the innovation process and shaping their idea into a competitive business model.