Ministry of Manpower Unemployment Dashboard in Excel

MOM’s website (www.mom.gov.sg) contains a wealth of labor data. An example is on unemployment rates in Singapore. I took two sets of data: Unemployment Rate (Seasonally Adjusted) & Resident Long-Term Unemployment Rate (Non-Seasonally Adjusted).

They look like this. As individual charts, they may not present a strong overall picture.

I redesigned the presentation into a dashboard using Excel 2003. You can download a copy here: Excel Productivity Cafe Files.

The effect of a well designed dashboard is you are able to survey more data in a glance, and make quicker assessments for faster decisions. I did this dashboard in 20mins, you can do it too.

Learn more about Creating Dashboards in Excel.

Magazine quality charts in Excel 2003

I’d like to show you how we run our Excel Charting courses in Aeternus. I promise that after attending our Excel Charting course, your Excel charting skills will definately go up by several notches. As an example, I plucked out a pretty line chart from the Economist Magazine August 2009 issue.

Imagine you can create such magazine quality charts directly in Excel!

The benefits:

• Refreshing way to present charts.
• You do not need expensive charting software.
• Explain your charts to management with your commentary.
• Carry this skill everywhere you go.

The process:

Step 1: I started with data in a typical worksheet.

Step 2: Using the above figures, I insert a typical Excel line chart.

Step 3: We will coach you through detail steps to produce this Excel chart. Clearly, the results speak for themselves.

Once this chart is created, you simply need to update those numbers and new charts are redrawn. You can also update your expert comments right inside the chart!

Step 4: We don’t stop here. AEternus will build upon your newly acquired charting skills to create entire management dashboards.

To learn more about how to create beautiful charts like these in Excel, check out this workshop: Beautiful Excel Charts

Learn more about Creating Dashboards in Excel.

Top 5 mistakes of Excel Charts

As you know, an Excel chart can make complex relationships much clearer than words and numbers can. But more often than not, most of us stumble to achieve this. How many times have we come across charts that confuses more than it explains? Knowing how to present your numbers in a clear even attractive fashion will definately give you a leg up in your work.

So what are the common mistakes that we commit?
1. You don’t know your Excel chart options.
This is how an Excel chart looks like.

Too often, we settle for the default chart settings that Excel gives us. The colors are usually dull, the numbers are not labeled, there are no titles. There are a huge number of options that we can change to make our charts look better. Simply select any part of an Excel chart, right click and format a chart component. Go and explore them, it won’t hurt you.

2. You used the wrong Excel charts.
There are more than 13 major chart types in Excel. Certain data are better presented with specific chart types. Make sure you use the right ones.

3. You loaded too much information in an Excel chart.
A chart with too many data series is no better than a table full of numbers. Relationships and meaning of the data are lost in the confusion.

4. You didn’t explain your Excel charts.
A strong chart should articulate the conclusions you want to convey. You can insert graphic shapes into a chart to do so.

5. You shouldn’t have used Excel charts.
This works if you only have one key message or the big message is just a number, why bother putting in a chart? You definitely can create a bigger impact by not using charts at all!

To learn more about how to create beautiful charts in Excel, check out this workshop: Beautiful Excel Charts

Where Microsoft Excel Fails

Your company may have spent thousands of dollars on a fancy financial application but nothing beats the comfort and familarity of a spreadsheet. They are indispensible tools for budgeting and analysis in all sorts of corporate functions like human resource, finance, accounting and the list goes on.

Now imagine among all the thousands of spreadsheets in your department what are the chances that you find errors in them? As reported in Computer World (May 2004) as much as 20% to 40% of Excel spreadsheets have errors. Some researchers would even put it as high as 91%!

Think about the odds next time you are working on a worksheet. Be very very careful.

Learn more about our Excel training program Robust Spreadsheets with Checks and Controls.

Drive your business with a dashboard in Excel

• What if there is a dashboard that puts you in the driver seat of your business instead of being drowned by numbers?
• What if you can understand in a glance all the key business drivers of your business operation?
• What if you have total control of the publishing process using a spreadsheet instead of a $10,000 reporting software?

