Personalising your Best Practice Financial Model

It goes without saying, in the world of best practice financial modelling; the crucial need for a high-level attention to detail. One aspect of this is the importance of delivering a value-added modelling solution, which in part should be highly personalised for the company in question. It should not just be industry-focused, but also based on the unique attributes of the company. There are many ways to further enhance the personalised value of a financial model to a company.

Definitively, personalised is the ability to make or produce something to conform to a person’s individual requirements. The following identifies some more obscure or less understood elements of Excel, which can further improve the personalised added value of a best practice financial model.

Conditional Formatting

In the case of a bilingual or multi-lingual financial model as was discussed previously, conditional formatting can enable variable formatted numbers based on the language specified in a drop-down box. This is a simple way to enhance the user-friendly value of the financial model, as it can improve the reporting of key performance indicators (KPIs) based on varying languages.

A company may want to report the number of loans, by agent in either French or English by formatting the cells. Conventional cell formatting won’t allow the flexibility to alternate between variables such as language. However, this is not a problem thanks to conditional formatting. In this example, if “French” is selected on the drop-down box on the cover sheet, then the highlighted cell switches to “2 Prêt(s)” instead of to “2 Loan(s)” – as was the case when “English” was specified previously.

Tick-boxes

The integration of tick-boxes can further permit the added-value of financial modelling flexibility, which a sophisticated financial model is often called to deliver at the eleventh hour by model stakeholders, such as corporate executives or investors. An example could be the need to tick or un-tick certain business units or subsidiary companies, in order to quickly reconcile the financial statements in the model, against not just the source financial numbers from an accounting system like Simply Accounting, but also against the furnished financial statements from the company’s external accountants.

The ability to tick off the irrelevant foreign entities in the financial model will permit the use of the model’s consolidated but high-level summarised snapshot of the financials; instead of trawling through the detailed financials (in the model) to identify the financials of the specific business unit.

Further, tick boxes can be used to great effect for scenario or sensitivity analysis. For example, financial model users can simply un-tick a specific business unit, asset or product, in order to understand the impact on credit metrics or earnings.

Radio buttons and drop-down boxes

As discussed in “The value-add of Dashboards in Strategic Planning”, there can be immense added value to model users with integrating the likes of drop-down boxes into a dashboard in a best practice financial model. These features can further personalise and deliver the added value of a user-friendly tool to executives, when analysing KPIs or other financial metrics of the overall company, business units or specific areas of business. The radio buttons for example can facilitate different output forms in the dashboard; monthly, quarterly, Year to Date or annually.

As the following demonstration proves, the addition of such radio buttons and drop-down boxes greatly improves the value of the graph. Instead of creating separate individual graphs, which might not be useful to the stakeholder; this dynamic graph can produce any one of at least eight different outputs.

Final Personalised Word

There are many ways to increase the personalised feel of a best practice financial model. The model must deliver a flexible, user friendly tool for executives to suit their respective needs; which is the true essence of the definition personalised. Integrating any of the above techniques; conditional formatting with a multi-lingual model, incorporating radio buttons and drop-down boxes into dashboards, and tick-boxes into the financial model will deliver personalised added value to financial model users.

Why a best-practice financial model is an important corporate governance tool

There are many elements that form a company’s corporate governance structure. The Cadbury Committee (1992) in the UK defined corporate governance as “the system by which companies are directed and controlled”. Clearly a best-practice financial model forms the basis of executive management decision making and future direction of the company, through the leveraging of value-adding tools such as the strategic plan, sensitivity and scenario analysis. The disciplined monthly or periodic reporting of the company’s financial statements helps executive management to control and monitor the financial performance of the company.

How a model helps to better direct a company

There are many instances when a best-practice financial model assists in the direction of a company. The strategic planning process helps a corporation to better conceptualise and quantify its future direction. By outlining forecasted rates of growth, under various growth scenarios, the strategic plan will force executives to be more accountable for all of their decision-making and understand how these decisions will impact the corporation’s future financial performance.

On a more micro level, a commercial pricing model, which is used to price a new good or service, based on a determined unit price, expected direct costs and partial allocation of indirect costs (under the auspices of activity-based costing) represents another corporate governance tool. By requiring executive management to use a uniform template to price a new good or service, it controls or directs the minimum rate of return a corporation will allow for a future good or service sold.

