Category Archives: 99-Spreadsheet modeling

Using Excel to create useful analyses of data, building reliable “what if” models to support the problem-solving process, checking integrity of modeling work, using spreadsheet work to drive communication of results

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 resources, Excel shortcuts

Pharmacist with Advanced Excel Skills, an Oxymoron?

1 Comment

I have now come to the conclusion that a pharmacist with advanced Excel skills is an oxymoron.

To prove this I did an advanced linked in search a couple of months ago with the key words “pharmacist” and “excel”. I was surprised when I got a lot more hits than I expected. I started to panic. However, when I started going through the profiles, I realized that people were describing how they excelled at say patient care or customer service etc. rather than at MS Excel.  While this brought a sigh of relief that I wasn’t alone in the boat of pharmacists bereft of excel knowledge, it made me also realize the huge opportunity that existed for me.

My journey into the world of excel has been through a variety or resources, but I have found wallstreetprep.com particularly helpful.

While I’m still a work-in-progress, I am encouraged by what I am able to do so far and wish to send that encouragement on to my other colleagues, who by their nature of work never have to touch a spreadsheet. It is doable. All it takes is a couple of minutes a day consistently.

05-Creating & constructing good solutions, 07a-Crafting an effective formal presentation, 07b-Using good information design, 99-Spreadsheet modeling, charts & graphs, tables, Uncategorized

Misleading Graphs & Statistical Lies

4 Comments

Graphs and Charts are everywhere, and are excellent tools to visually convey statistics, results, trends, data, etc. There are basically three groups of graphs out there that you’ll find on a regular basis:

1.) Graphs created by people who do know what they are doing

2.) Graphs created by people who don’t know what they are doing

3.) Graphs created by people who do know what they are doing and have manipulated it to intentionally deceive the viewer.

There’s a fine line between number 2 and 3 sometimes, and to be effective business leaders, one skill we must possess is the ability to call “BS”, whether intentional or unintentional. Below is a great book to help uncover a lot of deceptive tricks and a few some examples.

A great book that I highly recommend is: “How To Lie With Statistics“. It’s short, cheap, and uncovers numerous tricks people use with charts, graphs, numbers, and statistics to deceive the reader without breaking the rules.

Not to pick on Fox News, but below is a graph that is severely misleading in both the title and the scale of the X-axis. The title leads you to believe the data is by consecutive quarter, and the inaccurate spacing on the X-axis leads to to believe the data is linear.

If you title and plot this data accurately, below is what you would get:

There are many types of errors or tricks that results in the display of data in an inaccurate way. Below are several categories and things to watch out for the next time somebody slaps a fancy looking report down on your desk:

USE OF THE 3D CHART:

Simple use of 3D charts distort the ratio of pies and the height of bars. Notice how Item A and C look more similar in the 3D chart, but flattened, C is less than half of A

ChartMisleading Pie Chart.pngSample Pie Chart.png

 IMPROPER SCALING:

Notice how the intent is to increase the value 3X (Y-Axis), while the perception is that it increased 9X

Improperly scaled picture graph.svg

Comparison of properly and improperly scaled picture graph.svg

The appropriate way to display the increase from 1 to 3 is shown below.

Picture Graph.svg

MISLEADING TRUNCATION:

The truncation on the following graph leads the viewer to believe that group E is nearly twice the size of group A. While sometimes truncation is a great tool in certain situations, it is often misused.

Truncated Bar Graph.svg

Looking at the scale from 0 to 12,000 puts in perspective how slight of a difference there is between groups.

Bar graph.svg

IMPROPER AXIS RANGES:

The graph immediately below makes you feel as though the growth over time has been slow and gradual, but a quick change of the axis values gives a completely different perception. Don’t always believe the slopes of lines as they are a function of the Axis values.

Line graph2.svg

Line graph3.svg

OMISSION OF SCALE:

When Scales are left off, the range of the axis is unknown and differences are easily exaggerated or minimized.

Bar graph missing zero1.svg    Example truncated bar graph.svg

 

99-Spreadsheet modeling, Excel resources

More Excel Resources

Leave a comment

If you are like me and plan to continue developing your excel skills even more, this website has some great advanced excel tutorials. For each of the monthly tutorials, there are 6 or 7 advanced tutorials like COUNTIFS, Duplicate removal, or bubble charts.

http://isaacgottlieb.com/tip-of-the-month

I found these tutorials on a comprehensive website called skilledup.com that has a lot of different resources that are worth checking out.

http://www.skilledup.com/learn/business-entrepreneurship/free-excel-tutorials/

From the skilledup.com website, I also found Excel Central and Chandoo especially helpful. Excel Central has great basic through advanced video tutorials and Chandoo is super easy to navigate.

Enjoy these resources for your continued excel development. 🙂

Brandi

99-Spreadsheet modeling, charts & graphs, Excel resources, team dynamics

My Challenges with Excel

