How to turn Excel into a Corporate Application

EXECUTIVE SUMMARY

Who should read this document?

Entrepreneurs, Managers, CFOs, Controllers, Financial Managers, Key Users, Auditors, Tax Advisors, Bank Managers, Financial Analysts, Management Consultants, Business Professionals.

Topic

What if Excel could integrate fragmented spreadsheets to sustain a 360° Corporate Scenario Planning error-free?

What if this solution could be adopted to perform consolidation of plans and budgets across the organization’s subsidiaries and be aggregated with few clicks?

 

A STRUCTURED APPROACH TO SPREADSHEETS DEVELOPMENT

When surfing the web, you may find dozens of articles that warn how Excel can be dangerous and source of risks for the organizations. Excel is not necessarily worse than other applications.

It is just the lack of best practice to help organizations design, implement and sustain spreadsheet applications.

But, how can we sketch best practice for designing a professional spreadsheet?

Here below are some insights from our everyday experience.

    1.   TREAT EXCEL AS ANY OTHER APPLICATION SOFTWARE

    Depending on the complexity and the scope of the spreadsheet, apply a project-oriented approach to the development as if it were an application software (not just a spreadsheet).

    2.   IDENTIFY BUSINESS REQUIREMENT

    Business department should illustrate the requirement to IT which, in turn, verifies the feasibility of the project.

    3.   DESIGN & BUILD A SPREADSHEET PROTOTYPE

    If spreadsheet can satisfy the requirement, IT designs, builds and tests a prototype with the main functions.

    4.   USER TEST

    Once the prototype ready, Key User(s) tests and provides feedback to IT.

    5.   REFINE PROTOTYPE

    IT finalizes the spreadsheet based on Key User feedback by fixing errors, adding and testing ancillary functionalities as stated in requirements.

    6.   USER ACCEPTANCE TEST

    Key User(s) tests and validates the spreadsheet for go live. If tests are positive, then the spreadsheet goes live. If tests are not successful, then IT department reworks the spreadsheet until everything is fine.

    7.   DEPLOY, SUSTAIN AND ENHANCE

    The spreadsheet is released and used in the organization.

    A period of hyper care to troubleshoot post go live issues is then necessary (the length depending on the complexity of the application).

DEVELOPMENT TIPS

The following list of suggestions does not pretend to be exhaustive but simply highlights some useful tricks to develop an Excel application.

The assumption is that the developer has received an adequate training on Excel and VBA (Visual Basic for Applications).

    1.   DESIGN

The design phase consists of creating the model’s most efficient architecture.

When the relationships in a model are highly complex, the specification stage is particularly important as a method for defining how the model will function.

Models of this category are, for example, developed for financial planning or forecasting where complex formulas and additional constraints (e.g. macros operating in protected workbook, utilization of the model by multiple Users across the organization etc.) have a high degree of interdependency and require a strict sequence of logical operations.

The optimal approach is to segregate the activities and organize the model according to best practice in functional areas (e.g. in multiple Excel Worksheets) as follows:

§  KNOWLEDGE

Insert a Worksheet with a Quick Guide that allows the User to focus on commonly used functions and populate a standard scenario with the main settings and values.

Add another Worksheet with Help that gives detailed explanation and guidance for each cell. It should be reachable from any Worksheet via hyperlink and take the User to the section relevant for the Worksheet in scope.

§  INPUT

Clearly identify Worksheets dedicated to the input of values and parameters.

This allows the User to properly execute data entry activities per area without jumping around the application.

Some Worksheet may also contain output cells in a separate range mainly for control reasons (e.g. check total of several cells) or immediate feedback on a specific topic.

For example, after entering unit price for products/services it may be beneficial to know immediately which is the unit gross margin instead of navigating to the output Worksheet.

A separate output range can be dedicated in the Input Worksheets.

What matters here is to keep the output info at the minimum and properly segregated from the input.

§  OUTPUT

Clearly identify Worksheets dedicated to the output of calculated values.

Output can refer to different kind of objects such as Reports, Graphics, Dashboards and Indicators (e.g. KPIs, Ratios etc.).

A Report is a display of organized quantitative and/or qualitative information that represents an event, a process or phenomenon.

The sequence of information does not lead to predefined conclusions because it needs the analyst to apply his own decision and interpretation to the information.

A Dashboard is a graphic representation that displays key measures appropriate to a specific goal or business process.

