Multimedia-Introduction-to-Business-Analytics-with-Excel-1_-Ren_-Justin_-Z-Business-Analytics-with-E

Business Analytics with Excel (I) Get Equipped! A Multimedia Course on Data Analysis Z. Justin Ren

Copyright © 2015 Z. Justin Ren All Rights Reserved

To Xin, Zuzu, Nini, and Cece



Table of Content About the Author Preface to Series Chapter 1. Excel Basics and Tricks (1) Use ⌘ (or Ctrl) + Arrow key combinations to quickly navigate within a large spreadsheet (2) Use ⌘ (or Ctrl) key to select multiple items (3) Copy and Paste: A dangerous act (4) Defining a range and managing range names (5) Use Ctrl-Enter to fill up a range quickly (6) Absolute reference vs. relative reference: the difference of a “$”. Chapter 2. Organizing Data (1) IF, AND, OR (2) VLOOKUP and HLOOKUP (3) MATCH and INDEX Chapter 3. Basic Mathematical and Statistical Functions (1) Summation: SUM, SUMPRODUCT, SUMIF (2) Counting: COUNT, COUNTIF, COUNTA (3) Summary Statistics: AVERAGE, MEDIAN, MODE, MAX, MIN, STDEV (4) Covariance and Correlation: CORREL and COVAR (5) Ranking: RANK, PERCENTILE, QUARTILE Chapter 4. Basic Financial Functions (1) NPV: Net Present Value (2) PMT: Payment (3) IRR: Internal Rate of Return (4) FV: Future Value (5) RATE (6) NPER Chapter 5. Text Operations





About the Author Z. Justin Ren is an Associate Professor of Business Administration in the Operations and Technology Management Department, Dean’s Research Fellow, and Senior Fellow of Health Policy Institute at Boston University School of Management. He was also a Visiting Scholar and Research Affiliate at Massachusetts Institute of Technology (MIT) Sloan School of Management from 2009-2013. For almost ten years, Professor Ren has been teaching analytics courses which focuses on tools and frameworks that help managers gain market intelligence and make strategic decisions. He also teaches in executive education in financial risk management. He is a certified teacher by the Harvard Business School Case Method Discussion Leadership Program. Professor Ren has consulted for Fortune 500 companies as well as start-ups. His work has been mentioned in Sloan Management Review, China Daily, and the World Financial Review. He has given lectures and presentations around the world at institutions such as Harvard Business School, MIT, Duke, Dartmouth, Georgetown, Emory, INSEAD (France), Hong Kong University of Science and Technology, Peking University (China), and Tsinghua University (China). Professor Ren has received several recognitions, including the INFORMS George B. Dantzig Dissertation Award, INFORMS Junior Faculty Paper Competition Award, and the Production and Operations Management Society (POMS) Wickham Skinner Early-Career Research Accomplishments Award. Professor Ren received his M.S. in Operations Research and Ph.D. in Operations and Information Management from The Wharton School at the University of Pennsylvania.



Preface to Series Dear Readers, Thank you for reading (or watching or listening to) this multimedia series on business analytics using Excel. To make sure you know what this book is really about, let me first tell you what this book is not about. It is not about learning all the powerful functionalities of Excel. It will not make you a guru on Excel. In fact, it covers a rather small subsets of functions existing in Excel. Instead, this book aims to help you become a better decision maker. A better manager or executive. A better entrepreneur. A better thinker. I have taught thousands of students over the years, and I have consulted Fortune 500 companies as well as small start-ups. Two things continues to amazes me to this day: (1) How prevalent Excel is in the workplace. In fact, many of the largest corporations in the world run many of the most important strategic analysis and decisions based on Excel spreadsheets. The same is true for small businesses, or even in managing our own daily lives. In my humble opinion, Excel is here to stay. Despite the fact that there are more and more alternative spreadsheets programs, Excel remains the undisputable go-to choice for serious spreadsheet modelers (note: this is not saying the alternatives are without virtue. In fact, I very much like Google Sheets, and I use it on a daily basis.) (2) How common it is for people to use Excel improperly or ‘dangerously’. Let me give you an example. In my consulting and teaching career over the years, I have seen too many instances where people do ‘copy and paste’ in a rather haphazard manners: ignoring formula when they should be kept, and keeping formula when they should be thrown away. The consequence of such bad practices can sometimes be devastating: It has been reported that bad Excel practices played a role in the 2009 financial crisis (Just google “London whale Exce”)! It is those mispractices that motivated me to

