Excel 2010 – PowerPivot

If PowerPivot is the only new feature that Microsoft Excel 2010 has added, I would still go ahead to upgrade to Excel 2010.  It is especially valuable if your organization does not have business intelligence (BI) or online analytical processing (OLAP) tool.

PowerPivot is a self-services BI tool which enable Excel users to perform powerful data analysis using the skills they already have.  If you know what BI is, you can skip this article and wait for my blog on PowerPivot series which would cover more technical information and hand on demonstration.  I am only going to give a high level review of what BI and PowerPivot are in this article.

What is BI?

Before I answer your question, let me ask you some questions first:

  • Do your decision makers have to wait for their reports?
  • Do they receive consistent information, no matter who prepare the reports for them?
  • How many places do you need to go to get the answer?  Sometime, I don’t even know where to find the information.
  • How often is data rekeyed?
  • How much information do you need to manually aggregate?
  • How much data needs to be manipulated to get to an answer?

If you would like to improve your information generation processes, i.e. decision makers can have their reports quicker and provides a "single version of truth", then BI may be something you have been looking for.  You are not alone.  According to Gartner Inc., BI is the #1 priority for Chief Information Officers since 2006.

So what is BI really?  BI allows you to store data into a central repository from diversified sources, e.g. customer information, finance, human resources, inventory, or even from internet.  Then you can build report, dashboard or score card from a "single version of truth" source.  You can also quickly slice and dice data to conduct in depth analysis or look at it from different viewpoints.  Management can make prompt and informed decision and your organization would become highly efficient!

You have all the information at your fingertips!

alt  alt

However, there are challenges to implement BI.  Especially if your organization is a small business, or your BI solution is for personal or workgroup use, then you may not have the technical knowledge, budget, or resources to implement a BI solution.

What is PowerPivot?

Microsoft has designed PowerPivot for business users.  It is integrated with Excel, software that most of us are very experience and familiar.  It can run on your desktop (or laptop) PC; so you don’t have to buy new server to implement it.  You can download it FREE from http://www.powerpivot.com/download.aspx.

It is a data analysis tool that can transform huge amount of data from virtually any source into meaningful information that management needs in seconds.

alt

If you want to find out more about PowerPivot and see if it can optimize information channels within your organization, then stay tuned.  I am going to discuss more about PowerPivot in my blogs.

 

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Excel 2010 -Solver

Solver is not something new to Microsoft Excel; it was first introduced to Excel 3.0 on an OEM basis in 1991, i.e. 20 years ago.  However, I noticed it was one of the best kept secrets in Excel so I am going to formally introduce it to you as a NEW feature.

What’s solver?  It is a what-if analysis tool that you can use to do planning, budgeting, projection and optimization.  It consists of mainly 4 components:

  • Objective – you specify your goal in a cell

alt

  • Variable – cells that solver would keep adjusting in order to reach the objective

alt

  • Constraints – there may be constraints to your problem

alt

  • Method – How do you want to solve the problem

alt

Solver is not enabled when you first install Excel, you must first enable the Solver Add-In. To Enable Solver

  • Click the File Menu and choose Options and you would see Excel Options dialogue box.

alt

  • Select Add-Ins from the left sidebar and then hit the GO button next to Manage Excel Add-ins at the bottom.

alt

  • Check the Solver Add-in option and Click the OK button.

alt

  • Now you would see the Solver group under the Data tab.

alt

Example – Use Solver to find how much you need to contribute for your retirement You are 30 years old and earn $60,000 a year.  Your goal is to retire at age 65 and able to receive a retirement income that is equivalent to 70% of your salary at age 65. Assumptions:

  • Your salary and retirement income will increase 3% a year,
  • Interest rate is 10% until 55 and then 5% afterward

Question: What is the minimum contribution percentage that you must deposit to your retirement saving account? I prepared the following Excel worksheet.

alt

I don’t know the retirement saving percentage, so I just put 0% (I1). How to setup Solver? You can start Solver by clicking on the Solver icon on Data tab.

alt

