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.
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.
– 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.
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.
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.
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.
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.
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.
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.
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.
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 .
For this reason, we designed, developed and market ProfitQube™ – the first and the only EIFP software– that brings together the ease of use of Excelthe power of VBAlanguage 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 SMEsLarge OrganizationsFinancial 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).
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.