Category Archives: Excel resources

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.

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 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.

Excel resources

Excel-ing in Real Estate

2 Comments

I learned early in my first semester that my skills with Microsoft Excel were in need of serious improvement.  By the second semester, I realized that I might be the least proficient Excel user in the entire program.  This is sort of embarrassing considering that I was a finance major and work in the commercial real estate business.  That being said, I am determined to improve.

This MP project is very timely for me.  It coincides with the need for me to analyze several prospective investments for my company.  Recently I have taken the time to review Professor Noonan’s slides and from that decided on some of the skills that I plan to acquire.  I have since learned how to use pivot tables as well as the sensitivity analysis feature.  I found some youtube videos that really helped me fine tune these skills:

Pivot Table

Sensitivity Analysis

I recently used sensitivity analysis as part of my analysis in evaluating an apartment complex.  See below:

$                     264,976.47 37,000 38,000 39,000 40,000 41,000 42,000 43,000
15,000    258,823.53    270,588.24    282,352.94    294,117.65    305,882.35    317,647.06    329,411.76
16,000    247,058.82    258,823.53    270,588.24    282,352.94    294,117.65    305,882.35    317,647.06
17,000    235,294.12    247,058.82    258,823.53    270,588.24    282,352.94    294,117.65    305,882.35
18,000    223,529.41    235,294.12    247,058.82    258,823.53    270,588.24    282,352.94    294,117.65
19,000    211,764.71    223,529.41    235,294.12    247,058.82    258,823.53    270,588.24    282,352.94
20,000    200,000.00    211,764.71    223,529.41    235,294.12    247,058.82    258,823.53    270,588.24
21,000    188,235.29    200,000.00    211,764.71    223,529.41    235,294.12    247,058.82    258,823.53

 

The left-hand column (starting with 15,000) refers the Operating Expenses and upper row (beginning with 37,000) refers to Revenue.   The info in the middle shows the resulting value (based on an 8.5 Cap rate).  If you’re not in the Real Estate business, a cap rate is NOI/VALUE, essentially a measure of the rate of return.  This proved fairly helpful as I went over it with our current apartment manager to confirm our offer.

I also did analyses for revenue sensitivity to price and vacancy and the Operating Expense sensitivity to some of the specific expenses.  The Revenue sensitivity illustrates how much vacancy we could bear under certain price levels.  The Operating Expense analysis displays the critical expenses.

I am well aware that, for most of you, this is very basic.  I am just glad to address this weakness.

07a-Crafting an effective formal presentation, 07b-Using good information design, 99-Spreadsheet modeling, charts & graphs, Excel resources, PowerPoint resources, PowerPoint tricks, slide-craft, tables

Sharing a Helpful Resource

4 Comments

If your job is anything like mine, you use Microsoft Excel and/or PowerPoint on a regular (if not daily) basis. If you use either of these applications in conjunction with each other (which I know all of us have in our prior MP presentations), I have a very helpful resource to share with you.

One of the biggest challenges that I have faced is effectively incorporating data (particularly from Excel) into a concise PowerPoint presentation. Given that Excel and PowerPoint are both made by Microsoft, you would think that using these two applications in conjunction with one another would be fairly seamless and potentially even synergistic (1 + 1 = 3, right?).  However, integrating Excel and PowerPoint is not always straight forward, and conveying key takeaways rather than “data dumping” an entire financial model into your presentation can be a challenge. Additionally, I think that most of us are typically better at one than the other which doesn’t make things any easier.  I certainly have found this to be the case for myself, and while I have a strong background in using Excel, I have a lot of “room to grow” in terms of incorporating financial data from huge bulky models into a clean and concise presentation that conveys the underlying data effectively.

As I have searched for tools, resources, and articles giving guidance on this very topic, I have come across a particularly helpful website that I wanted to share with our class and hope that you will find it to be as useful as I have. This website is called ‘Think Outside the Slide’ and is as close to a “one stop shop” as I have found for guidance on just about everything relating to creating powerful presentations, with literally thousands of articles neatly organized by topic, as well as video tutorials if you’re more of a visual person. If you use Excel or PowerPoint at all, there is likely a specific article with tips and guidance on how to more effectively use the applications in a context relevant to you.

These are just a few that I have begun to frequently reference to give you a snapshot:

  • Using Excel Data in Powerpoint Presentations
  • Slide Design, Creation, and Editing
  • Linking Excel Data (and other content) to PowerPoint so that data in slides automatically updates
  • Tips on effectively cleaning up and animating graphs
  • Creating powerful visuals using Excel Data (waterfall graphs, diverging stacked bar charts, treemap diagrams, proportional shape comparisons, etc…)

For many business professionals, myself included, Excel and PowerPoint are critical tools, and learning to use data effectively in PowerPoint presentations can take time but is versatile skill with application to countless professions that can help to distinguish yourself from your peers/co-workers, and add value to your clients. I hope you find this to be a helpful resource, please feel free to share any other resources that you frequently use and have found helpful in your career. Thanks!

Helpful links referenced in this post:

http://www.thinkoutsidetheslide.com/free-resources/

http://www.thinkoutsidetheslide.com/using-excel-data-in-a-powerpoint-presentation/

http://www.thinkoutsidetheslide.com/articles/

99-Spreadsheet modeling, Excel resources, Excel shortcuts

Some Excel Resources to Get you Started

Leave a comment

Those of you choosing to focus on your spreadsheet modeling skills this summer (and everyone else who hopes to devote some time to that at some point) should download the materials I’ve just added to our Blackboard site. (You’ll see a new section, Excel Resources, in the left side-bar.)

The main deck, the “Three Hour Tour,” is an enormous collection. For some of you, all you really need may be found in a small number of slides, for example:

  • The “Top 10” and “Next 10” lists of Excel skills that are essential for MBA work are found on slide #162.
  • Lists of print and web resources for learning and using Excel are found in Appendix 3, starting on slide #193.

Also, for those of you looking for keyboard shortcuts, those new resources on Blackboard include a compilation of several sources’ recommendations and cheat-sheets for Excel and some of the other MS Office applications.

I’ll contribute some more Excel content here in the 05-Spreadsheet Modeling channel of our blog, but of course part of the point is for you to do the same – please share your own discoveries and experiences, and get some discussions going here and elsewhere on the blog.