Map Your Financial Health With an Excel DuPont Dashboard

Your Return On Equity ratio is a key indicator of financial health. This report lets you show the components of that ratio in a unique Excel display.

Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

Management reporting is all about communication. Reporting Return On Equity (ROE) is a case in point.

DuPont Dashboard in Excel

 

The ROE financial ratio is a key measure of financial health. But to non-financial managers, the ROE can be difficult to understand, for two reasons.

One reason is that people wonder what all the fuss is about. Bean counters create ratios faster than grandmothers bake cookies. So, what’s the big deal about one more ratio?

Slightly over-simplified, your ROE compares directly with your growth rate in sales. If you grow faster than your ROE, you weaken your financial structure; if you grow more slowly than your ROE, you strengthen your financial structure.

Years ago, I wrote two columns for Inc Magazine about the ROE. The columns provide more background about this important measure. You can read them at: Weighing Your Debt Load and How Fast Is Too Fast?

The other reason that using ROE can be difficult is that it’s a top-level ratio that’s affected by virtually every other measure of financial performance. That’s the benefit offered by the Excel report shown above.

Read Full Article: Map Your Financial Health With an Excel DuPont Dashboard

Source: ExcelUser

Create Variable-Length, Dynamic Reports Linked to Excel Tables

When you change a criteria value in a cell, Excel can use formulas and conditional formatting to expand or contract your report automatically, without macros. Here’s how.

Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

Excel Tables are a powerful feature introduced in Excel 2007. Not only can you report from them directly, you can use them as a source of data for dynamic reports, including variable-length accordion reports.

The first task is quick and easy; the second is a lot more interesting. Let’s look at both.

Report Directly from Excel Tables

Suppose you have a table like this and you want to generate a call-assignments report for each sales person.

The easiest approach is to report directly from the Table. For example, If you wanted to report on Alyson, you could click on the Sales Person filter and choose Alyson from the list, giving you this result:

You could print her report, and then do the same for Peter.

Read Full Article: Create Variable-Length, Dynamic Reports Linked to Excel Tables

Source: ExcelUser

What’s a ‘Deprecated’ Function in Excel?

Excel Deprecated

Image Courtesy of: Stuart Miles/FreeDigitalPhotos.net

 

Microsoft tells us that many worksheet functions are ‘deprecated.’ So what does that mean, exactly? Wikipedia tells us that deprecation is a status applied to a computer software feature, characteristic, or practice indicating it should be avoided, typically because of being superseded. Each new generation of Excel seems to deprecate additional worksheet functions. For example, Microsoft’s help topic for the FTEST function tells us:

IMPORTANT: 

This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel. In Excel 2010 and above, as you type a function name, Excel lists the functions that match and provides a short description.

Read Full Article: What’s a Deprecated Function in Excel
Source: ExcelUser

Introducing Excel’s Three Types of Spreadsheet Databases

Excel offers at least three ways to set up data so your reports and analyses can use it easily as a reliable data source.

Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

Excel offers three general ways to arrange data in your spreadsheet so you can use it as a database with your worksheet formulas:

  1. Simple (or “Gray Cell”) Tables, which I’ve used since Excel 2.0.
  2. Excel Tables, introduced in Excel 2007.
  3. PivotTables with a Tabular Report Layout, introduced in Excel 2010.

Database experts likely would be offended by my calling any of these a “database.” After all, these three spreadsheet databases are easy to set up and use…not at all like a “real” database. Even so, these databases work like a real database for your Excel formulas. If your reports and analyses can get their data from one of these types of spreadsheet databases, you can improve your reporting and analyses significantly.

In this article, I’ll introduce each type of spreadsheet database. Then in future posts, I’ll go into greater detail.

Read Full Article: Introducing Excel’s Three Types of Spreadsheet Databases

Source: ExcelUser

Five Really Useful Excel Keyboard Shortcuts

Excel Shortcuts

Image Credit: David Castillo/FreeDigitalPhotos.net

These keyboard shortcuts will help you to things in Excel that are difficult or time-consuming to do any other way.

Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

Several years ago, I created my Complete Excel Shortcuts Workbook. It contains more that 250 keyboard shortcuts we can use when the workbook is active.

I created the workbook as a reference, because I didn’t know of any other source that had them all. I’m fairly certain that this workbook is complete, because thousands of people have downloaded it and I’ve added the few additional shortcuts that Excel users have sent my way.

Most Excel users use some of the shortcuts frequently, like Ctrl+c and Ctrl+v.  But here are five shortcuts that aren’t as well-known, and that I use almost daily. I think you’ll find them worth remembering:

Excel Shortcut to Get Help for a Worksheet Function

Suppose you need to read the help topic for a worksheet function, say the MATCH function. When you type…

=match(

…in your formula bar, you’ll see something like this:

Excel shortcut to get help for a worksheet function.

Press Ctrl+a or click the icon where the red arrow points to launch the Function Arguments dialog for the function. This dialog gives you a form that you can fill in for each argument. The dialog also provides short help for the function and for each argument.

Read Full Article: Five Really Useful Excel Keyboard Shortcuts

Source: ExcelUser

Big Data doesn’t have to be so “big” after all

In an era where having access to real-time data and analyzing data effectively can push you a step ahead of your competition, it is essential to make the big data process as simple as possible. Data gathered from transactions, to customer service functionally, price, financial, and sales data are all essential to running your business smoothly.

Big data as the name suggests has been made to be quite a complex process. While this assumption may not be completely erroneous, with the right business intelligence tool, big data can be simplified. If you are currently using a big data solutions tool but still find yourself working long hours to get your reports, or if your reporting system is currently not automated, then you are making the data analyzing process more complicated than it should be. The goal when gathering data for reports and analytics is to spend as much time coming up strategies that put you a step ahead of your competition. If you go through so much trouble to get the data and your reports, it may not leave enough time for you to accurately analyze, forecast and make those all-so-important decisions that move your business forward.

With the right business intelligence tool such as PowerOLAP ; your skills are leveraged by using the same user-end software that you currently use such as Excel. Cutting out the complexities of using an unfamiliar user-end software, can reduce the time spent contacting IT for help. PowerOLAP helps companies overcome the limitations of Excel on the one hand and accounting systems on the other, at the same time combining their benefits.  Your business intelligence tool should free you from repetitive data entry and working in two dimensions with static data.PowerOLAP’s modeling environment—multidimensional cubes allows companies to work with both.

Download a PowerOLAP free trial to simplify your big data analyzing process to automatic reporting powered by real-time data. We believe that big data analytics can be made simple so no matter what business intelligence tool you use that should be the key- sales and financial data through the entire cycle of a project.

This Blog Post was originally posted in the PARIS Technologies Business Intelligence Blog.

View Original post: Big Data doesn’t have to be so “big” after all