1 Comment

C.S. Lewis (1898-1963), Fellow and Tutor in English Literature at Oxford University, and Chair of Medieval and Renaissance English at Cambridge, was an intellectual giant of the 20th century. In later life, reflecting on challenges he faced in his younger days, Lewis remarked:

“I could never have gone very far in any science, because in the path of every science the lion mathematics lies in wait for you. Even in mathematics whatever could be done by mere reasoning as in simple geometry I did with delight. But the moment calculation came in I was helpless. I grasped the principles but my answers were always wrong. Yet though I never could have been a scientist, I had scientific as well as imaginative impulses and I loved ratiocination*.”

To be candid, I have some apprehension when it comes to Excel . . . made all the more pressing because of how central excel skills are to the practice of finance. So when I consider my excel challenges I draw much encouragement from these thoughts of C.S. Lewis.

Although I have a deep passion for finance, and have worked in investment management for 10 years, my position at work has been more about interpreting and leveraging excel generated data, rather than performing the actual work of creating spreadsheet models myself.

The result is that up till now, my excel skills have languished.  Thus I’m grateful that our MBA program has forced me to improve. In this spirit, (and acting on the advice of professor Noonan) I’ve set out to build my own personal top 10 excel skillset, that will be useful to my financial career.  Some of these may seem rather obvious, but excel is my self-selected focus area and I figured that I could subject my list to public opinion and scrutiny, in the hope that collaborative discussion might occur. I also thought that some of this material might be useful for those considering a financial career.

Rough draft of “Top 10” excel skills for investment management and financial planning for high net worth individuals, families, trusts, and charitable foundations:

  1. Precision tree
  2. Sensitivity analysis, useful for evaluating insurance policies
  3. Goal Seek
  4. Historical market and financial instrument analysis
  5. Excel integration with Word and PowerPoint
  6. Configuring excel for pleasant appearance and functionality
  7. Mortgage / Bond / Annuity amortization schedules
  8. Learning to manipulate and efficiently sort data – this is hugely important for many areas within our business such as ranking client positions, asset allocation decisions, analysis of individual positions, and assessing performance in order to prioritize investment decisions
  9. Graphing portfolio performance vs. relevant market benchmarks
  10. Leveraging excel with proprietary finance software used by major financial enterprises. (our firm uses Advent)

When I reflect on the challenges of the first year of MBA school, I am glad that I have improved in several of theses areas and incorporated them into different assignments and projects. Our cohort has aided much of my improvement.

For example, during first semester, I was fortunate to be on a team with Shehzad Shabuddin, who was quite generous with his time and patient with helping me make progress on excel. Shehzad’s blog post, The Excel trap, reminds us of the dangers of reducing life to data and mathematics. Excel-ing in Real Estate by Bob Caperton and the article by Barry Slaymaker on MBA level excel skills were both particularly helpful. I’m also hopeful that I can persuade Joe Song to give me some Excel lessons between now and graduation.

One of the most powerful lessons I have learned in life (and a “key content” area of MP) is the importance of surrounding yourself with others whose complementary strengths have the capacity to offset your personal areas of weakness.  My next blog post will examine a leader who intimately understood just how powerful this principal is, and became a great, and most unexpected, actor in history.

*Ratiocination, noun. – the process of logical reasoning. [1520-30]

99-Spreadsheet modeling, Excel resources

The Excel Trap

2 Comments

Vlookups, macros, formulas, cells, tables, all words used to describe a function people utilize Excel to complete a task. A quick Google search for anything related to excel will return countless forums, message boards, tutorials and other methods to learn whatever challenge you are having in the excel space. The knowledge and power to utilize Excel as a business tool is readily available. The biggest mistake people make is forgetting that Excel is just another tool, not a cure all. Sadly, once a “template” for an excel model is created, people assume the accuracy of the cells and forget to look at the data for accuracy. In many cases this problem may not cause any serious ramifications; however, as seen from Tim Worstall’s Forbes article, “Microsoft’s Excel Might be the Most Dangerous Software on the Planet”, there are situations where people were depending on excel for billion dollar deals with the wrong formula calculation.

As important as it is to understand the reasons to use excel, it is just as important to know reasons not to use excel.  Ramon Ray in “Another Reason Not To Use Excel” writes, “Many of you, I do it too, use Excel for so many things as it’s so easy to use. You just open it and begin typing. However, as your data needs expands, it’s important to use a tool that’s built for your needs. An accounting program for bookkeeping; a database for inventory; and a contact management tool for keeping track of contents. I could go on.” All too often Excel is relied upon for various business needs in places where it probably shouldn’t be there. It’s important to understand the capabilities of Excel in order to make critical decisions as to the benefit of utilizing it. Knowing the limitations can make the analysis quicker as well as ensure falling for an Excel trap.