It shows information graphically and provides visualizations that assist concentrate on main trends, comparisons and exceptions.

It only displays information appropriate to the dashboard’s objective and proposes predefined findings.

A Graph is the visual representation of numerical values through the use of signs and geometric shapes (lines, points, circles, parallelepipeds, etc.).

Displaying information in a well-designed diagram can make numbers more comprehensible and helpful for summarizing and relationships analysis.

It would be beneficial to display the numeric table near to the graph to support the reading of the information.

 

 

Indicators (e.g. KPIs, Ratios etc.) are information expressed as relationships deduced from a company’s financial values and used for comparison purposes.

 

They are useful to concisely help monitoring performances and potential issues, to assess what has been delivered and how managers have behaved.

 

NOTES

It is helpful to add some area for notes to describe the rationale behind inputs and settings or to comments specific outputs.

These areas should be placed within the relevant Worksheet in a dedicated location (e.g. end-right of rows).

 

§  USER MANUAL

A User Manual with a good degree of explanation should be part of the application.

The User Manual detail may change according to the complexity of the application but the structure should look something similar to this:

                               – System Requirements.

     – What is the scope of the application and what can be done.

     – Who are the intended Users.

     – What is the content of the User Manual.

     – What are the conventions such as formats, signs, input and output areas, Excel add-in activated and limitations (e.g. suppression of some Excel functionality such as drag/drop and cut/paste).

     – A representation of the architecture.

     – Functions of the Worksheets and their relationship.

     – How to use the application.

     – Getting started.

     – Typical steps.

     – Create a Scenario.

     – Appendix.

     – Author(s), Release and maintenance info.

 

    2.   USER FRIENDLY

What does User Friendly really mean?

Let’s say that the User should be comfortable with the application scope, the utilization logic, the sequence of activities and constraints, the navigation and have clear in mind what to input and expect from the application.

The more the complexity of the application the more the effort to make the application easy to use and understand. 

 

    3.   NAVIGATION

Large applications need to have a pilot function to take the User to the desired location effortless.

Moving around only with Excel standard tools (cursor, arrows, CTRL+ etc.) can result inadequate when there are thousands of items that require fast identification.

It is advisable to provide the application with hyperlinks, preferably assigned to buttons/object.

Roundtrip buttons are also desirable (e.g. go to Help and back), especially for the most used Worksheets.

 

    4.   COLORS/FORMATS

Colours and formats are very useful to help User understanding immediately the meaning of a cell or a range (e.g. colour A for historical data, colour B for actual data etc.).

A specific attention should be paid to the choice of soft colours that do not stress the sight and, at the same time, allow immediate identification of the field/operation.

 

    

    5.   DATA VALIDATION

Use Data Validation Excel function to restrict the type of data or the value that the User selects/enters from a drop-down list.

This ensures automation and data consistency across the application.

 

    6.   MESSAGES

Error alert, warning messages, conditional formatting cells that change colours depending on the data entry quality will facilitate and streamline the process of data input and analysis of the output.

Large and complex applications deserve to have a set of custom messages that prevents data entry mistakes and displaying actions to be taken.

 

        7.   CALCULATION

Excel Calculation Option is set Automatic by default.

For simple spreadsheets this works well but in case of large and multi-worksheet application it may be advisable to set the Calculation Option as Manual avoiding continuous recalculation.

This is also appropriate when executing massive data input or using a computer which is underperforming.

 

 

   

    8.   MACROS

A macro is an action or a set of instructions that you create to automate tasks. 

Tasks relate to everything from complicated calculations to easy formatting or navigation to other worksheets.

 

ADVANTAGES

Macros provide an optimal way of saving time on predictable, repetitive works and standardizing document formats.

The well programmed macro always executes the task in exactly the same way much faster than manually and error-free.

Any User with limited Excel knowledge can perform the task by running the macro which, in turn, can make tasks that are otherwise time consuming or impossible.

DISADVANTAGES

The macro developer should have a good knowledge of VBA, the programming language developed by Microsoft and a tool used to develop programs that control Excel.

Sometimes, unpredictable errors can occur as it is not possible to assume that the macro works correctly under any circumstance (debugging and technical support will help).

Microsoft is continually upgrading Excel. It may happen that the macro doesn’t work properly with older versions or with a new version of Excel.

In conclusion macros are a great tool if are supporting work time reduction and granting compliant, consistent and error-free deliverables.

