Monthly Archives: October 2013

3 Steps To Creating Dynamic Excel Reports

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

Dynamic Reports With Excel

 

Develop Template

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.

 Gather Data

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.

Uninsured and Eligible by State – The Truth

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.

 

Uninsured and Eligible by State in Excel

 

What is the financial implications by state?  The next few blogs will explore this a bit further.