write a training course to teach how to do Excel analytics in a safe and proper way. This introductory series is designed for readers who are interested in building business analytics skills and applying them into business situations such as: ● Meeting budgets, ● Predicting demand, ● Selecting mortgages, ● Managing cash flows, ● Picking stock portfolios, ● Identifying trends and patterns, ● Making purchase decisions to minimize cost… When it comes to analytics with Excel, my view is that there is a set of essential skills to be acquired. Such skills include: ● Finding information efficiently; ● Using commonly used math, statistical, and financial functions; ● Building a correct and clear model to analyze a problem; ● Scenario analysis to make decision under uncertainty; ● Summarizing information (e.g., with pivot tables); ● Finding patterns among data; ● Simulation and analyzing risks; ● Optimization for better decision making. Those skill sets are taught through an extensive set of examples in this series. There are seven modules in this series, each in a book: 1: Basic Skills 2. Model Building 3. Power Analytics with Data Tables and Pivot Tables 4. Visualizing data 5. Pattern Finding, Curve Fitting, and Regression 6. Problem Solving and Optimization 7. Simulation What is exciting and unique about this series is its multimedia delivery method: ● Each key lesson is accompanied by a how-to video. For example, if you look out for this icon and click on its web link below,

goo.gl/2R31ND You will be opening a video introducing this whole course. Check it out! ● Recognizing the different computer systems being used to run Excel (i.e., Mac and PC), I have incorporated both in this book. When it comes to model building, both systems work fine and they are the same. The differences largely exist in menu structure and ‘look and feel’. Whenever necessary, I explicate those differences by illustrating both. That is the reason why sometimes you will see two video icons, as shown below in an example, one video for each system: Mac PC goo.gl/Wjvv17 goo.gl/BSEkWT For most lessons, though, it is irrelevant as to which system is being used, as they follow the same steps. In those cases, I just pick one system (Mac or PC) and go with that in shooting videos, so the ‘skin’ may look different but it should not cause any confusion. The downloadable files are readable by both Mac and PC (as well as other operating systems). ● Templates and answers for each example are downloadable so that readers can gain a first-hand experience. Look out for this sign: and its download link beneath. ● Hyperlinks are provided throughout the book for quick referencing, and easy usage. For example, click on this one: Send me your feedback,

(goo.gl/y3kJmv) and you will be led to a web page where you can send me your comments and suggestions. ● Another important advantage of owning this e-book is that you will also be enjoying free updates as I add more topics and videos to the book. Compared to traditional paper-based books, all the above features are only possible in an e-book format. Readers therefore can learn through multiple channels: by watching the how-to video, by hands-on working with the downloaded spreadsheet, or just by reading the book. The choice is yours. Please note: You will need to an active Internet connection to be able to download files and watch videos. I hope this multimedia experience will prove beneficial for you. I am eager to hear from you on your thoughts. Thank you. Z. Justin Ren, Ph.D



Chapter 1. Excel Basics and Tricks In this Chapter (1) Use ⌘ (or Ctrl) + Arrow key combinations to quickly navigate within a large spreadsheet (2) Use Ctrl key to select multiple items (3) Copy and Paste: A dangerous act ○ Paste values only ○ Transpose (4) Define a range and managing range names (5) Use Ctrl-Enter to fill up a range quickly When you open up Microsoft Excel, a window will appear which may look like one of the following figures (depending on which system you are using - Office for Mac 2011, Office 2010, Office 2013, or more recently, Excel for tablets, you may see slightly different things). Your screen and window size also determine how many menu items you will see.

(Mac: Office 2011) (PC: Office 2011)

