Category Archives: Excel power users

99-Spreadsheet modeling, Excel power users, Excel resources

Introducing…. Power Pivot (with Excel)

2 Comments

I think everyone can agree that pivot tables are great excel tools which make analyzing and sorting massive amounts of data a life saver. However, over the course of the past couple years I have been introduced to Power Pivot. Yes, Power Pivot. Pivot tables on steroids. At first, I was pretty intimidated because of the sheer size and complexity on the surface. But after spending some time learning and taking “lunch n’ learn” course at work, Power Pivots and I are becoming friends. My favorite part is being able to run pivot tables essentially off other pivot tables. At work, we’ve also created many tools that allow our leaders to simply and easily run their own reports for specific requests, which saves everyone time. (The analyst teams refresh the data regularly as needed).

I’ve included a couple links below about Power Pivot, and would encourage all to consider using the valuable tool the next time you are looking to crush some colossal data. It’s available as a free add-on for Excel 2010 and now comes standard on Excel 2013.

Happy Pivot Tabling!

 

WJEM

 

https://www.youtube.com/watch?v=URy_uQYS49s

http://info.110consulting.com/blog/bid/374825/Top-10-Benefits-of-Using-Excel-PowerPivot

http://blogs.office.com/2010/10/01/top-5-ways-powerpivot-helps-excel-pros/

99-Spreadsheet modeling, Excel power users, Excel resources, Excel shortcuts

Every Spreadsheet Has An Error

2 Comments

If your job is anything like mine, you’ve had to work on a massive data dump, sorting and manipulating to find “a story.” The size of the data files can sometimes be intimidating and there sometimes is that concern in the back of your mind that some formula or reference within your Excel workbook went rogue.

As we strive to be more proficient in our Excel skills and more efficient in our tasks, coming across a headline such as “Every Spreadsheet Has an Error” can certainly sound alarming! However, as I read the article in greater depth, I found the points to serve as good guidelines to help check my work.
http://www.forbes.com/sites/billconerly/2013/04/25/every-spreadsheet-has-an-error-7-lessons-motivated-by-reinhart-and-rogoff/
Some of the tips suggested in the link are:

1) Use assumption variables – Similar to what we learned in Decision Analysis class, create a section within your workbook with all your assumption variables. Create links within the workbook to these assumptions. This will ensure that when you have to change your assumptions in the future you will not have to search all your data for every occurrence that may be affected.

2) Link, don’t copy – Create links to raw data so that you can go back and check your manipulated data against the original document.

3) Create double-check formulas – “If, then” and “true, false” statements are great for checking your work!

4) Format to Tell Differences – Conditional formatting helps to highlight differences in the data. They also help to identify trends and patterns in your data. I’m a big fan of using colors when doing conditional formatting.

5) Graph Your Data – When possible, graph your data. A line chart for instance will give you a quick visual to not only spot any irregularities in your data, but also to help find “the story.”

6) Document – Create notes of the steps or sources that you used to create your end product. This habit can save a ton of time when you have to do an update. Notes are also helpful for others who may have to replicate your report. We have a saying at my workplace, “a detailed source line is not for the client, it’s for us!”

7) Be Suspicious – Check your work. See if you can find an error.

Hopefully, by following some of these tips, you found all of your errors!

99-Spreadsheet modeling, Excel power users

The Dangers of Excel: A Lesson for Managers

Leave a comment

A Google search on Microsoft Excel results in hundreds of websites that offer advice on Excel. There are hundreds of businesses that can teach you how to use Microsoft Excel and hundreds of thousands of YouTube videos explaining Excel tips and tricks.

Although there are software applications available for accounting, finance, data analysis, project management, and forecasting, Excel is a forerunner for these business functions. Most businesses want their managers to have a basic, if not advanced, understanding of Microsoft Excel.

Yet, there are dangers in relying too heavily on Excel, especially if the advanced understanding of Excel is unknown by the company’s management team.

In “Microsoft’s Excel Might Be The Most Dangerous Software On The Planet” Tim Worstall described when JP Morgan’s CIO (in 2012)  called upon a quantitative analyst, “a London-based quantitative expert, mathematician and model developer”, to create a new value-at-risk model for a synthetic credit portfolio. The analyst’s “advanced” techniques involved copying and pasting data between several Excel spreadsheets. This manual process left room for error, several errors in fact, which cost JP Morgan several billion dollars.

Despite the availability of automated technologies to double check databases and equations (especially when dealing with billions of dollars), top management of large, public companies still rely on Microsoft Excel.

UK Regulators, the Basel Comittee on Banking Supervision (BCBS) and the Financial Services Authority (FSA), were quick to respond to the incident. In his article, Vic Daniels said the BCBS and FSA “made it clear that when relying on manual processes, desktop applications or key internal data flow systems such as spreadsheets, banks and insurers should have effective controls in place that are consistently applied to manage risks around incorrect, false or even fraudulent data.”

This is an eye-opening lesson for budding managers. A lesson in communication and how to oversee projects worth billions of dollars but also a lesson about understanding the tools a company uses and how they should be used in company projects. To me, the scenario illustrates an important reason for me to learn Excel. With Excel skills,  I can create and manage my own projects as well as double check my team members’ projects to prevent errors such as JP Morgan’s. There are other tools that can be used for managing data but I’m going to start with Microsoft Excel.