# excel case study and need guidance to help me learn. Need help going over my excel homework for class Requirements: UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Pot

The post excel case study and need guidance to help me learn.

Need help going over my excel homework for class
Requirements:
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Pot is a property of College Pal
College Pal writes Plagiarism Free Papers. Visit us at College Pal – Connecting to a pal for your paper

excel case study and need guidance to help me learn.

Need help going over my excel homework for class
Requirements:
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 3 You can see that two additional variables were created in the event that in the future you need to conduct any analyses by day of week: Weekday: Column B uses the Weekday function to calculate the day of week of the Date shown in Column A. This excel function determines the day of week for any date, and returns a number that ranges from 1 through 7. =WEEKDAY(A2,2) means: ?? what is the day of the week for the date in cell A2, ?? the 2 after the comma starts the numbering so that 1=Monday. A 1 instead of a 2 after the comma means that 1=Sunday. EXCEL TIP: If the result isnt a 1 through 7 after hitting the enter key, it usually means that the cell does not have the correct number format. To change the number format, highlight that column by putting the cursor on the B at the very top of the column and it will turn to a black down arrow. Left click to highlight the whole column. Then choose Home Tab, and on the Number group, click on the little down arrow to choose Number. To get rid of any numbers to the right of the decimal place, click on the button with the .00 arrow right .0. Day_of_wk: A 1 through 7 isnt very user friendly to indicate a day of the week. To create the Day_of_wk column of data, you will see on the worksheet called weekday that a table has been created in cells C4 through D10 that is called day_of_week. You can see the name show up in the upper left hand side name box by highlighting cells C4 through D10. (Yes, groups of cells can be named just as individual cells can be named.) This is a LOOKUP table that will be used for column C of the appt_Dec2021 worksheet. When you go back to cell C2 of the worksheet called 1.appt_Dec2021, the =VLOOKUP(B2,day_of_week,2,FALSE) means (the V stands for Vertical; there is also a related function called HLOOKUP for Horizontal):
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 5 Your goal is to create a table titled Advanced Access Performance Metrics. It contains 3 columns: Physician (counting by 100’s going down the column), PCP match (in percentages) and PCP coverage (in percentages).
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 6 To do this, you will need to create an Excel Pivot Table. 1. Highlight cells A1 through G3430 by clicking on cell A1 and then scroll down until you can see cell G3430, then hold down the shift key while simultaneously clicking on cell G3430. (If you had not put the information in cells E4341 through F3433, you could simply click in the little box to the left of column A above row 1 to select the entire worksheet. But if you do that in this case, it will include those 6 cells in the pivot table, which you do NOT want. This is why it is a good idea to NEVER include extraneous cells of analysis in a set of data you will be analyzing using pivot tables.) 2. Choose Insert, then Pivot Table. Because you highlighted your range already, the wizard pivot table information should look as below. Make sure New Worksheet is selected and hit OK. (For Mac Users using Mac Excel, the Pivot Table icon shows up when you click on the Data Tab, choose that you want to Create a Manual pivot table if it is asking you to choose either Automatic or Manual, and choose to put the pivot table on a New Worksheet.)
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 7 3. You will get a new worksheet. 4. Double click on the worksheet name tab and change its name to pcp_pivot. Excel Hint: If the Pivot Table Fields window on the right disappears from your screen, clicking in the Pivot Table area will make it reappear.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 8 5. Highlight PCP_Prov_ID on the pivot table field list and DRAG it to the Row Labels by holding down the left mouse as you drag it. 6. Highlight Appt_Prov_ID on the pivot table field list and DRAG it to the Column Labels. 7. Highlight PCP_Prov_ID on the pivot table field list and DRAG it to the Values. However, we dont want the Sum of Values because this is summing the numbers in the PCP_Prov_ID cells. This doesnt make sense because the sum of the ID numbers is meaningless. We want the count because this will record the number of times each PCP_Prov_ID showed up with each Appt_Prov_ID. 8. To get the Count, click on the down arrow in the Values box and choose the Value Field Settings option. Choose Count and then hit OK.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 9 9. If you want to make it easier to see all the columns without having to scroll to the right, you can reduce column size by moving the cursor up to the part of the column that has the column letter in it. As the cursor moves over the lines dividing the columns, it will change from a white cross to a black cross. Double click when it does this and it should make the column only as wide as it needs to be to still show the data in the column. 10. Each row represents all of a physicians patients that came in, regardless of which physician that patient saw. For example, 310 of physician #100s patients came in, and 211 of them saw physician #100. Each column represents the patients a physician saw, regardless of which PCP that patient belonged to?. So, physician #100 saw 235 patients, of which 211 were his/her own patients. Calculate PCP Match So, to calculate PCP match, one needs to calculate the row percentages. Click on the down arrow on Values, choose Value Fields Settings, and then when the box pops up, choose the Show Values As tab, and then choose % of Row Total. Before you click the OK button, click the Number Format button, and change the format to Percentage and change the decimal places to 0. Hit OK to get back to the Show values as, and then hit that OK to present the data as row percentages.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 11 Calculate PCP Coverage 14. To calculate PCP Coverage, one needs the column percentages. Remember to click anywhere in the Pivot Table to bring back the Pivot Table Fields window on the right. Click on the down arrow on Values, choose Value Fields Settings, and then when the box pops up, choose Show values as, and then choose % of column. Before you click the OK button, choose the Number Format button and make sure the Percentage format is selected and the decimal places are 0. Hit OK to get back to the Show values as, and then hit that OK to present the data as column percentages. This should change the numbers in each cell of the pivot table to the column percentage. However, in this case, the percentage at the intersection of matching PCP Prov_ID and Appt_ID represents the proportion of that physicians patients that s/he saw. So, PCP Coverage will be 1 minus that percentage. So, the PCP Coverage for Physician 1 is 1-.9, etc. 15. Complete your summary table for the PCP Coverage column by getting the appropriate data from the pivot table. So, for Physician 100, the cell entry would be =1-B5, etc. Then do a copy, paste special, values for the PCP Coverage column to break the relative reference links to the pivot table. 16. Now you want to create a heat map? that has the following logic. If the PCP match is =.8, then you want to turn the cell color GREEN. So, 2 rules for this column. Then in the PCP coverage column, if the PCP coverage is >=.2, then you want the cell in this column to be GREEN. And, the second rule for this column is if the PCP match is <.8 and the PCP coverage is <.2, then you want this column to be RED. To do this requires Conditional Formatting. a. Make sure the Home tab is chosen. Highlight the cells containing the numbers for your PCP match. For example, if they are in cells C25 through C38, this is what you will highlight. Choose Conditional Formatting. b. Click on the little down arrow, and then choose New Rule. c. When the text box comes up, choose Use a formula to determine which cells to format
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 12 d. =C25=.8 green. Highlight the cells, etc. f. Once this formatting works, move to the PCP coverage column. Highlight these cells for your conditional formatting. At the Format applies to box, type =AND(C25<0.8,D25=.2 h. If you get stuck, search the internet for conditional formatting in Excel?.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 13 17. Note that if you want to see the data in the pivot table for different days of the week, you can do the following (not required for the homework assignment.) Drag the Day_of_wk into the Report Filter. You will see Day_of_wk appear in cell A1, and (All) in cell B1. Choose the down arrow in cell B1 to choose different days of the week. The pivot table will filter the data to show only the day of the week that you specify.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 15 Now click on the Data tab at the top of the screen, and on the far right there should be an Analysis section with Data Analysis as an option. Click on it and choose the Random Number Generation option and hit OK. 2. Complete the Random Number Generation as follows: ?? Number of variables: 1 ?? Number of random numbers: 100 ?? Distribution: Normal ?? Mean = 5250000 ?? Standard Deviation = 100000 ?? Random Seed = 1 Click the Output Range option; click on the little icon on the right hand side of the white box, click on cell A3, then click on the icon on the right hand side of that input box, and finally click OK. To make the results easier to read, highlight the cells in column A and choose the Home Tab, choose the little arrow to the right of the Font group, choose Number tab, choose Currency, and change decimals to 0.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 16 3. Repeat step 2 for Competitor B, except change the data to reflect Bs bid values, and put those results in Cell B3. 4. Repeat step 2 for the Contract Cost, except change the data to reflect what is stated above, and put the results in Cell C3. 5. In columns D, E, and F, you need to calculate a 0,1 indicator variable that compares your possible bid values (represented in cells D2, E2, and F2) to each of the 100 bid scenarios you generated with your simulation for Competitors A and B. For each cell, the logic is if your bid is smaller than the minimum of your two competitors, you will get the bid (indicator set to 1), else you will not (indicator set t0 0). You will need to use a MIN function embedded within an IF function for each of the 100 scenarios. So, for example, in cell D3 you will create an IF statement that compares the \$5,000,000 bid to the bids in A3 and B3. If \$5,000,000 is smaller than the minimum of those 2 bids, then the cell should get a 1, else a 0. Do NOT type \$5,000,000 in each of the formulas for column D, simply click on cell D2 when you get to the point in the formula where you need it. You can see D2 is named five_mil?. The formula for cell D3 will look as follows: =IF(five_mil<MIN(A3,B3),1,0). Put the appropriate formulas in columns D, E, and F. 6. You now have the information you need to determine the proportion of the time you would expect to get the bid. In cell D103, divide the sum of column D by the count of column D. Repeat for E103 and F103. (You used sum and count in the first problem of this homework.) 7. In columns G, H, and I, you need to determine what your profit (or loss) would be under each of the 3 bid numbers under consideration. Clearly, if you dont get the bid, the profit cell should be left blank. If you do get the bid, the profit (or loss) is the difference between what you bid and what the contract costs. So the logic requires an IF function, where if you didnt get the bid, then leave it blank, (Blank is indicated by putting two double quotes right next to each other like this: ?), else the cell is the bid amount minus the contract cost. If you reference cells G2, H2, and I2 for the bid amounts in your formulas, be sure to lock those cell references with dollar signs like this: \$G\$2, \$H\$2, etc. 8. In cell G103, calculate the expected value of the profit for a bid of \$5,000,000 by using the AVERAGE function. Repeat in cells H103 and I103 for the other bid values. 9. Now you need to create a line chart that shows the profit distribution on one chart. Choose cells G3 through I102 and Copy, Paste Special, Values into columns J through L. (Highlight cells G3 through I102) and do a copy. Then put your cursor in cell J3 and do a paste special values. See Step 13 of Question 1 of the homework if you dont remember how to do paste special values.) 10. Now highlight Cells J3 through L102 and then choose Sort & Filter on the Home tab, then choose Sort Smallest to Largest (if you get a message asking if you want to Expand the Selection or Continue with the Current Selection, choose Continue with the Current Selection). 11. To plot all three on one line chart, you need to highlight the cells that have numbers in them, one column at a time. In my worksheet the first number in column J is showing up in cell J3, and the last one
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 17 is in J97. So, click on cell J3, and then use the scroll bar on the right hand side of the spreadsheet to scroll down to cell J97. HOLD DOWN THE SHIFT KEY WHILE YOU CLICK ON J97. (If your first number of column J is showing up in cell J4, then you would start in cell J4. For some reason, sometimes when I am sorting the data, it shows a blank in cell J3, and the numbers start in J4 and end at J98. Where ever data start in the column is the first cell you will choose, and then scroll down and choose the last number in that column) 12. Now, without clicking anywhere else in the spreadsheet, use the scrollbar on the Right hand side of the spreadsheet to scroll up to the top of the spreadsheet. HOLD DOWN THE CTRL KEY and click on cell K3 (or K4 if that is where the data start in column K). (On a MAC, it will be the COMMAND KEY instead of the CTRL KEY). 13. Now scroll down using the scrollbar to cell K53 ( or K54), and HOLD DOWN THE SHIFT KEY WHILE YOU CLICK ON K53 (or K54). 14. Now scroll up using the scroll bar to the top of the spreadsheet and HOLD DOWN THE CTRL KEY (COMMAND KEY IF MAC) and click on cell L3 (or wherever the first cell with data in it is showing up). Then HOLD DOWN THE SHIFT KEY WHILE YOU CLICK on cell L4 (or wherever the last cell with data in it is showing up.) 15. All cells with numbers in them in columns J through L should be highlighted when you complete Step 14. Now choose the Insert Tab, Insert Line Chart, under 2-D Line choose Line (left-most choice at the top of popup box). 16. This will create a line chart on your current Excel worksheet. It should look something like below, although if you got a different stream of random numbers generated, it may not look exactly like this. To move it to a new worksheet, right click on the outer edges of the chart, and choose Move Chart, and then click on New sheet. Name this worksheet profit_chart.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 18 . 17. The numbers along the \$0 line in the middle of the chart (the horizontal axis) are not very helpful. To make the chart look better, left click on the numbers along the 0 axis. This should put a box around those numbers. Now right click within that box, and choose Delete. This should get rid of those numbers. 18. Now you need to name the Series1, Series2, and Series 3. Right click again in the chart and choose Select Data.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 19 19. A new box called Select Data Source should appear. Choose Edit under Legend Entries (Series) with Series 1 highlighted. Type 5 mil bid and choose OK. Repeat for Series 2 and Series 3, naming them 5.25 mil bid, and 5.5 mil bid, respectively. Once you are done naming all 3 series, Choose OK on the Select Data Source Box.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 20 20. You can add a title to the chart by choosing the Design tab (you have to have the chart selected in order to see the Design tab up top), then Add Chart Element (on the left), then Add Chart Title, and then Above Chart. Create a title called Profit Distributions of Bids. 21. You want this chart to still be usable if it were printed in black and white (i.e. if someone does not have a color printer). Right click on one of the lines to select it (little boxes should show up on it). A box of options should appear. Click on Format Data Series.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 21 22. When you do that, the right hand side of your screen should show Format Data Series, with one of the options being a tilted paint can. Click on that paint to enable the ability to change the type of line. Select the down arrow on Dash Type, and choose a different dash type. Repeat for another of the lines. When done you can click on the X in the upper right hand corner to the right of Format Data Series at the top, and it will close.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 22 So, now you end up with a chart with a red solid line and a blue dotted line tracking up to the right.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 23 23. One last thing you want to calculate is the probability of breaking even under each bid. Go back to the 2.bid worksheet. In cell J104, use the COUNTIF function. To have some guidance on how to do this, put your cursor in cell J104, then click on the formulas tab, and then click on the fx at the far left. Type in COUNTIF to get the format for this function. Under range, click

The post excel case study and need guidance to help me learn.

Need help going over my excel homework for class
Requirements:
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Pot appeared first on College Pal. Visit us at College Pal – Connecting to a pal for your paper

Posted

in

by

Tags:

Get 30% off your orders today

X