(PC: Office 2013) Compared to the earlier versions of Excel prior to 2010, even though the big white gridded space of spreadsheet is still there, the menu structure has been revamped and is different from the previous version. Instead of the \"menu bar\" in older versions of Excel, starting with Excel 2007, the top menu area is called the \"Ribbon\". It changes dynamically depending on the context you are working in. The menu items along the tops are called tabs. Click on a tab, and the Ribbon changes to display a set of commands. The picture below shows the Home tab, which is the most frequently used tab, and has its various commands underneath. (Mac: Office 2011)

(PC: Office 2011) (PC: Office 2013) You can hide the Ribbons by clicking on the little \"^\" sign on the top right corner, just by the question mark sign (In Excel 2007, double- clicking on any of the tabs). Note that the items within the Ribbon are now invisible. Also, the \"^\" sign becomes a \"v\". To reactivate the Ribbon, click on the \"v\" (In Excel 2007, click on any tab). (Mac)

(PC) There are a few very useful skills that will allow you to navigate a spreadsheet efficiently. Let’s look at them in detail.

Report Issues (goo.gl/y3kJmv)



(1) Use ⌘ (or Ctrl) + Arrow key combinations to quickly navigate within a large spreadsheet You will be working with spreadsheets that will cover more than what your computer screen can show, or you just need a quick way to go to a certain place in a spreadsheet without using the mouse or the scroll bar. Under those situations, using such a key combinations will help tremendously. Example Here is the historical data for NASDAQ composite index: Find out the date range of this data set. goo.gl/G8kDSv Mac PC

goo.gl/hTo8ei goo.gl/75U2Wu Solution You can use your mouse to drag down the scroll bar on the right side, or you can keep pressing down the page down key till the end. Either way, it will take you a long time to find out that this long table has 10,473 records! There is a speedier way of navigating. After selecting any cell with the table, pressing down Command ⌘ + Down Arrow key (for Mac; ctrl + Down Arrow Key combination for PC), it will take you immediately to the bottom of the table. Similarly, you can use ⌘ (Ctrl) + Up Arrow, Left Arrow, or Right Arrow key to quickly go to the top, left, or right of a region. Tip: Freeze a part of data table To keep the top two rows visible (where the labels are), click on cell A3, and click on Layout → Freeze Panes → Freeze Panes (Mac) or View → Freeze Panes → Freeze Panes (PC). (Mac) (PC)

After freezing the top two rows, even if you scroll to the bottom, you can still easily check the labels of each column (notice the thin line separating the frozen pane from the regular area under row 2): goo.gl/BryHOa

Report Issues (goo.gl/y3kJmv)





(2) Use ⌘ (or Ctrl) key to select multiple items Sometime you need to select multiple cells, sometimes even non-adjacent ones. You can hold down the Command ⌘ (for mac) or Ctrl key (PC) key while selecting them using your mouse. Example Select the highlighted cells in the worksheet below, copy and paste it to another worksheet. Mac PC goo.gl/Wjvv17 goo.gl/BSEkWT goo.gl/WQgvmD Solution In this example, there are three disjoint rows of data that need to be selected. Hold down the Ctrl key while using your mouse to select all three

rows of data. Then press Ctrl-C to copy, and paste it to a new worksheet. Tip: Zoom in and out quickly A less well known but very useful trick is to use keyboard and mouse together to zoom in or out of a spreadsheet. To do that on a mac, go to a spreadsheet, press control + command (⌘), and then use your mouse scroll wheel to zoom in or out. On a PC, it is just control + mouse scroll wheel.

Report Issues (goo.gl/y3kJmv)



(3) Copy and Paste: A dangerous act Most of us know how to copy and paste efficiently – just hit Ctrl-C (or ⌘-C for mac) to copy, and then Ctrl-V (or ⌘-V for mac) on your keyboard to paste. But one of the most common mistakes in Excel modeling is resulting from the misuse of this “Paste” functionality. This is because “Paste” in Excel has literally 20 shades of meaning underneath: it could be pasting value, format, formula, etc. – Don’t believe it? See this dialog box on paste special for yourself. Here we discuss a few important “Paste” in Excel. a. Paste values only In Excel, when you simply click Paste, or hitting Ctrl-V (⌘-V for Mac users) on your keyboard, everything that has been copied is pasted to the new location – I mean everything – values, formulas, formatting, and even hyperlinks if any. This may not be what you want. So be careful. The good news is that Excel provides a wide range of pasting options that are easy to choose from. See screenshots below.