The Solver dialogue would be shown.

  • The objective cell is $I$1 and you want to Minimize it.
  • Changing Variable Cell is also $I$1.
  • Make sure the balance for Age 65 – 85 is >= 0, so you setup the constraints to be $F$38:$F57 >= 0.
  • Choose Simplex LP Solving Method.

alt

After I click the Solve button, you would see the Solver Results dialogue.  Click the OK button.

alt

Here is the result.  You have to save at least 12.19% of your salary into your retirement saving.  You can verify the result by checking the balance at Age 85. It is $0.00.

alt

This is a very simple model. I only spent a few minutes to formulate the worksheet; but you can see it took me no time to setup the Solver. Financial planners / insurance brokers have software to do planning for their customers; but there would be situation that their software doesn’t know how to handle. If you know Microsoft Excel Solver, you would never run into such situation.

If you need to build a more sophisticated model, you can contact me at chan_a@algconsultings.com.  I am an Associate of Society of Actuaries (ASA) with extensive financial model experience.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Excel 2010 – Slicer

Microsoft Excel 2010 delivers a new feature called slicer; it allows you to slice-and-dice your pivot table.  In the older versions of Excel, if you want to use pivot table to do an income analysis for BC, Ontario and Quebec; and include disability insurance and long term care products; then you would use report filter to select corresponding items in province and product type.  But report filter does not give you a good visualization, where slicers show you what you have chosen.

alt

Create a slicer in an existing PivotTable – it cannot be easier.

  • Click anywhere in the PivotTable report for which you want to create a slicer.  This displays the PivotTable Tools, adding an Options and a Design tab on the Ribbon.
  • On the Options tab, in the Sort & Filter group, click Insert Slicer.

alt

  • In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer.

alt

  • Click OK, then you can use the slicers to choose your provinces and product types.

alt

You know what have been included and what have not been in your pivot table.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!


Excel 2010 – Sparklines

Graph is an effective way to present large amount of data.  However, data driven graph can also be quite large in older versions of Microsoft Excel, so it is not easy to post multiple graphs on 1 single piece of paper.  Microsoft Excel 2010 has implemented Sparklines to solve this problem. Sparklines is "intense, simple, word-sized graphics", its inventor Edward Tufte describes them in his book Beautiful Evidence.

alt

You can see from the above example, the current sales number plus sparklines that indicated 1 year trend of the sales pattern.  Each sparkline takes up a cell and it is not floating on the grid like chart does.  It does not give you details number but it adds context and meaning to the number next to it. Below you can find a few more examples on how sparklines can present extra information. altalt

Creating sparklines is quite easy.  First, you insert a new column where you want to place your sparklines.

alt

And then you go to the insert tab on the ribbon and choose the type of Sparklines that you want to inset into your column. alt

You define the Data Range and Location Range in the Create Sparklines dialogue and click OK button.

alt

Now, you have your first sparklines.

alt

You can format you sparklines, e.g. highlight the high and low points on the sparklines.  When you select the sparklines, you would see a sparkline contextual tab in the ribbon.

alt

You can highlight high / low points by specifying their color.

alt

Your sparklines would then have the new format.

alt

Isn’t it easy?  You can download a copy of Microsoft Office 2010 Beta and starting experiencing all the new features including Sparklines.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!


Excel 2010 – 64-bit Edition

One of the most obvious new features of Microsoft Excel 2010 is its 64-bit edition.  If you have over 4GB of memory and a 64-bit Windows operating system; then you may consider upgrading to Microsoft Excel 2010 64-bit edition.  It allows you to create gigantic Excel spreadsheet, i.e. over 2 GB limit.


I don’t recommend Excel users to create any huge worksheet because it is often problematic.  I encourage them to move their data to a database if the worksheet is too big.  However, I understand some organizations may not have a database server and their Excel worksheets can hit the 32-bit limit.  With the 64-bit edition, the 2 GB limit is gone and you can also do a pivot table analysis on large database recordsets. You might need to review your VBA code and make updates in order to get it working with 64-bit Excel. You may also need to have 64-bit versions of any ActiveX controls, COM Add-ins, or XLLs if these are used in your solutions.  Please consult your IT department before upgrade to Microsoft Excel 2010 64-bit edition.

Andrew Chan is the owner and founder of ALG Inc.