# Consolidation Analysis and Staffing Strategy

29 Pages10463 Words360 Views
|
|
|
Homework #1Building Models in ExcelAll directions are given in Excel 2013There are 3 problems in this first homework, with an accompanying Excel file that must be completed. The thirdproblem requires you to also create Powerpoint slides that describe your decision and its justification.Point allocations for the problems are: problem 1: 20 points; problem 2: 30 points; problem 3: 40 points, problem3 write-up: 10 points. The Excel portion of the grading will be based on your arriving at the correct answers inthe cells indicated. Even if you arrive at the correct answers in the cells, full credit will not be given if youhardwired in numbers into your formulas, rather than inserting the appropriate cell references. Your Powerpoint write-up for problem 3 will be based on the extent to which it is effective in using charts,graphs, and words to convey the message to convince key stakeholders of your recommendations. An approachto convincing key stakeholders is to describe and show: 1) the current situation and what will happen if the statusquo continues (create the tension for change and set the vision for where the organization needs to be); 2) whatare the options to prevent the bad, and get us to the good in the future, and what are the pros and cons of thoseoptions; 3) what is your recommended option and why; and 4) what this means for the affected stakeholders. Becreative in thinking through how to put together a convincing story in Powerpoint. Lists of bullet points will bethe worst possible approach. A well-articulated story with graphs or charts and text to explain them, will garnerfull points. 1
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 return 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 isn’t 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 indicated.Day_of_wk: A 1 through 7 isn’t 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.) Thisis a LOOKUP table that will be used for column C of the appt_Dec2010 worksheet. When you go back to cell C2 of the worksheet called 1.appt_Dec2010, the =VLOOKUP(B2,day_of_week,2,FALSE) means (the V stands for Vertical; there is also a related function called HLOOKUP for Horizontal): 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).3
Your goal is to create a table in Excel that looks like this:Advanced Access Performance MetricsPhysicianPCP MatchPCP Coverage10068%10%20073%17%30086%2%40088%54%50080%98%60063%5%70092%6%80085%4%90078%15%100086%12%110071%11%120074%13%130089%13%140083%54%5
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. 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.)6

## End of preview

Want to access all the pages? Upload your documents or become a member.

### Support

#### +1 306 205-2269

Chat with our experts. we are online and ready to help.