Posted by sebastian.zang on October 8, 2012
There are many dashboard templates available on the internet and we’ve gone through most of them to give you the “Top 3 Dashboards Designs” that we think would be useful.
Each template has a screenshot followed by a short description with the website and download link (if available) mentioned below.
MS Excel Dashboard #1
- Very detailed dashboard, good mix of colours and charts making it easy to read and understand.
- In depth display of performance per quarter, with summary and comparison for “Salesman” and “Region”.
- Key Performance Indicators (KPIs) clearly visible to give an overall picture.
- Option to customise a data range, to display charts for “Customer Type” and “Product Sales” for that period.
- Very neatly arranged dashboard, with a good play of colours which makes it easy to read.
- Summary of quarterly and yearly data shown.
- Data to be displayed can be filtered by “Customer Type” and “Region”.
- Good mixture of line and bar charts to compare averages.
- Also has a section “The Big Picture” that gives an overall representation of the sales trend for that period.
- Dashboard neatly displayed, with good use of colour.
- Good use of box plots and trend lines for comparing data (half-yearly and yearly) across “Customer”, “Product” and “Region” for each sales representative.
- Bar chart can be rotated to enable easy comparison.
- Emphasis given to sales representatives performance and analysis seen from that point of view.
- Data represented in the form of horizontal bars, rather than vertical are easy to read especially while making a comparison. Trend lines on the other hand, would give a better picture of data across many years.
- A section with Key Performance Indicators (KPIs) or charts displaying the overall performance during a certain period, is also essential to get an idea of the trend in which the business is heading.
MS Excel Dashboard #2
MS Excel Dashboard #3
From the examples above, it is important to note a few essential features that go into designing a good sales dashboard.
Note: The templates in this article were taken from www.chandoo.org.
We hope this article was useful and hope to see you back for more on dashboard designs in MS Excel.
Posted by sebastian.zang on July 31, 2012
It’s a major source of incorrect results: Numbers formatted as text. For example, mathematical functions won’t work anymore for instance, after importing data into Excel from sources such as the internet. Check out the VALUE function!
There are times, when you import data into Excel from other sources such as the internet or other applications, data types are inadvertently changed. Often, Excel stores numeric data as text which is not desirable: data in these columns cannot be used in mathematical or statistical functions such as SUM, AVERAGE, etc. and use of these functions results in errors. We will look at a method to convert the text to numbers using the VALUE function. Subsequent use of mathematical or statistical functions will result in the correct result.
Here is a quick tutorial on the use of the VALUE function to convert text to numbers.
Text or Number? - A quick check
When a column in a worksheet holds text, by default the values are aligned to the left. If they are numbers, then the values are aligned to the right by default. This is a quick way to identify the data types.
In the image above, both columns are aligned on the left side because they are formatted as text. The column labeled Units should have been numeric data.
Takes the value of a cell in text format and returns the value as a number, where Text is the data to be converted. This can either be a cell reference or the actual text in double quotes.
- Enter the data into Column B and C as shown above – as you enter each number in Column C, type a single quote ( ‘ ) in front of the value and it is turned to text.
C2 – ‘123
C3- ‘456 etc.
- Now type the formula =SUM(C2 : C12) into cell C13. Instead of the expected sum, you will get an answer of zero. This is because the values being added are text and thus ignored by the SUM function.
- Click on cell D2 and type the formula =VALUE(C2). This function is one of the Text functions of Excel. The numeric equivalent of the cell value in C2 will appear in cell D2, and thus it should be right-aligned by default.
- Copy the formula to the remaining cells in that column up to D12 by dragging on the fill handle.
- Now type the formula =SUM(D2 : D12) into cell D13. Since the values being added are numbers, the correct answer will be displayed.
I hope that this tutorial provided a satisfactory answer to the question, as to how to quick-fix wrongly formatted numbers in Excel. However, from an IT point of view one question is even more intriguing: How come you have wrongly formatted numbers in your Excel spreadsheet in the first place? We strongly recommend that you take a closer look at the import process, that’s generally where this issue arises. You’ll find some useful advice on state-of-the-art import methods on our website in the section on Excel Reporting.
Posted by sebastian.zang on August 29, 2012
It’s about user comfort and data quality: Using in-cell drop down lists prevents wrong manual data entry and enhances substantially usability of entry masks or reportings. Here’s a quick guide on how to implement this!
I think you got it: if you pick a value from a list of options from a drop-down box, this is known as in-cell drop-down lists.And what about conditional in-cell drop-down lists? Well, this is where the drop-down list varies depending on the selection in another drop-down list. We’ll show you how to implement this.
Consider the scenario where we have a list of States (I decided to pick the top 3 US states in the alphabetic list : Alabama, Alaska and Arizona), a list of 3 cities in each of these states, and a list of 10 zip codes in each of these cities.
Columns B, C, and D contain drop-down lists. If you select Arizona in the drop-down list in column B, only cities in Arizona will be displayed in the drop-down list in Column C. If you select Alaska, only cities in Alaska will be displayed in the drop-down list in Column C. Similarly, when you select a city in Column C, only the zip codes of that city will be displayed in Column D. Once a zip code is selected, the corresponding population of that zip code is displayed in Column E.
To create conditional lists, complete the following procedures.
Enter the data
|1.||First, we’ll create the table as shown above, in columns B,C,D and E with only the Column titles in Row 2 and no data.|
|2.||Next we will create some lists with names (named ranges).|
|a. First create a vertical list of states :
|b. Next we will create horizontal lists of cities for each state:
|c. Now, we’ll create vertical lists of zip codes for each city from Columns G to O. Some cities have fewer zip codes due to lower population. Create named ranges for all cities automatically by selecting the entire range and pressing CTRL+SHIFT+F3 and clicking OK.|
Create the drop-down lists
|1.||After creating the lists and defining the names as in step 2, we are ready to create the drop-down lists.|
|a. First we will make an in-cell drop-down list for State.|
|b. Next we will make the first conditional in-cell drop-down list for City.|
|c. Repeat the above process with cell D3 to make the second conditional in-cell drop-down list for Zip code. This time the data validation dialog box should use the formula =INDIRECT($C3).|
|d. We are done creating the drop-down lists.|
|2.||To make it more useful, I have added a last column Population that uses a VLOOKUP function to display the population for the selected Zip Code in Column D.|
|Hope you enjoyed this tutorial on creating conditional in-cell drop-down lists.|
One last hint: If your data is dynamic (say, the number of cities keeps changing, and/or the number of states keeps changing based on an automatic import from a database) it’s also possible to have an automatic update of this named ranges. Please check on our Website for our offering regarding Excel macros.
Posted by sebastian.zang on October 18, 2012
Cash Flow forms one of the most important key data in any company control. Rarely does the Cash Flow work completely during the first derivation with the help of the indirect method. Clearing these differences can be time intensive. Here is a procedure in MS Excel, with which your Cash Flow will always work. Always!
Cash Flow in MS Excel – The traditional method
The Cash Flow is determined generally through the indirect method, whether it be as per the German financial accounting standards (DRS) or international GAAPs [above all IFRS, US-GAAP]. For this proceed typically as follows [simplified] :
… Start with a value from the profit- and loss calculation, mainly the EBITDA or the annual surplus.
… On the one hand payment outflows are added, which are not reflected in the profit- and loss calculation [e.g. change in the stock].
… On the other hand non-cash balance sheet movements are eliminated [e.g. change in the reserves].
If now after the determination of the Cash Flow, it does not work [i.e. the change in the payment instrument does not correspond to the change in the item cash of the statement of application of funds ], then look for actual factual situations, which are not yet considered – for example payment outflows, which are hidden somewhere in the balance sheet. This can be a very long and frustrating process.
Now get to know an alternative method, which you can implement easily in MS Excel. This method leads to the EXACT same result, is clearly more time efficient and produces a larger transparency about Cash Flow-relevant matters in your Excel Tool.
Cash Flow in MS Excel – The simple method – Executive Summary
We describe this method, which can be applied very easily in MS Excel, first in an Executive Summary. Further this method is also illustrated in the example.
In short, the method functions as follows as explained in the following diagram :
- Firstly, map the Cash Flow scheme in one column of MS Excel – as it is required for presentation [in an Excel Reporting] for your firm.
- Enter a SUM formula in the next Excel column, which extends over several columns towards right.
- Now distribute all items of the statement of application of funds [except the item “Cash”] along your existing Cash Flow items. That is, assign each item of the statement of application of funds to one of the Cash Flow items. For example, item in the statement of application of funds “Change in stock” to the CF item “Increase/decrease in stock”. Then assign the change in the cash on hand to the CF item “Change in the finance instrument stock“.
- In the end you will get in your MS Excel Tool initially, a rough Cash Flow-Derivation scheme, which works completely. Then it is derived completely from the statement of application of funds.
- In the next step, refine the Cash Flow-Scheme in your Excel Tool: Take for example the “interest cost”. This is required typically in CF-Scheme, but did not assign any values, interest cost is not derived from the statement of application of cash funds – the interest cost comes from the profit- and loss calculation. So select a column from the area “Reclassification“[compare mapping] and ADD the interest cost in the row of the relevant CF-Item [e.g. in the item “Interest“, where it is added to the annual surplus to reach operative CF]. Then SUBTRACT this interest cost again in the same column in the functionally suitable CF item, e.g. in the item “Cash interest cost”.
- The reclassification method, which you have used in the Excel Tool for the interest cost, can be used in the same way for all other relevant facts. They include : “Depreciations”, “Taxes”, “break up of reserves”, “Interest earnings” and others. The number of required reclassifications depends on the CF scheme of each firm.
- Through the Excel SUM formula [compare mapping], the values of the statement of application of funds and also the “Re-classifications” in the CF-Scheme are mapped. In terms of Mathematics the reclassifications do not change the result of the statement of the application of funds calculation at all: Then everything, which is added, is also deducted at the same time. The net effect of the reclassifications on the result of the calculation of the application of funds is always equal to zero. This accounts for the simplicity and even infallibility of this method, which can be implemented in MS Excel very easily.
This principle is illustrated briefly with an example.
Cash Flow in MS Excel – The simple method – An example
The following diagram shows the section of a MS Excel Reporting Tool, in which this method is used. Please note: It is only a section: Not all visible sums here are completely obtained from the visible values.
Now you have first, in Excel column range A to C the Cash Flow-Items, which you want to map [for your Excel Reporting or your Financial Model]. In column L, the items of the statement of application of funds are distributed. The annual surplus [“JÜ”], with which this Cash Flow-Scheme starts, is obtained for example from the change in the equity capital items [possible disbursements or increase in capital can be mapped through the “Re-classifications” in Excel Tool ].
In column J of this Excel worksheet, the total is formed : Here relevant items of the statement of application of funds and the reclassifications are added [here for the CF-Item “Intangible depreciation” in row 7].
Now let us look at the reclassification “Depreciations IVG with intangible assets” in column O : The cell O7 is linked through linking in MS Excel with the relevant GuV-item. This value is included in the CF-item “Intangible depreciation”, and of course through the considered Excel SUM formula. The same value, now with negative sign, is again found in the cell O20. Technically Excel has a linking with negative sign [so with the formula: “=-O7”]. Technically viewed, this corresponds to the value for the CF-item “Increase / Decrease in intangible assets”.
Let us take a look at the reclassification item “Taxes” : The CF-item “Taxes” is derived first from the corresponding GuV-item, wherein it is linked with a simple Excel formula. The value with negative sign, from functional point of view, should be assigned to the item “Tax payment” [an item, which here is concerned with other reclassifications, which is not completely visible in the section visible here].
In short: With this method in MS Excel, ANY Cash Flow-scheme can be mapped … and produces at the same time an excellent transparency about different facts, which go in to the Cash Flow calculation.
This method can be used in MS Excel Reporting systems or in MS Excel Financial Models or Planning tools – and is also used there. By the way we offer regular free Webinars, which handle methods for implementation of functional requirements in MS Excel and other Microsoft Technologies. Even this method was already presented in one of our webinars. Take a look regularly at our web site, where you will find the webinars on the development of efficient MS Excel Tools.
Posted by sebastian.zang on October 18, 2012
Introduction – Modeling of Loan in MS Excel
In our blog on the topic “Financial Models in MS Excel – Concept”, we had already pointed out to the increasing significance of Financial Models and simulations, which is obtained from an increasing volatility of the entrepreneurial general conditions. In this article we would like to delve into an aspect of the structuring of Financial Models in MS Excel, namely the modeling of loan. Financial models are necessary frequently in the context of refinancing, acquisitions in connection with loan borrowing and similar corporate situations.
This article is devoted above all to the question, how loans can be mapped flexibly in an MS Excel Financial Model. Which parameters are basically relevant for the topic “loan”? How can the cash-flow effects be mapped?
Parameterization of Loans
Step one: Use in those cells, where the parameters for loans are made, names at any cost. In MS Excel 2010 [like in other Excel-Versions] this is child’s play: Choose a cell, for which you want to assign a name. Then go to the Tab “Formulas”, click on the “Namemanager” and assign meaningful and unique names.
Why names? Quite simple: If you reference these parameters in the following text, you get meaningful names in the formulae. These Excel-Formulae can be validated more easily, besides you will get quickly an overview of the actual functional processing logic, which are actually implemented with an Excel-Formula.
Let us start with an example for parameters of a loan:
You will see here for example, for Tranche A of a liability structured into different tranches, the relevant descriptive parameters:
- Loan amount at the start of the Financial Model
- Repayment method, here you can choose between amortization [current amortization] and Bullet [final maturity loan]
- The interval for repayment [of course relevant only for the repayment method “Amortization”]
- Date of final maturity
- Underlying interest rate [LIBOR or EURIBOR]
In another scope of your MS Excel Tool, which captures the TIME DEPENDENT parameters, enter those parameters, which change in course of time. This includes quite obviously LIBOR- and EURIBOR- rates, the spread and other details, which you can recognize from the above screenshot.
Calculation of relevant key data for loan in MS Excel – Amortization
Here you will see, for the loan, whose data path we follow up in a sample MS Excel-Tool, that range, in which the captured input values/parameters are processed.
On the one hand you have here the category “Repayment”, on the other hand a category “Interest”. Here basically all relevant data is determined, which you need for the PnL, the balance sheet and the Cash Flow – in short: for completely integrated financial data.
Let us take up the first row [“Repayment Indicator”]. It replies to the question, whether for a specific quarter a repayment should be made or not. This value is obviously dependent on the following settings, which you can do in the model:
- Amortization payments over the period OR a final maturity amortization.
- For the amortization loan: Frequency of amortization payments, so 3, 6 or 12 months.
The Excel-Formula for this appears as follows:
One thing is clear here: The use of the NAMES in MS Excel is unbelievably helpful for more complex formula constructions. The functional logic of the Excel-Formula can be translated [can be easily traced] as follows:
The Excel-Formula goes into the THEN-condition of the first IF-Formula [so “1”], if it concerns an amortization loan. The check, whether repayment is made in a specific period, to which the Excel-Formula is assigned, is made quite easily:
- The name “repay_period_A_USD” contains the number of months, in which repayment is made … so 3, 6 or 12 months.
- In this formula we will do the following: simple procedure is applied to determine whether repayment should be made in a specific month or not. Namely: From the start of loan disbursement, increment simply the number of months … this is done in a separate row per loan, this Excel-Formula only points out to it. And then the following simple rule of proportion is applied: For example if you repay every six months, then you will get interest payment dates always if this number of months forms a multiple of these: 6 months, so 6, 12, 18, 24, etc.
And the REST-Formula in MS Excel helps you to check this: This Excel-Formula determines whether the result of a division is even numbered or odd numbered… during divisions by 6, 12, 18, etc. the result of the division is of course even numbered and that is why these cases deal with an interest payment date.
The OTHERWISE-condition in the outer IF-formula … here “2”…checks, whether the date of final maturity has come for the final maturity loan. Even here the name “final_mat_A_USD” in MS Excel is used nicely in a transparent manner … namely the date of the final maturity.
From the frequency in our example you see [“1, 0, 1”], that the repayment period is 6 months.
The remaining formulae for the determination of repayment sums are quite simple Excel-Formulae, which we need not consider now. More interesting would be the area of the determination of interest-PAYMENTS.
The calculation of relevant key data for loans in MS Excel – Determination of interest
For the sake of overview we reproduce once again the section from the calculation area from our sample MS Excel Tool:
The determination of interest in the row ”Total interest rate” is again no problem, which can be taken from the areas in your MS Excel Tool, where the basic specifications were made. Let us take a look at the determination of interest… thereby we consider first the interest relevant for the PnL …
The Excel-Formula is completely simple:
- One would take the loan amount, for which the interest is to be charged, at the beginning of the period and annual interest rate.
- One would take the number of interest days, with which the interest amount is determined for a specific period. In our example they are quarters. To determine the interest days we use the following Excel-Formula:“YEARFRAC”. What does this formula do? It “converts the number of days between start date and end date into fractions of years” – just the one, which we need for the determination of interest during a fiscal period.
Now you have to pay attention to one factor: In order to map the interest quite exactly in a financial model, relevant loan agreements have to be checked and assumptions should be made, which interest-Method or interest usanceis applied. There are different methods here; an overview is shown in the upper screen shot. To mention a few of them:
- In the German and European interest method 30/360 each month is counted with a maximum of 30 days.
- In the interest methods act /360for example … here the number 2 … the actual number of days is used and indeed in combination with 360 year days.
Calculation of relevant key data for loans in MS Excel – Cash Flow
To determine the cash flow in your MS Excel Tool following factors must be considered:
- FIRSTLY: Payments do not accrue in each period … a payment flow occurs on the interest payment dates (!)
- SECONDLY: Only very rarely do the interest payment dates coincide exactly with the end of quarter or end of half-year… we shall see in detail, how it looks and how it can be designed in your MS Excel Tool.
In the above screenshot you will see once again the relevant calculation area within your MS Excel Tool in overview:
- The row “Interest cost (PnL)” determines the PnL-Perspective
- The row “accrued interest (balance sheet)” considers the accrued interest, which is non –cash item and is indicated separately in the balance sheet
- The row “Interest effective for payment (CashFlow)” considers the CashFlow-Effect … and for this (as per the indirect method) the relevant data from PnL and balance sheet must be available. We have mapped this simple rule of three of integrated financial data in MS Excel Tool
In the following screenshot that Excel-Formula is mapped, which we need for the calculation of the Cash Flow-relevant values:
Let us first consider the following case: Within the period there is actually an interest payment date:
- In the relevant formula we reach the second row, quite simple logic: If the repayment indicator is on [“Repayment indicator”] “1” (lf an interest payment is due ), then only the second part of the Excel-Formula is relevant through the reduced If-Formula.
- There you recognize the Excel-Formula “YEARFRAC” again. If now the interest payment date does not coincide with the period end, then the difference in days is determined between the ACTUAL interest payment date and the end of the quarter and the plan period. That’s the part, which is NON cash item and is shown separately in the balance sheet. The interest payment date is again a parameter, which is captured within the MS Excel Tool – compare further in this article the capture range for parameter… You will see clearly the linking logic.
Second case: In the period there is NO interest payment date. This case is clearly simpler:
- Simply add the interest cost [corresponds to the PnL-value] with the accrued interest cost of the previous period. Complete!
The Excel-Formula for the CashFlow is again simple, however we will not go into the details now…
This blog has [in short] other articles with proven practical tips &tricks for MS Excel Financial Models. Just in connection with planning- and simulation processes even the use of the FreeWaredatabase PALO is suitable, which is marked by an excellent integration in MS Excel. Based on this multidimensional database we developed a technical approach for the purpose of simulation, which is presented to you in a short Demo-Video. This video is available in our homepage MS Excel Financial Model or in our YouTube Channel YouTube Channel.