A ROI analysis should also help to assess if the macro is worth to be implemented compared to the time saving and efficiency increase for the day-to-day User tasks.

 

    9.   DRAG&DROP, CUT&PASTE

It may be mandatory for some spreadsheet to disable the Cut functionality in order to protect the integrity of the application.

Copy and Paste should be used instead, then clear the source.

Likewise, Drag and Drop Function must be disabled too.

The more likely error could be #REF which prevents the Application from working properly.

 

    10. EXTERNAL REFERENCE / EXTERNAL LINKS

Sometimes information to be connected are in another file, so it is necessary to connect from one file to another through external links.

External links can cause many issues like broken connections, inaccurate information and error messages. Avoiding external links will make the model much simpler.

if you choose to include them, here a list of common reasons for issues:

     Change the names of the file or move it to a different location.

     Change the name of the source file sheet when the linked file is closed.

     Insert rows or columns when the linked file is closed in the source file.

     Email files with links.

 

    11. SECURITY

In Excel there are several levels of protection.

File-level protection. There are two options available:

If you don’t want others to open your file: You can encrypt the Excel file, which is the most common technique used. This basically means you lock it with a password and nobody except you can open it.

If you want to enable Read-only or editing access to different Users: You can protect the Excel file by specifying two passwords: one to open, and the other to modify. You can later share the appropriate passwords with the team depending on the access they should be given.

Workbook-level protection. Let’s say your status report workbook has multiple worksheets, and each worksheet is named after a team member. You want to make sure each team member can add data to their own worksheet, but not be able to modify any of the worksheets in the workbook, whether it be adding a new worksheet, or moving worksheets around within the workbook.  

Worksheet-level protection. Let’s say each worksheet in your status report workbook contains data that is common to all worksheets, like header rows or a specific report layout, and you really don’t want anyone to change it. By protecting your worksheet, you can specify that Users can only perform specific functions in a sheet. For example, you can give Users the ability to enter data, but keep them from deleting rows or columns, or only insert hyperlinks or sort data.

The levels of protection for Excel data depend on the organization’s needs.

 

OUR INNOVATIVE EXCEL/VBA SOLUTION FOR ENTERPRISE INTEGRATED FINANCIAL PLANNING (EIFP)

We, at Crown Consultancy Services, know that it is possible to develop an Excel application with the highest degree of security, data consistency and reliability to elevate Excel to the rank of professional EIFP application.

For this reason, we designed, developed and market ProfitQube™ – the first and the only EIFP software – that brings together the ease of use of Excel, the power of VBA language and the end-to-end business process simulation for manufacturing, trade and services companies within one modelling environment.

ProfitQube™ is a simulation software tool that provides Excel interface for modelling complex integrated financial projections producing IFRS Financial Statements with Charts, KPIs, Ratios and more.

ProfitQube’s mission is to enable SMEs, Large Organizations and Financial Professionals to manage budgets, forecasts and plans for critical decisions.

New Product ProfitQube Consolidation is now available.

ProfitQube Consolidation is a management accounting add-on of ProfitQube which enables the consolidation of Income Statement, Balance Sheet and Cash Flow (Direct and Indirect).

The consolidation process is aimed both for corporate internal control, plan, budget or forecast and for evaluating strategic investments and joint ventures.

Crown Consultancy Services Company is a leading provider of EIFP financial simulation software.

 

 

ABOUT THE AUTHOR

Crown Consultancy Services was founded in 2000 by Dr Gian Paolo Avanzo PhD (Business and Economics University).

Website : www.crownapplications.com 
Email : info@crownapplications.com

He has been involved as Chief Financial Officer / Financial Analyst in multinational
companies (Unisys, Avdel, Chubb, TNT for 10+ years).

He has also acted as Senior Consultant/Project & Transformation Manager implementing SAP ERP and Organization Projects worldwide for Blue Chips (Total, Novartis, Shell, ABB, AbbVie, Kraft Foods, Owens Illinois, Saudi Electricity Company, Fuji and more for 20+ years).

PARTNERS: Accenture, Atos Origin, Axon HCL, Bull, CRA, Deloitte, E&Y, IBM, Infosys, KPMG, PWC, Siemens, Tata, Unilog and SAP.

 

Excel© is a Microsoft Corporation product. All rights reserved.

PROFITQUBE™ is a Crown Consultancy Services Ltd product. All rights reserved.

All other brands or products are trademarks or registered trademarks of their respective holders.

 


 

 

Leave a Reply