The post excel case study and need guidance to help me learn.
Need help going over my excel homework for class
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
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 1 Homework Module 1 Homework #1 Building Models in Excel You are the operations management analyst that supports operational decisions in your organization. There are 2 issues that have come up for which the leadership team needs your analytic expertise. The first issue involves an Excel analysis and written 1 slide Powerpoint summary regarding PCP match and PCP coverage. The second issue entails conducting a simulation model of contract bidding. The Excel portion of the grading will be based on your arriving at the correct answers in the cells indicated. Even if you arrive at the correct answers in the cells, full credit will not be given if you hardwired in numbers into your formulas, rather than inserting the appropriate cell references. Your PowerPoint slide for problem 1 should include your heatmap? table of results, and should provide a brief summary of your analysis and the so-what? of your findings, based on the information that was given to you about PCP match and PCP coverage. Which physicians look like they have too many patients? Which look like they are new physicians? (I usually copy the table, and then do a paste special picture into PowerPoint.) (ONE SLIDE ONLY) Point allocations for the problems are: 1. Problem 1: 25 points a. 5 points for the PowerPoint slide 2. Problem 2: 25 points Note that in turning in your homework, you are asserting the following: ?? The answers represent your own work. You did not copy answers from any other students, or access any of this classs previous years materials to complete this work. ?? You can consult with other students in your class, but they should NOT send you their file, or a part of their file, that you then copy and paste into your own file and claim as your own work, or copy their write-up, changing a few words here and there. If a fellow student offers some help to you via zoom or other online collaboration tool, you did NOT take screenshots of their work they may have shown you. ?? Similarly, if you are being asked for help, you should not email your completed file to the student(s) who is asking for help. ?? To do any of these things constitutes academic misconduct, and will result in an F for the course. To submit your work: 1. TYPE YOUR NAME in the Excel worksheet called Academic Conduct Signatures to verify that the work you are turning in represents your own work. 2. Rename the Excel file and PowerPoint file with hw1 3. Submit both the Excel file and the Powerpoint file to Canvas. Do NOT email the files to me.
UCLA HLTADM 413 Operations Management in Healthcare ?Sandra Potthoff, PhD 2021 Building Models in Excel? Page 2 Problem 1: Advanced Access Performance Metrics Your clinic has implemented advanced access, an outpatient scheduling technique designed to provide same-day appointment access. For more background information on Advanced Access, see Gupta, D., Potthoff, S., Blowers, D. and Corlett, J. (2006). Performance Metrics for Advanced Access. Journal of Healthcare Management. 51(4):246-259. You can access journal articles free of charge by logging in on the Universitys library page and searching by the article title if you want to read the article. As part of the performance monitoring feedback mechanisms the clinic has implemented, two key quality metrics are tracked to monitor advanced access performance in your clinic: ?? PCPmatch (PCP=Primary Care Physician): the proportion of patients who came to the clinic who got to see their designated provider. (When a patient signs up for insurance, they have to indicate which doctor they wish to choose as their primary care physician. The goal is that each patient should be able to get in to see his/her own PCP when s/he comes in for an appointment.) ?? PCPcoverage: the proportion of a physicians appointments in which s/he saw other physicians patients as opposed to his/her own. (Thus, the physician is covering for other physicians in the practice.) The goal is that for an established physician, their appointment slots should be filled as much as possible with that physicians patients and not other physicians patients. Generally, for an established physician, one wants PCP match to be as close to 1 as possible, and PCP coverage to be minimal. However, for new physicians who are still building their practices, both PCP match and PCP coverage will likely be high (these physicians see all their own patients, but still have room in their schedules to take excess appointment demand for other physicians patients). If PCP match and PCP coverage are both low for a physician, this could indicate that the physicians panel of patients is too large. Helpful Excel Information The Homework 1 Excel file contains appointment data for December 2021 in the worksheet called 1.appt_Dec2021. The original variables provided were: ?? Date, ?? PCP_Prov_ID (this is the identification number of the primary care provider of the patient, but not necessarily the physician the patient saw for this appointment), ?? Appt_Prov_ID (this is the identification number of the physician the patient saw for this appointment), and ?? Specialty. The red triangle in the corner of cell G1 (where Specialty header is) means that there is a comment for that cell. If you move your cursor over that cell, the comment will appear. EXCEL TIP: How to insert comment: 1) on MAC; 2) on PC – place cursor in cell you want to have a comment, choose Review tab, choose New Comment (or Edit Comment if there is already a comment in the cell).
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 4 ?? the value to be looked up in the first column of the table array is in cell B2, ?? the day_of_week is the table array to look in, ?? look in column 2 of the table for the value to be returned, ?? false means the value to be looked up in column 1 must be an exact match (true means look for the closest match in column 1 and return the corresponding value in column 2). Your Analysis Your 2 minute analysis Your boss asked you for a quick overall PCPmatch percentage for December, the meeting is in 2 minutes and she is breathing over your shoulder asking for the number. Here is what you need to do to make that calculation. 1. In the PCPmatch column (column F), create an indicator (0,1) variable using an if statement. If the PCP_Prov_ID matches the Appt_Prov_ID then it should return a 1, else 0. This should be done for Cells F2 through to F3430. =if(D2=E2,1,0), and fill to cell F3430. 2. You want to use these 0-1 indicators to calculate your percentage. The sum of the 1s divided by the total number of visits equals the PCP match percentage. Calculating the percentage requires three steps: Step 1: Sum the cells in Column F. In cell F3431, sum the numbers. =sum(F2:F3430). ?? Make sure your cursor is in cell F3431 and type =sum( ?? Then scroll up and click on cell F2, and then scroll down, and then hold down the shift key before clicking on F3430, ?? Then type ) and then hit the enter key. Step 2: Count the number of visits represented in Column F. In cell F3432 count the visits. =count(F2:F3430). ?? Make sure your cursor is in cell F3432 and type =count( ?? Then scroll up and click on cell F2, and then scroll down, and then hold down the shift key before clicking on F3430, ?? Then type ) and then hit the enter key. ?? This counts the total number of entries in your column, which in this case represents the count of all appointments in December. ?? [A quicker way is to put a $ in front of the 2 and the 3450 in the formula in cell F3431; e.g. =sum(F$2:F$3430). This will make the row number an absolute reference, rather than a relative reference. Then copy the formula in cell F3431 to cell F3432 and change the word ?sum to the word ?count.] Step 3: In cell F3433, divide the sum in cell F3431 by the count in cell F3432. = F3431/F3432 ?? Make sure your cursor is in cell F3433 and type = ?? Then click your cursor on cell F3431, then type in the division sign / and then click on cell F3432 Your complete analysis For tomorrows meeting, you have been asked to present PCP match and PCP coverage data by a physician. The clinic considers itself successful if PCP match is at least 80% for each physician. They also look for physicians in which PCP match and PCP coverage are both low, as it can indicate that the physicians panel of patients may be too large if this persists on an ongoing basis.
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 10 11. To create the summary data table you need, create a table to the right of your pivot table that looks like below. Type the header Advanced Access Performance Metrics? in one row, all in one column, although it will look like it spills over into adjacent columns. To center this header under the 3 columns that will be below it, choose the 3 cells that the header spans, then under the Alignment option, click on Merge and Center. Now in the next row, type Physician? in the first column, PCP Match? in the second column, and PCP Coverage? in the third column. Click the centered lines under Alignment to center. Now go up to your pivot table, copy the physician ID numbers in the PCP_Prov_ID column, and paste them into your table under the Physician header. Advanced Access Performance Metrics Physician PCP Match PCP Coverage 12. For the PCP Match cell for Physician 100, in your summary table, go to the appropriate cell in the PCP Match column of your summary table, type an = sign, and then scroll up to the pivot table and click in the cell where the PCP_Prov_ID and Appt_Prov_ID for Physician 100 intersect ( the 68% number). Then hit return. Repeat this for all physicians. 13. When completed, the PCP Match column should be filled. However, the formulas are still referencing the pivot table, so if you choose a different view of the pivot table to look at new data in your pivot table, the numbers in PCP Match in your summary table would change also, which you dont want. To fix this, highlight all the PCP Match cells in your summary data table, and do a Ctrl + C to copy them. Then choose the down arrow below the Paste button in the Clipboard section, and under the Paste options choose Values. This will cut the link to the pivot table reference location.
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 14 Problem 2: Contract Bidding Your organization is considering bidding on a contract to supply health care services to a medium-sized company for the next year. Your system believes it will cost $5,000,000 to provide the service, although you think this is the mean value of a normally distributed cost function, with a standard deviation of $100,000. Your Accountable Care Organization wants to determine the amount to bid to maximize expected profit. You know there are two competitors who will also likely bid for the contract. Although you don’t know exactly what they will bid, based on historical knowledge, you believe the following about the potential distribution of their bid: Competitor A: Bid amount is likely to be normally distributed with a mean of $5,250,000; standard deviation of $100,000 Competitor B: Bid amount is likely to be normally distributed with a mean of $5,500,000; standard deviation of $250,000 If your system does not have the minimum bid, you will not get the contract. However, if you bid too low, the contract will not be profitable. You are considering bids of $5,000,000, $5,250,000 and $5,500,000. Your boss has asked you to model this problem and get back to him with a recommendation. Under each of these 3 scenarios, what’s the expected profit if you get the bid? What’s the probability you won’t break even if you get the bid? What will your recommendation be? To conduct the simulation to answer these questions, do the following: 1. In the worksheet called 2.bid, you are going to generate 3 sets of random variables. One set is competitor As bid (column A), the second set is competitor Bs bid (column B), and the third set is the contract cost (column C). To generate the random numbers, you need to add the Data Analysis Tool Pak. Choose the File tab on the far left of the screen and then choose Options. Choose the Add-Ins option. On the next screen, at the bottom there will be a drop down box to the right of the word Manage?. Choose Excel Add-ins from the drop down menu and then hit the Go button on the right. It will bring up an Add-Ins box that looks like below. Choose the Analysis ToolPak option and then hit OK. (The picture below shows Solver Add-in as also being chosen, but this is NOT needed for this problem.)
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
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