A further corporate governance feature of this pricing model is the version control with the “Full” version restricted to executives like the Controller and CFO, for the purposes of the arbitrary allocation of indirect costs, incremental versus full costing and analysing the cash flow of the commercial arrangement. Whilst the “Light” version is the domain of employees and middle management, who have more operational knowledge and will concern themselves only on the return on sales and profit margin.

A best-practice financial model will feature comprehensive error and alert checks throughout, which will help to safeguard the overall accuracy of the model. This reinforces and bullet-proofs the financial model, and enables executives to safely rely on it as a corporate governance tool, and to better manage the company’s future financial direction.


Controlling the management of a company

Equally as important is the importance of corporate controls, which a best-practice financial model delivers via a customised, audit-friendly and robust monthly reporting tool. This model provides a transparent, consistent and user‐friendly repository for executive management to analyse, and compare monthly and actual financials relative to budget or forecast.

If the company is trailing forecasted or budgeted earnings, then the efficient and accurate best-practice monthly financial model will clearly communicate this to executives. Hence they will be able to make the necessary business decisions, i.e. improve sales or manage costs, in order to help control the company’s expected financial performance. This represents one of the key tenets of a corporate governance tool.

As discussed, the implementation of error and alert checks throughout the best practice financial model, will not only verify the source financial information from a system like Hyperion or Simply Accounting, but will also check financial metrics that were used to report, forecast and analyse the financial performance of the company.


How a model can both direct and control a company

On the surface, a best-practice financial model’s sensitivity and scenario analyses represents a valuable corporate governance function, as they can both influence executive direction and control of the company.

A sensitivity analysis such as measuring the impact of a 15% increase in gas prices on a logistics company’s earnings, or a training company reducing its unit course price by 10% and forecasting a 8% increase in unit enrolment volumes (refer to the following presentation), are examples of a model’s ability to assist executives to control the company’s financial performance.

Whereas a scenario analysis outlining the impact of financial performance, for example the divestment of an inefficient and poorly performing business unit, or a Greenfields capex expansion into a new product sub-range, will help to direct and guide executives with future decision-making.

A best-practice financial model and its role in corporate governance for Your Company

The introduction of a disciplined, best-practice financial modelling regime in a corporation delivers added-value in many ways. Similar to its importance to drive lean finance throughout a firm, a model’s role in the implementation of corporate governance systems is equally paramount.

As the Cadbury report in Britain stated, corporate governance are tools to direct and control a corporation. As discussed, a best-practice financial model helps to control a corporation via disciplined periodic reporting, and implementation of strategic plans or commercial pricing models to assist in the future direction of the company.

Further, the addition of scenario and sensitivity analyses will further safeguard how the company’s executive management direct, control and properly plan its financial future.

Best Practice Financial Modelling

A financial reporting challenge for your start-up company

Your Company is nascent with choppy cash flow and needs to streamline its accounting systems. There may only be a CEO, CFO and a part-time employee to perform basic accounting duties. Hence the ability to undertake proper financial modelling and analysis appears to be a stretch. Further, without a source of historical and actual financial information, it will be extremely hard for your Company to undertake proper strategic planning and forecasting.

A forecasting regime can greatly assist your Company to properly plan its financial future. The following financial model contains such a dedicated forecasting feature, which can enable your Company to seamlessly forecast out it’s income statements, balance sheet and cash flow into the future.

Making steps to overcome your Company’s financial reporting vacuum

You need to brainstorm the elements to forecast a basic financial statement; your Company’s revenue streams, operating expenses, cost of labour and capital expenditure. At first keep it relatively simple and high-level. If your Company’s reporting needs are more thorough, then you can easily increase the detail of your financial reporting i.e. wage cost breakdown.

Although a monthly reporting regime may sound onerous and time-consuming, it will become more seamless after a few months. The great benefit of this is the ability to spend more time undertaking more value-adding financial analysis and commentary of applicable financial drivers to your Company’s business; rather than number crunching of historical financial numbers.

 

 

 

 

The end solution – a financial model.