These are the key questions that I asked myself before deciding to create such a training program.  The questions lead to a logical answer: Why not create dashboards in Microsoft Excel?  Microsoft Excel is rich in database features and its easy to create charts in worksheets.  Excel simply has a lot of untapped potential for applications and it seemed like a perfect platform to create management dashboards.

Learn more about Creating Dashboards in Excel.

How to design a Sales Dashboard in Microsoft Excel

Sales activity is the life blood for all businesses.  People who are responsible for sales must have their fingers on this vital pulse at all times.

Traditionally, managers read sales reports in tables of numbers.  Imagine if all the sales data is presented like below.  It summarizes 40 thousand sales records and presents the results all in a single dashboard. This dashboard shows:

• Sales information are divided by regions and placed side by side for Regional managers.
• Tiny sparklines show various historical sale trends and is compared with historical forecasts in blue for an entire year.
• Current sales performances are immediately compared with Sales KPI levels to judge performance levels.

The great thing about using dashboards is that sales results are communicated clearly and immediately.  It can align with the way people think and act so decisions can be made quicker!

Such dashboards are not difficult to build in Microsoft Excel.  Sales transaction can be extracted from the backend database systems and the numbers are simply plotted by Excel charts. Of course the analyst needs to follow certain dashboard design principles.

Learn more about Creating Dashboards in Excel.

Microsoft Excel Training Course for Human Resource

Excel is a powerful tool that allows you to analyze data and create various reports for decision making.

Being Excel savvy is an invaluable if not necessary skill to be a successful HR professional. Further more, it is no longer an option to depend on the IT analyst to develop and generate critical HR reports on your behalf. They may not be timely or flexible enough to cater to your specific needs.

By being proficient in Excel, you can take advantage of its powerful features to organize and analyze HR business information so that important decisions can be made.

To learn more about this training program Microsoft Excel for HR Professionals

Develop Robust Excel Spreadsheets with Checks & Controls

The use of spreadsheets has crept into daily business operations at the highest levels. Given that many spreadsheets are developed by self taught professionals, there is a likelihood that many critical spreadsheets (eg. balance sheets, portfolio investment models, risk analysis models, revenue calculations) have material errors.

There is a clear and present need to develop error free spreadsheets and adopt these electronic documents into a checks and controls framework to prevent future errors.

In this training program, we learn Excel techniques to:

• Develop robust spreadsheets.
• Prevent formula and user operation errors.
• Audit spreadsheets with powerful formula auditing techniques.
• Foil attempts to conceal data and formulas.

Learn more about our Excel training program Robust Spreadsheets with Checks and Controls.

Control your Excel spreadsheets!

Consider these shocking stories on how a simple Excel error can cause you sleepless nights:

• A cut and paste error cost TransAlta $24 mil when it underbid an electricity supply contract.

• A missing minus sign caused Fidelity Magellan Fund to overstate projected earnings by $2.6 bil and miss a promised dividend.

• Falsely linked spreadsheets permitted fraud totalling $700 mil at Allied Irish Bank.

We tend to use Excel spreadsheets like pieces of paper without care and consideration. I’m willing to bet that many of our most important Excel spreadsheets have material errors.

Learn more about our Excel training program Robust Spreadsheets with Checks and Controls.

Painless Business Reporting in Excel

I have noted that generating business reports in Excel has many common problems across companies though my years of teaching Excel and working as a business analyst in a large corporation.

An analyst without sufficient knowledge of Excel often labors at each stage of business reporting. Compounding this problem, reports are often expected on short notices and mistakes are common.

Motivated by this situation, I’ve put together some of the best tricks I’ve learned and applied them successfully in an operational environment. Each task in this book contains detailed instructions to solve typical reporting problems in a quick and accurate manner. An analyst with a general proficiency of Excel should be able to take these ideas as recipes to automate many transaction based business reports to improve productivity, reduce reporting errors and enhance reporting value to managements.

Learn more about our Excel training course Painless Business Reporting in Excel.

« Previous Page — Next Page »