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

07b-Using good information design, 99-Applying business intelligence, 99-Spreadsheet modeling, charts & graphs, tables

What is the Best way to Pull data from the Internet? #GrowthHacking

Leave a comment

A lot of times we find ourselves in the need to pull a database of information online, but copy and pasting (for example a government database) would take too long.  This forces us to either abandon the data infusion that might give us great insight or to make bad assumptions.

As with many of these types of problems there is a startup working on it.  This specific problem is solved by Import.io.  Their slogan is:

Web Data Extraction Made Easy,  Transform any website into a table of data or a Structured API in minutes without even writing any code.

This software really does what it says and is super simple to use.   Turn any website domain or page into an excel document!  While this is probably a big win in productivity for a lot of us, dont miss the big picture.  There are a bunch of these type of programs available that will make your work easier, but, often times they are either hard to find or we are just not aware that there is a solution for it.

I recommend everyone sign up for these services two newsletters: Product Hunt and Beta List.  They will keep you on the bleeding edge of whenever a new problem solving software comes out!

 

 

 

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: The Hammer to Every Business Nail?

2 Comments

My organization is a small firm engaged in supplying doors and hardware to the commercial construction industry, so we have little need for capabilities related to Excel, right? Well, not exactly….

Leadership at my company is aging and is mired in the view of technology from a decade ago, but they are in love with Excel (although they are only familiar with the most basic functions and capabilities).  Their solution to EVERY issue is to use Excel spreadsheets to find solutions, manage information, or make a decision. In turn, those of us that are tasked with executing and managing these issues have begun to refer to Excel as “The Hammer”.  You see, even if a bullet proof case can be made for managing information through Access, SharePoint, or other industry specific software, leadership wants to use Excel. After all, it is the hammer to every nail!

This leads me to one of the main reasons that I feel that improving my Excel spreadsheet skills will be critical: efficiency. Efficiency with Excel allows me to better handle my workload, more effectively represent the data I have compiled, and more quickly derive time sensitive conclusions to complex issues. Currently, I feel like my skills lead me to taking circuitous routes to reach my destinations, so this is the reason my goal is to spend the summer improving efficiency. There are countless Excel methodologies and formulas that I plan on skimming briefly to gain an understanding of their power, but I intend to focus my personal journey on isolating the key skills such as the top 20 list and polishing my techniques.

One of the techniques that I will be using this semester is to learn by teaching at my workplace. Few of my coworkers and direct reports have Excel skills beyond rudimentary data entry and I have been looked to as an “expert” in the past. Although I hardly deserve the expert label, I find that teaching the software allows for introspection that reveals my weaknesses. In the past 2 weeks, I have spent time with experienced and beginner users working through the basic methodologies and applications for pivot table use. Pivot tables allow our estimators to estimate more quickly, our project managers to more accurately manage material, and our engineers to more effectively isolate and coordinate the details of a project. My goal is to employ my “learn by teaching” idea throughout the summer, hopefully resulting in not only an increased skillset that I can leverage moving forward, but also effecting positive change on our organizational efficiencies.

I invite any comments on my plan or the nature of my post. If you have never tried it, experiment with learning by teaching and see if it works for you. Time to get back to swinging my hammer…

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.