A thorough and best-practice financial model will allow your start-up Company to remedy its financial and strategic planning reporting challenges. A financial model will become your Company’s financial repository that management will base strategic planning and forecasting upon. The additional functionality of scenario modelling and sensitivity analysis into your Company’s financial model, will allow management to better measure the financial impact of asset purchases/divestures and changes in interest rates or foreign exchange rates for example.

Strategic Financial Planning & Analysis

How can Your Company plan for its strategic plan?

The thorough nature of building or updating a strategic planning financial model requires a great deal of information. Although a strategic plan is a high-level overview of your Company, it is important to obtain immense detail as it will better assist you in completing the strategic plan.Your Company will need to call upon various stakeholders to furnish information.

Some of the information that your Company will need

The information gathering process will take time. Depending on the size and nature of your Company, relevant stakeholders will need time in order to deliver certain information; some will send you revised information at a later date i.e. Treasury’s revised forecast on interest rates or the price of oil. Here is a list of information deliverables that your Company may need to consider when planning for its strategic plan:

  • All cost of labour information, such as base salary, pension costs and other compulsory employment costs for an employer.
  • A detailed breakdown of forecasted sales by product or service, by region and business unit.
  • Credible and accurate forecasted numbers from Treasury for interest rates, foreign exchange, foreign interest rates and applicable commodity prices such as oil.
  • Pertinent government subsidies or tax credits from your Company’s tax department – which your Company may qualify for.
  • If applicable, all the Company’s debt tranche details
  • An assets or corporations register to maintain an updated list of all assets/subsidiaries that have been bought and sold.
  • Accounting and tax depreciation information for all your Company’s tangible assets.
  • Dividend information covering not just your Company’s preference, but also the ordinary shareholders and whether imputation tax credits can be passed onto shareholders.
  • Anticipated government policies that may impact your Company, i.e. making a carbon liability provision in the balance sheet for a future carbon tax or cap and trade system.
  • Corporate tax and other applicable tax rate information
  • Expected debt or capital raisings by your Company, as they will impact credit metrics or shareholder earnings per share.
The benefits of planning your strategic plan

The above list demonstrates the need for your Company to implement some planning. The above list of shopping items is by no means complete or absolute; it is a list to merely jog your strategic planning mind and help you to brainstorm your information needs.

To execute a credible and value-adding strategic plan for your Company, you need to be detailed orientated and source complete data from relevant stakeholders such as treasury, accounting and finance, human resources, legal and policy, and sales/marketing. You need to educate these various stakeholders about the importance the strategic planning process will deliver to your Company.

Insert multiple columns using Excel VBA

This video will outline an approach via VBA, to insert multiple columns at once into an Excel worksheet. This insert multiple columns macro will give us the flexibility, to choose numerous columns to insert simultaneously.

 

Excel AGGREGATEStrategize FM – VBA insert multi columns.xlsb

 

Opening up Visual Basic for Applications

Begin with a custom header to notate the macro. Give a short, but concise macro name. In this case, we will call our macro “insertMultiCols“. Remember to disable the three Excel applications of Calculation, EnableEditing and ScreenUpdating. It is preferable to narrate each part of the code, which will appear in green. 

Next, it is time to declare variables in the code, which in this case is the column count variable – iCountCols.

 

Input feature of the macro

Now we are ready to write out the input feature of our macro. This will enable users to specify the number of columns to insert into the worksheet.

Take your time when typing out this part of the macro; this InputBox syntax must be 100% accurate. The underline is merely there to break up the length of the syntax, and place the remainder of the code on a second line – as is the case here.

Like with most VBA macros, it is wise to prevent erroneous inputs such as negative numbers. This piece of code will trigger the macro to end; if a negative number is entered.

The final element of the macro will trigger the insertion of multiple columns. Remember this code is quite different to the syntax used to insert multiple rows in a worksheet. We can now re-enable the three Excel applications, which were earlier disabled, whilst the macro was running.

 

Test driving the Insert Multiple Columns macro

The macro is now ready to test drive. As we can see, it is referencing column D by its column index number, which is not as intuitive as by its column letter. Thus, we will make a small adjustment to the code to facilitate this preference.

 

Fine-tuning the Insert Multiple Columns macro

