Downloads - How to Buy

• News
• Events
• Subscription
• Newsletter
• Blog

Download Free Trial
Register for a Webinar

Blog

2 minutes to customize your calculations!

November 22, 2019

In this article, you will find how to customize your Invest for Excel calculations, so it better fits your needs. Tips and tricks described below will help you tailor your calculations. Learn more about:

1. External referencing - create an Input / Output sheet
2. Layout - make it easier to read
3. Default formulas – define how specific indicators are calculated

Explore these simple techniques to get the most out of your Invest for Excel software!

1. External referencing: when creating your financial model, sometimes we may need to pull data from another worksheet or from another Excel file. It is extremely easy to do in Invest for Excel – just create an external cell reference (a link) between the worksheets within the same or even another calculation file (workbook). Since Invest for Excel resides within standard Excel, you can use all Excel functions – there is no compromise! It becomes very handy when you want to create the so-called "input" and "output" sheets in your calculation file.

Input worksheet: E.g. you receive some specific data about the cost of machinery (investment items), that you need to use in order to create your financial model. Add a new worksheet within your Invest for Excel calculation file (you can name it as "Input" sheet), paste the data and use external referencing to link the data to your Investment table:

Example of an Input sheet

Output worksheet: In a similar way, you can create an Output worksheet. Imagine if you need to create a table with very specific data and layout for your own quick analysis. Add a new worksheet within your Invest for Excel calculation file (you can name it as "Output" sheet), create your table and pull the data from Invest for Excel calculation table to your recently created table, by using external referencing:

Example of an Output sheet

2. The overall layout of your calculations:

You can easily tailor the overall layout of your calculation with the help of the "Hide/Show Rows" button and "Outlining" dropdown control.

A. Each Table/statement has a "Hide/Show Rows" button, which allows hiding unnecessary rows. With the help of this feature, you can easily hide rows, which are empty or have "zero" value as well as other parameters/ratios which you don’t want to see:

Select empty rows - hide the default rows, which you didn't use.
Select zero rows - hide all rows that have "0" values.

B. Each table/statement in the "Calculations sheet" of a calculation file has an "Outlining" dropdown control. This feature helps you to modify the outlining levels. There are 5 levels of outlining allowing easy modification of the overall look of your table from less detailed (1) to more detailed (5). E.g. The special structure of the Income statement allows creating over 300 000 rows to reflect your income and cost items. You can model as complex and detailed calculation as you need and manage the way how it looks, by modifying the outlining level (1-5) in the dropdown control. You can also expand separate row groups to see the details at any time by toggling the "Flip button" next to the rows.

C. If you want to edit the program’s default names of the rows (in one or multiple languages), press the "Hide/Show Rows" button and select the "Edit Row Text" tab. Here, you can also filter out languages, which you don’t use. E.g. If you prefer to use the "Revenue" or "Sales" term instead of "Income" or "Profit and Loss statement" instead of "Income statement" – just rename those rows here.

3. Financial ratios: Use the Options dialog window to define how financial ratios are calculated.

Go to the Invest for Excel ribbon menu --> Other --> Options to define how financial ratios are calculated. In the Options dialog window, define whether you want to calculate:

• Mid-year discounting (read more here)
• Net assets, based on Average balance / Opening balance / Balance at the end of the period
• RONA based on EBIT / NOPAT / Net income for the period + financial items + appropriations
• EVA based on EBIT / NOPAT / Net income for the period + financial items + appropriations
• NOPAT as EBIT*tax percent

Select the Other Options tab and change other defaults:

• Include profitability calculation based on FCFE
• Update analysis charts automatically
• Picture copy: show "Picture added to clipboard" message
• Use Offset formulas for specification rows

 

Contact DataPartner for help, if needed! We have been helping our Customers to create calculation templates for over 30 years!