EXCEL EXERCISES The purpose of these exercises is to get you more comfortable building a dataset from multiple sources, and then analyzing it to provide further insight. You will likely need to reference the \"Excel Notes\" that I've provided. [recommend saving the notes to your computer desktop so that you have them for a reference whenever you may need them in the future. The exercises will start out with some of the simpler commands (e.g. adding columns together), and progress to more complicated commands (eg. concatenate, vlookup, etc.) With continued practice on, and comfort with, the commands below, you will be able to creatively apply what you've leamed to conduct analysis that meets the overwhelming majority of the data analysis needs of even the largest companies across all industries. Feel free to work at your own pace. NOTE : All commands discmsed are for PC '5. If you have an Apple computer, some command: may be slightly different. Files Save all four of the files provided to an accessible location. Begin by opening the le titled "Sample Data". Take a moment to assess the data provided. It is an example dataset of a national physical therapy provider. On the \"Hours" tab, you should see seven columns and 610 rows. It provides you with clinics across different states, with the labor hours worked at that clinic for that PPE (pay period ending). \"Clinical\" refers to physical therapists, \"Support Staff\" refers to physical therapy aides, and \"Non-Clinica " refers to administrative staff (people like you and me). As you can certainly ascertain yourself, the data is by state, by clinic, by PPE (two week periods) because that is when hours are reported (for payroll purposes). On the \"Visits & Charges" tab, you'll see the gross charges (before insurance adjustments), visits (patient Visits to the clinic), and units (number of CPT codes (number to identify what was performed on the patient) executed). Again, these are broken down by state, by clinic, by PPE, Whv is the data in the "Visitr & Charges " tab categorized by state by clinic by PPE? Certainly, you might think, this data could be displayed by other date ranges. You would be correct in drinking this. However, if we want to examine the charges, visits, and units with respect to the labor it took to generate this production, then it needs to match the date ranges that we have for the hours worked. This is why the data was pulled by PPE. Lastly, you'll see the \"Labor Expenses\" tab This tab is almost identical to the \"Hours\" tabr Except, this time, we have the money spent on each labor category instead of the hours worked for that PPE at that clinic in that state. Also, where we have the total hours worked by all labor categories, we don't have the total money spent on all labor categories combined. Part I Sum Let's use this opportunity to create a column that sums the individual labor expense buckets. In cell 01, type \"l'otal Exp". Now, in cell 62, add the three labor expense categories together. You can do this in a couple of different ways, both illustrated below. =d2+e2+f2 You should get $19,658.51 for state=A1, clinic=1, and PPEt/19/20008. This approach is straightforward, but could become long and cumbersome if you have a large number of columns to add together. Alternatively, you could use the following: =sum(d2 : f2) With this command, you are telling Excel that you want to sum a group of numbers. \"d2\" tells Excel where to start. The colon after that basically tells Excel \"and keep going until\". Then you type the cell where you want to tell Excel to stop, f2 in our case. This same approach can be used to sum cells in the same column (e.g. =surn(d2:d610)). We now have the three labor categories summed for the second row. However, we need to sum them for all of the rows. You could repeatedly type one of the above commands into each corresponding cell in column G. However, I doubt you want to do that. Moreover, your boss will probably be upset with you for spending that much time performing that task. Instead try one of these three possible alternatives. 1) If you click on cell 62, you should see a bold black outline around the cell. In the bottom right comer of that should be a little black box. If you click and drag that box all the way to row 610, it will copy the formula down for all of the rows (automatically adjusting the formula for the row that it is in). Instead of perfomring the click and drag, double-click on that same black box. It should send the formula all the way down in the same manner that the click and drag did. NOTE: Be carell with this method. Sometimes, if there is an empty cell in one of the neighboring columns, it will stop there instead of going all the way down. Always double-cheek to make sure it went all the way down. You could also copy the formula (right-click on the cell or, aer selecting the cell, hit Ctrl+c), and then paste it into the desired cells. Paste by highlighting the cells, right- clicking and selecting paste. Alternatively, you could highlight the cells and hit Ctrl+v to paste the formula into the cells. Open Formulas It's good practice to not leave active formulas in your spreadsheet unless you have good reason to do so. There are many reasons for this. If you accidentally change data in one of the referenced columns, it will also make the new value change. Also, Excel doesn't have the \"horsepower\" that the more advanced statistical software packages have. It is constantly performing the formulas you have open, displaying the returned answer. The more of these you have going, the slower Excel becomes. Lastly, if you want to perform a vlookup (referenced later) to bring data over to another location, Excel won't nd the data needed. In Excel's mind, the only thing there is a formula. Paste Special Values It's important to remember that, when you have active formulas, the value being displayed isn't what's actually in the cell. The formula is in the cell. However, this is easily changed by using a version of copy and paste. Take column G (with the sum formula active for all of the rows), and copy the entire column by right-clicking on the entire column (right click on the \"G" at the top of the column). After you've copied it, we're going to paste the actual values (the sums desired) right on top of the formulas. To do this, rst right-click on the entire column again. This time, we're going to \"paste special values\" by selecting the clipboard that has \"1 2 3" on it. Now instead of the cells having active formulas in them, the actual values from the equation are what Excel recognizes as being in the cell. The formulas themselves are now gone. Get External Data We've got a lot of valuable data between the three tabs. There is another key piece of information, though, that we don't yet have in this le RENT! Rent for the facility is a vital, factor to consider. This information is in a separate le titled \"Rent\". But, it's not an Excel le. It's a text file. Again, you're likely to have data sent to you from many different sources. A lot of soware packages will export data as a text le. Not to worry, though. Excel can bring it into a spreadsheet relatively easily. First, you need to insert a new spreadsheet. Along the row of tabs at the bottom (Hours, Visits & Charges, Labor Expenses), you should see a \"+\" at the end. Click on that to insert a new sheet. Make sure that cell Al is selected. This way, when we import the rent data, it starts it in cell A1. Now, along the top row of tabs (Home, Insert, etc.), select \"DATA\". Then, within \"Get External Data", select \"From Text\". It will then have you select the le. Find the \"Rent\" le that you saved, and select it. The Text Import Wizard should now pop up. In Step 1, you need to select how Excel should separate the information in your le into different columns. \"Fixed Width" means that there is a distinct distance marking that should be used to begin each new column. If you use this, it will give you the ability to tell Excel where each distance marking for a new column should be. However, the data here is \"Delimited\here, so simply click \"Finish". Make sure, in the following pop up box, that you put the data into the existing work sheet starting in Al (=$A$l). You should now have four columns in this new tab: State, Clinic, PPE, and Rent. Label this newly created sheet \"Rent" by double-clicking on the tab at the bottom (will likely currently say \"Sheetl\information in that cell. Knowing what it needs to match on, Excel now wants to know where to go looking. So, after the \\"Fields, Items, & Sets", then choose \"Calculated Field\". You should see a pop-up box allowing you to name the new eld and enter the formula. Let's name it \"Productivity". In the formula box, type the following: = Visits/( 'Clinical Hours/8) For \"Visits\" and \"Clinical Hours\This chart provides a very clear, easily understood visual presentation of the performance by each state for the data. This could be further adjusted by formatting the colors, gridlines, etc. I encourage you to do just that, and see how you can make it look professional while remembering to keep it simple. Let's also create a slightly different PivctChart, the Line Graph. Go to the PivotChart option as before. This time, though, select, the Line option, Map out the visits per day by PPE, with a lter option for states. It should look similar to the one below: Total m Again, this is very useful for spotting trends across time for the company. The state lter also gives us the ability to view the individual trends by state, You should also practice with these in alternate forms and formats, keeping in mind that it needs to be easily understood by your audience. Descriptive Statistics When presenting research, especially for scholarly purposes, it is appropriate to illustrate the characteristics of your data. This is often done by constructing a table of descriptive statistics. While they can change slightly from one to the next, the basic premise remains the same. You want to display your key variables, with important information about each one. This often includes the total number of observations for your dataset, as well as the mean and std. deviation for each variable. You can achieve this easily within your PivotTable. When selecting from the \"Value Field Settings\" as before, instead of choosing \"Sum", you can now choose \"Average" and \"Std Dev\". The Pivot Table will now automatically calculate these for each variable you select. You can also simply drag the variable into the \"Values\" box twice so that you can display both simultaneously. It is then easily to formulate the table to properly display it in your nal research product. Correlation & Multivariate Regression \"NOTE: You may need to install the \"Analysis " Add-In package for your Excel software for there next Steps '* Go back to your main dam tab that is feeding your Pivot Table. Highlight all of the data, then select \"Data Analysis" at the far right of the menu bar under the \"Data\" tab. First, select \"Correlation\explanatory variables (Input X Range). Doing this should provide you with an output similar to what is below: SUMMARY OUTPUT Regression Statistics 0.618995 Multiple R 847 0.383155 R Square 859 Adjusted R 0.380097 Square 128 97.46219 Standard Error 147 Observations 309 ANOVA Significanc df SS MS OF 3569667.34 1189889. 125.2662 4.11398E- Regression 3 115 703 63 5746821.65 9498.878 Residual 605 766 9316488.99 Tota 608 Coefficien Standard Upper Lowe Upper Error t Stat P-value Lower 95% 95% 95.0% 95.0% 19.65314 10.0991628 1.946017 0.052115 39.48682 0.180526 39.48682 Intercep 67 414 297 0.1805266 011 011 0.768673 0.05825582 13.19478 4.05034E- 0.6542648 0.883081 0.654264 0.883081 Clinical Hours 057 38 35 0.005821 55 259 855 259 Non-Clinical 0.354490 0.12808997 2.767508 0. 1029350 0.606045 0.102935 0.606045 Hours 05 238 76 024 076 024 Support Staff 0.392661 0.05750685 6.828082 2.09842E- 0.2797242 0.505598 0.279724 0.505598 Hours 562 5 677 62 862 262 862 While this is not a statistically strong model for many reasons, this exercise does at least walk you through how to perform a basic regression in Excel. It gives you various measures for the "goodness of fit" for the model. Here, we have an Adjusted R-square of 0.38. Meaning that about 38% of the variation in the number of visits is explained by the explanatory variables here. The coefficient estimates tell us more precisely the impact that a change in any of one those variables (the various hours worked categories) has on the number of visits. The P-value tells us the level of statistical significance that each of those coefficient estimates has (how much we can trust it). Again, this is a rough regression meant for illustration purposes only. However, you are encourages to try alternate regressions with different dependent and explanatory variables to see how it changes