We need to replace ActiveCell.Column, by adding a couple of declared string variables colRef and colRef2. Next we must Set these declared variables of colRef and colRef2colRef will be formatted to remove the absolute row number reference from the ActiveCell’s address – $1. 

Then colRef2 will use the combined function Right and Len to remove the $ from the absolute cell reference of the column reference. Now we can replace ActiveCell.Column with colRef2.

insert multiple columns

We can save the macro and workbook, and run the macro again, and see how it is referencing the ActiveCell’s column letter – in this case column D. We will demonstrate the macro again in column G, but this time we will insert 5 additional columns.

Therefore thanks to VBA, we have the ability to insert multiple columns at once.

Insert multiple rows using Excel VBA

Presently Excel only allows users to insert one row at a time, but what if we could insert multiple rows simultaneously?

This video will outline one way, via Visual Basic for Applications or VBA, to insert multiple rows simultaneously into an Excel worksheet. 

Excel AGGREGATEStrategize Financial Modelling – VBA insert multi rows.xlsb

 

Commencing the Insert Multiple Rows macro

Go to Visual Basic Editor and then insert a module, which will store our macro. This macro will enable us to insert multiple rows simultaneously. Start with a custom header to describe the macro. 

Remember to give your macro a succinct and self-explanatory name. In this example, we will name our macro insertMultiRows. Don’t forget to disable the three Excel applications of Calculation, EnableEditing and ScreenUpdating. It is desirable to narrate each part of the code, which will appear in green because it will improve the macro’s purpose to other users. 

Now it is recommended to declare variables in the code, which in this case is the row count variable – iCountRows. Next, we are set to transcribe the input feature of our macro.

 

Input feature of the Insert Multiple Rows macro

This will assist users to stipulate the number of rows to insert into the worksheet. You need to be 100% accurate, whilst inputting this InputBox part of the macro. Hence don’t rush this part of the code.

The underline is simply there to separate the length of the syntax, and place the rest of the code on a second line, which is the case here. 

The use of an error handling feature is to safeguard the macro, from erroneous inputs such as negative numbers. This piece of code will cause the macro to end; if a negative number is inputted.

The final piece of the macro will initiate the insertion of multiple rows. Just like the InputBox syntax, this part of the code can cause run-time errors for macro developers, because it is quite detailed. Take your time in writing out this code. 

We can now re-enable the three Excel applications, which were previously disabled, whilst the macro was running.

 

Testing the Insert Multiple Rows macro

The macro is now ready to try-out. As we can see, it is working correctly and seamlessly. We will validate the macro again in row 21, but this time we will insert 7 additional rows. 

Hence thanks to VBA, we have the capability to insert multiple rows at once.

Excel Flash Fill in Financial Modeling

This article illustrates how to use the Excel tool, Flash Fill, for the purposes of financial modeling and business planning. Excel Flash will be demonstrated across a number of instances, and highlight how it can be an incredibly powerful and time-saving tool in financial modelling. This is especially so with large data sets.

 

Excel AGGREGATEStrategize Financial Modelling – Flash Fill.xlsb

What is Excel Flash Fill?

Here is a quick explanation of Excel Flash Fill, which is a:

knight_strat x 0.8Time-saving feature (in Excel 2013 and beyond) that fills out data based on patterns of data – thus, it can flash fill once a pattern is recognised;

knight_strat x 0.8Tool that automatically fills data when it recognises a pattern;

knight_strat x 0.8Feature that works best when data is consistent in form and type; and 

knight_strat x 0.8Can be executed on the basis of solely one cell – assuming the data array is consistent

 

Example 1 – Extracting GL Numbers

Excel Flash Fill can be applied to the list of GL accounting codes, in order to extract the integer part of the code. If we type 456 in cell C9, then Excel Flash Fill is able to fill the range and reflect this pattern – that is extrapolate just the integer for each GL code.

Excel in a Flash - Flash Fill - ex 1

 

Example 2 – Combining of Columns

Excel Flash Fill can be deployed to combine the “Type” with a dash, with the “Serial Number”; which is an alternative to utilising the Excel CONCATENATE function. All it takes is one example of this version, and Excel Flash Fill can effortlessly fill down the data range. 