(Mac) (PC) If you just want to paste the value of a cell or a range, for example, click on the little upside-down triangle symbol to the right of the Paste button, and click on Values. Or, select Paste Special on the bottom to bring out a dialog box, and then select Values or other nuanced options such as Values and Number Formats. See below.

(Mac) (PC) Example In the original table below, it contains formula, highlighting, and a comment. Copy the table, but just paste its values to the designated location. That is, no formula or any formatting should be pasted.

Mac PC goo.gl/RY2dVK goo.gl/E2yl6T goo.gl/rsNp4O Solution First copy the content, and then use Paste Special. Choose values only, since this is the only thing we want to be copied over. b. Transpose Sometime you copy an entire row, but then need to reshape it as a column in order to use it somewhere. Put more generally, you may want a table to be flipped 90 degrees, i.e., transposed. You can accomplish it by copying the content, then clicking Paste Special, and then checking the Transpose option. Example Copy the original table, transpose it, and paste it to the designated location.

Mac PC goo.gl/vDjUfN goo.gl/THAzHN goo.gl/HP9b82 Solution After copying the table, select Paste Special to bring out a dialog window. Check Transpose on the bottom.

(Mac) (PC) After clicking OK, you will see that the table is now transposed in the new location.



Report Issues (goo.gl/y3kJmv)



(4) Defining a range and managing range names One of the very useful features in Excel is that you can give names or aliases to any cell or group of cells so that you can refer to them in calculations. For example, you can name cell A1 “revenue” and B1 “cost”. To define range names, you can just select the cell(s), and then type in the text box just above cell A1, where default names of cell coordinates are given. When you calculate profit in cell C2, instead of type in the formula “=A2- B2”, you can just enter “=revenue –cost”. Pretty neat, isn’t it? Mac PC goo.gl/X5UhQK goo.gl/MtXlTn goo.gl/HvdalB Also note that once you defined a cell, the cell now carries the new defined name. For example, if you click on B2 in the example above, the formula bar will show its name as “cost”, as opposed to its original default name “B2”. The benefit of using range names is thus apparent: it helps in understanding relationships between cells. It is easier to read profit being “=revenue-cost” instead “=A2-B2”, isn’t it?

To later change and delete defined names is easy. Go to Insert →Name →Define. There you can also manage other earlier defined names.

Report Issues (goo.gl/y3kJmv)





(5) Use Ctrl-Enter to fill up a range quickly Ctrl-Enter (on both Mac and PC) is one of the most useful and yet underutilized feature in Excel. It is also one of those useful tricks that will impress your co-workers – yes, it is that good! Frequently we need to fill up an entire row, a column, or a table with a formulated calculation or number. Many know how to use mouse to “click- and-drag”. A quicker way is to select the range you want, enter (or paste) the formula, and hit Ctrl-Enter. Suppose we have now two columns, one representing revenue (in A2:A11), the other cost (in B2:B11), we can quickly fill the profit column using this feature. Select the column C2:C11, enter formula “=A2-B2”, and hit Ctrl- Enter. –Voila! goo.gl/bj3hfM goo.gl/Riv7fl

Tip: Another feature of Ctrl-Enter is to fill up a range with a single number. Suppose you want to fill up a 4 × 4 table with “1”. A quick way to achieve this is just to: (1) Select a 4 × 4 range with your mouse (B2 to E7, for example); (2) Type a “1”; and (3) Ctrl-Enter! Another benefit of using Ctrl-Enter instead of using “click-and-drag” is that it only works on formula and therefore does not copying any of the formatting over. In contrast, if you use mouse to click-and-drag all the formatting from the first cell will be copied over to other cells, which sometimes results in not- so-pleasing effects (for example, if you have added some borders to the first cell)

Report Issues (goo.gl/y3kJmv)