While its users easily identify the benefits of Excel, the dangers are not so easily understood. Without having a proper spreadsheeting software, the financial institution as a whole would not be what they are today. Many of the derivate securities that are sold on the market on a daily basis are modeled using Excel or something very similar. In Worstall’s article, he mentions “That very throwing of trillions a day around the markets (and it really is trillions a day: the foreign exchange market in London alone is over $2 trillion a day) is dependent upon the existence of Excel itself.” Without excel, the models necessary to perform these transactions would be impossible; but just like anything else, if you put garbage in, you’re going to get garbage out. Unfortunately, once a model is created, it is taken as a fact, rather than as a resource to determine the right answer. This faith in Excel can lead to serious issues which is why it is important to review and refine models as more information becomes readily available. The next time you turn to Excel to solve a problem with a any sort of business model, be sure to remember that it’s just another tool in your tool belt, a powerful tool, but a tool nonetheless.

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.

99-Spreadsheet modeling, Excel resources

MBA-Level Excel Skills

5 Comments

Beyond the MBA top 20 supplied by Professor Noonan, I have located a handful of academic resources indicating which Excel skills MBA students should have when they graduate, and in some cases, before they begin their studies. While this information may not be a direct resource to gain these skills, it paints a picture of the expectations that the business world will have for us when we complete our studies.

 

Duke’s Fuqua School of Business identifies a number of skills that incoming MBA students should have on the Computer Skills section of their MBA ready website at http://www.fuqua.duke.edu/student_resources/mba_ready_executive/computer_skills/#doc2

One of the skills that has caught my attention here, and an area in which I lack experience, is referencing across worksheets and workbooks. While referencing across worksheets is relatively intuitive for me, references across workbooks will definitely require some additional research on my part. Additionally, Duke requests that students are familiar with text file imports into Excel. While I feel that minor research can reveal a process for this, my interest is piqued by this requirement.

Indiana University’s Kelley School of Business and their Kelley Direct online program provide a slightly different set of recommendations on their Prerequisite Excel Skills page at http://kelley.iu.edu/onlineMBA/Admissions/Prerequisites/page36896.html . Here we find a grouping of skills and specific functions that are required. While many of these functions should be familiar to most of us, I am not sure how many within our cohort spend time parsing text or using reference functions. To that end, I am sure that the nature of one’s background is likely what is driving what and when these functions may be used.

Finally, the Anderson School of Management at UCLA, http://www.anderson.ucla.edu/degrees/mba-program/admit-central/curriculum-and-academics/pre-mba-preparation provides significant opportunities for incoming MBA students to refine their skills in advance of their studies, from assessments to online courses and on campus workshops. While this site does not directly speak to the Excel requirements of it students, it does exemplify the importance that the university places on these skills. Also, you will find a link to an online tools page at the bottom of the above linked page.

 

What does all of this mean? First and foremost, I believe that this solidifies the concept that excel skills are paramount to success in business school and business in general. Secondly, one could interpret this information as a gauge of where the Excel skill lie for those you will be competing with post-graduation. Finally, I see this information as a sign that Goizueta may be lagging behind some of the top schools in their stated value of Excel. Perhaps I have forgotten by this time, but I do not recollect any Excel requirements being addressed during the orientation or onboarding process and I am fairly sure that there was no foundations course in Excel. Please correct me if I am wrong about this, but I think Goizueta would be well served to sharpen the focus on this key skillset.

05-Creating & constructing good solutions, 99-Applying business intelligence, 99-Spreadsheet modeling, Uncategorized

Tiny Data: Not An Excuse

4 Comments

When I got my first job as a Process Improvement Engineer for an industry leading company in their flagship facility, my first question to their production manager was: “Where’s the historical data on the process we need to improve?”. His answer was: “Well I know how many pounds of potatoes we usually put in, and I know about how many bags of potato chips come out the other end.”

How could such a sophisticated, industry leading company have so little knowledge about their own processes? Four years later, reflecting back on all the companies I have worked for and had exposure to, few have had the ‘big data’ that is such a popular topic of today’s data analysis discussions.

How do those us us who have only, ‘Tiny Data’ or incomplete data use it to make better decisions and improve out businesses? The first article posted below cites an Army Colonel’s experience:

“Look,” said the colonel, “if I’m on a battlefield trying to defend a hill and I get a piece of intelligence, even if I’m not 100 percent sure that it’s accurate, I will make decisions based on that intelligence.” He strongly believed that it’s better to have some information than none—and that you’d be a fool to disregard it just because it falls short of being definitive.

There are many ways to utilize small amounts of data, incomplete data, and varying quality data. You must find ways to fill in the gaps, determine the variance of the quality, and find ways to draw meaningful conclusions and areas to investigate more fully with small amounts of data.

Branch out and be creative, because a little bit of information is better than no information and is no excuse for simply accepting the status quo.

When Big Data Isn’t An Option

Small Data Analysis

How to Analyze Data With Low Quality or Small Samples