If you’ve been in any of our classes you know we love to share shortcuts. The shortcuts can be keyboard shortcuts or anything that will help our attendees do things quicker. Here we talk about the subtotal function (not to be confused with subtotal tool). This function will help quickly aggregate only visible rows if some rows are hidden.
Very often when people sort things in Excel they sort rows. Here is a way to sort columns.
If you’ve ever had to link multiple spreadsheets you known it can be painstaking. Did you know there is a way around this? It’s called 3D formulas. Here is a short video showing you how to use it.
Pivot tables are one of the most helpful tools in Microsoft Excel. I literally use it everyday for the various projects I work on. One of the crucial steps when working with a Pivot Table is to update your source range. Updating a source range is needed when new data is added. This is an easy step to forget when you are working on multiple time sensitive projects. There are two ways to get around this issue. The first technique is to transform the source range into a table. A table is a way to tag a group of cells in Excel. To change a group of rows and columns to a table do the following:
1. Click Insert
2. Click Table
4. Click Ok
5. The source range has been transformed into an official Excel table
The next step would be to create a pivot table and specify the table range. Excel will automatically recognize that this is a special “table”. Now any additional rows or columns you add to the table will be automatically carried to the pivot table. All you have to do is refresh the pivot table. Tables are handy tools which have other benefits as well. We will share another approach on how to automatically update a source range in our upcoming class. Register here.
Recently, I’ve been getting quite a few questions about ways to understand complicated Excel formulas. To put it in simple terms breaking down an Excel formula is like learning another language. At first glance it may seem difficult and daunting but once you have the “Rosetta Stone” it becomes simple and seamless. The following outlines a few tools and techniques which can be used to unlock the mysteries of Excel functions and formulas.
Evaluate Formula Tool
Complex functions and formulas are typically nested within one another. What is nesting? Nesting is the process of combining multiple functions into a long formula. There are pros and cons to creating a large nested formula. The Pro: Spreadsheet real estate is saved. A complex calculation is included in one cell. The Con: A large formula is included in one cell. Large formulas are usually harder to understand and take time to correct or debug. The evaluate formula tool is one of those tools which can help unlock the mystery behind long complex formulas and functions. This tool essentially walks you through each calculation step that Excel takes until a final result is displayed.
- Click on Formulas
- Click on Evaluate Formula
3. The formula in the cell appears in the evaluation window
4. Click Evaluate to watch the first calculation step
5. Click Evaluate additional times to watch each calculation step
6. The last evaluation step shows a simplified version of the original formula
The function button sometimes referred to as FX is a tool which is rarely used but has a tremendous benefit. The FX function is like a dictionary, it gives additional description about a function. In addition when clicked a dialogue box appears which reveals step by step instructions on how to use the function. It’s a huge time saver and it helps pinpoint common errors. In order to use this function to begin to understand a complex function click on the cell that contains the function then on the Fx button. A dialogue box is exposed showing the purpose of the function, the elements needed and the intended result. Below are a few brief steps on how to use it.
1. Click on the Fx button next to the formula bar
Note: The active cell must contain the formula
2. A dialogue box is revealed outlining the following
a. Description of required elements
b. General description of function
c. Anticipated result based on populated values
Understanding the Symbols
Another way to begin to easily understand complex functions is to understand the operators and symbols. Every Excel formula has symbols and each carries a specific meaning. Very often using the wrong symbol can be the difference between accuracy and error. For example there are mathematical operators which are needed for arithmetic in Excel. In addition there are other characters which are used to define worksheet locations etc. In our post titled Excel Characters, we highlighted different symbols used in Excel and there meaning. It’s a great primer to understanding complex functions.
Complex formulas and functions will never be eliminated from the world of Excel. With the tools and techniques mentioned above these formulas and functions can be translated into plain language.
Recently I read this articled titled Excel in Financial Planning and Forecasting: The old same problem for CFOs. This was a great post which summarized the wide spread usage of Microsoft Excel in the area of financial planning and forecasting. In short, “43% of respondents use Microsoft Excel for financial planning and budgeting”. According to the article In spite of the high usage of Excel many survey respondents had some concerns about using this tool. Amongst the concerns were:
- Inability to preform what-if Scenarios
- Inflexibility of Excel
- Not friendly to users
- Lack of skilled staff to perform the task
Many of the highlighted reasons stem from the limited Microsoft Excel expertise available in many organization. Skilled staff should be able to create a financial model that solve the concerns above. A financial model can be built to handle the planning and forecasting process in a seamless way. What is a financial model? Think of a model train. The train model looks like a real train but may not have all of the components. It resembles the real life version in some form or another. A financial model is used to forecast the impact on an organizations finances based on “what-if” scenarios. Internal or external changes can be entered into the model to review the change on a business. The purpose of a financial model is to paint a financial picture, test various scenarios and support fact based decisions. If this tool is built properly it can alleviate all of the concerns highlighted in the article above.
In order to build a sound financial model a few things should be kept in mind. The models that we have built in the past for institutions are the result of a through fact gathering process. The process includes multiple discussions with key personnel, research on the specific industry and review of prior financial statements to name a few. As a result the models are more accurate, flexible and provide information quickly. In order to build a good financial model below are a few things which should be kept in mind:
Build Ability to Test What-If Scenarios
Utilize tools in Excel like drop-downs and links to create the ability to test scenarios dynamically.
Design with Environment in Mind
In order to design with the environment in mind research the industry and key trends. Include this information in the model.
Design with User in Mind
This is a key part of any software design. Interview key stakeholders. Who will be using the model? Who do they report to? What type of information will they need to produce? What types of ad-hoc request did they receive in the past? What is the proficiency level of the primary user with Excel? The answers to these questions and others will help in the design of a tool that is easy to use.
Outsource Design & Internalize Knowledge
Many organization lack the skilled staff to build such a tool. In many cases it is ideal to outsource the development of the tool but internalize the knowledge associated with it. For example, often after we develop a tool for an organization we provide training and documentation. After this point key personnel are able to modify the financial model as the business changes or products\services are added.
Microsoft Excel has become the software of choice for planning and forecasting primarily due to its flexibility. This same reason is a source of concern for many organizations which lack the skilled staff. Outsourcing the design of a financial model can increase accuracy and speed up the planning and forecasting process.
Over the past few weeks I’ve been attending FastTrac Growth Venture©. This is a entrepreneurship program designed by the Kauffman Foundation to help revenue generating small businesses grow to the next level. During one of the sessions each business owner took some time to dive into there revenue patterns. Understanding revenue patterns is a critical piece of every business. A good understanding of this line item can help business owners plan for cyclical slowdowns, identify high volume customers or lucrative products\services. Below are a few things you can implement in your business right away to begin to understand the revenue numbers:
Create a Cash Flow Analysis – Cash flow is the movement of money in and out of your business. Analyzing your cash flow can help you plan for late paying customers and slow seasons. Analyzing your cash flow in advanced can help your business stay away from costly stop gap measures like a line of credit. An analysis tool can be created in a tool like Excel or by utilizing a stand-alone package like Quickbooks.
Create Revenue Slices – There are different ways to cut your revenue to discern patterns. For example as a small business owner you can cut your revenue by client demographics, product\service type or various time horizons to name a few. Each slice tells a different perspective, together they tell a story about your businesses revenue. These slices can be setup with a simple table in Excel. The addition of trend visualization tools like sparklines can assist with identifying revenue patterns.
Every business owner should understand the numbers, especially the revenue numbers. In addition business owners should develop a plan to measure and provoke growth. With the help of some of the techniques highlighted here small business owners can soar to new heights in revenue.
One of the benefits of Excel is that it saves time and increases productivity. Quite often you can build something once and reap the benefits for years to come. One way to increase productivity is to create dynamic reports. Dynamic reports are built to give users the ability to change certain variables quickly. This automation eliminates the need for data entry or constant redesign. Dynamic reports often employ the use of functions or other tools (ie Macros, VBA etc.) . Below is a three step process I typically take when creating dynamic reports for clients 1) Develop Template 2) Gather Data 3) Add Dynamic Techniques
A template is simply a spreadsheet formatted in a way that presents information clearly and concisely. This template is then reused on a periodic basis. In order to develop a useable template, start by writing a list of data that you absolutely need. After a brief list is created, go through a brainstorming trial and error process of template layouts. Choose the layout which communicates your message most clearly. It is a good practice to get outside criticism on the final format.
After the template is created, the next step is to compile all the data points needed for the template. The final output desired, which is the template, guides the data that is needed. The data points should be organized into a concise compact table on one spreadsheet. In many cases after getting more information about your data such as the level detail and generation frequency “rework” may be needed to your template. You may find out that some critical elements are unattainable or organized in different way. These types of challenges may force a rework in the template format. Gathering data and developing a template is an iterative process.
Add Dynamic Elements
The final step would be to add dynamic elements. The elements chosen are dictated by the format of your template or organization of your data. Do you want to have the ability to filter through calendar years? Do want to be able to see the detail? These questions help determine the types of dynamic tools you would use. Some of functions I use frequently for this are Sumif and Countif. These functions help to quickly populate reports without manual intervention. A few other helpful tools are Visual Basic for Application (VBA) and Pivot Tables.
Time is precious. Business owners and professionals are overworked and need to get things done quickly. Implementing the outlined process will lead to time savings. This new found time can be used in other productive ways to grow an organization.
With the government shutdown still in progress and the Affordable Healthcare Act at it’s core, I wanted dive into the numbers further. How many people are impacted by the legislation? How is it distributed by state? I went to Excel (of course) to see the number of uninsured and eligible by state. Based on data from the Department of Health and Human Services It turns out California, Texas and Florida have the greatest number of people affected by this legislation. These states in total account for close to 14 million people or 33% of this particular demographic.
What is the financial implications by state? The next few blogs will explore this a bit further.
I recently attended a NACUBO conference in Georgia discussing various topics around planning and budgeting. One of the sessions which I found particularly interesting was called Strategic Budgeting and Financial Forecasting. During this session an Excel based tool created by Public Financial Management (PFM) was demonstrated. This dynamic tool gave those involved in planning the ability to project profit and loss statements years in advanced. A tool such as this helps the administration plug possible gaps and capitalize on surpluses. This same concept and tool is what we bring to small business owners. The power of Excel to organize data and its interrelationships in one place to give a picture of how an organization works. This is referred to as a financial model. This tool is then used to support decisions and test different scenarios. How are you using Excel in your organization? Does it help you make business decisions?