Additionally, Excel Flash Fill can readily be rewound; if you are not happy with the pattern fill. Otherwise, we can accept the Flash Fill.

 

Example 3 – Partial Combining of Columns

Excel Flash Fill can facilitate a need to partly combine the Business Unit name with a corresponding transaction number. Additionally, Excel Flash Fill will house the abbreviated Business Unit name inside square brackets. 

Once more, Flash Fill identifies the pattern and instantly applies it to the other line items. Clearly, an alternative solution to Excel Flash Fill, would be to apply Excel functions to derive the same output.

 

Example 4 – Splitting out bank & account numbers

Once again, Excel Flash Fill will perform a fill down based on the pattern from one cell; so as to split out a bank and account number. As along as the serial numbers are consistent, which they are; then Excel Flash Fill can perform this repetitive exercise in seconds.

 

Example 5 – Combining & Adjusting of Columns

In this final example, we want to combine and adjust the format of the following items using Excel Flash Fill – which pertain to bonus amount by employee. 

We want to add a dollar sign to the bonus amount, then a dash, the employee’s surname, a comma and then the initial of the employee’s first name.

Excel in a Flash - Flash Fill - ex 2

 

Final remarks on Excel Flash Fill

What these examples have demonstrated is the immense value of Excel Flash Fill – particularly in financial modelling.

Assuming our data is consistent, Excel is able to recognise a pattern by leveraging simply one input cell – to fill down an array of data.

Using Excel Consolidate in Financial Modelling

This video will outline a macro-free approach to execute effective consolidation of data, which in this example will sum capital expenditure across three business units for strategic planning purposes.

Excel AGGREGATEStrategize Financial Modelling – Excel Data Consolidate.xlsb

 

Excel Consolidate function

The Consolidate function enables users to summarise and report results from separate worksheets, by consolidating data from each individual worksheet into an aggregate worksheet.

After selecting Data > Consolidate, you will arrive at the following window:

Excel Consolidate window

 

Curiously, Excel Consolidate allows users to aggregate data via a number of Excel function – not just the SUM function.

 

Consolidating Financial Information

We can now aggregate forecast capex from three separate business unit worksheets into our consolidated financial model.

Firstly, the individual business worksheets must be open in the same application of Excel – in order to correctly execute the Excel Consolidate function.

We now have the worksheets for the business units, GammaAlpha, across the entire forecast year by month, and by line item.

And finally the Delta business unit. Note, Excel Consolidate will aggregate these line items, even if the individual worksheets have different time-series data.

 

Excel Consolidate Parameters

Before we start consolidating our capex data by business unit, there are some key points to know about Excel Consolidate:

  • Consolidate allows us to summarise data from multiple sources into one output.
  • Worksheets can be consolidated from the same workbook or independent workbooks.
  • Excel will enable this consolidation to be carried out based on category, formula or position

Category means by line item in the row or column. Formula applies cell references to the source worksheets to achieve aggregation. Position can be applied if all worksheets are the same in order and position.

This capex example will be based on category, because we are using the same row and column labels, in order to match the data into the output worksheet.

 

Executing Excel Consolidate

After selecting Excel Consolidate, under the Reference section, we need to load the respective reference to each source worksheet range.

We select Alpha worksheet first, because it contains the earliest column of data – January 2016. Remember to return to the Consolidate window, and clicking Add to guarantee the successful uploading of this range.

We then clear Alpha’s range in the Reference window, and move onto the other business worksheets.

It is preferable to check the boxes for Top row, Left column, and Create links to source data.

Excel Consolidate actions

As soon as we complete the uploading on these source worksheet ranges, we can simply click OK and Excel will almost instantly generate the consolidate view.

 

The Consolidate View

We have the forecast capex line items down the worksheet. We have the time-series correctly presented across the worksheet. The clear value of the Excel Consolidate tool to aggregate capex line items across months – by business unit file is evident.

Given the link to source option was checked earlier, this schedule is presenting the source cell address reference for each line item. Finally, we will use the AGGREGATE function to sum the high-level total amounts by line item. Making sure to ignore hidden rows.

Excel Consolidate permits users to drill into the granular detail by line item, or hide such detail and merely analyse the high-level view by these line items.