The Data Group tool in Excel is an important feature to implement in a best practice strategic plan. It is a safer, more intuitive alternative to hiding rows or columns, which will often be overlooked by other model users. For the purposes of strategic planning or financial planning & analysis (FP&A), the Data Group function in Excel will enhance the financial model in a number of ways.

The following financial model is illustrated in the below YouTube video, and will illustrate the practical benefits of Data Group in financial modelling, in terms of:

• Hiding the time-series details on a worksheet,
• Hiding the financial model detail i.e. business unit financials,
• Hiding the innocuous calculations i.e. Opening Cash at Bank + Retained Earning = Closing Cash at Bank,
• Hiding irrelevant elements in parts of the Plan i.e. time-series actual years in a forecast schedule worksheet,
• Hiding error and alert checks, and
• Creating a more high-level snapshot of the Plan for executives and management.

 

Strategize Financial Modelling – Strategic Plan (SOLUTION).xls

 

❶ Time-series details on a Worksheet

The time-series provides important information for the model’s users. Further, it functions as precedent cells in certain planning formulas in the best practice financial model. Rightfully, this information does not need to be displayed, and hence it should be grouped up in the spreadsheet using Data Group.

 

❷ Financial model detail

Some strategic plans will entail multiple business units or subsidiary companies. This detail will be pertinent for some stakeholders, but executives will unlikely require this granular detail with the financials. The model’s detail should be grouped with Excel’s Data Group function.

 

❸ Innocuous calculations

There are some items such as net fixed asset values, or closing cash at bank on the Balance Sheet, which require behind the scenes calculations to compute their final number. Obviously the final strategic plan does not require this in-depth information, and thus Data Group should be applied to these calculations.

 

❹ Irrelevant elements

A best practice financial model employs a uniform time-series. Evidently the columns with the actual periods on the forecast assumptions worksheet are redundant. Therefore they can be grouped with Data Group to improve the usability of the strategic plan to users. Other irrelevant items could be blank, defined cell names for business units or corporate valuation inputs, which the strategic plan currently does not require.

 

❺ Error and alert checks

These checks are a mandatory piece of a best practice financial model. The addition of checks in worksheets is highly subjective and at the province of the model developer. Hence some worksheets will contain more checks than other worksheets. For presentation purposes, it is wise to use Data Group to group the individual error checks.

 

❻ High-level snapshot

Finally the application of grouping improves the aesthetics of the financial model. Especially for printing purposes or presentation to executive stakeholders, who are primarily concerned with the high-level detail and bottom-line numbers.