Strategic Planning

Highlights:

• Set up a query and review the result.
• Export the result to MS Excel for use as a data file.
• Review the EXCEL file and add information from external sources.
• Analyze the Data and add calculations for current capacity and potential for growth in the region.
• Import into MS MapPoint for geographic presentation.

The Case:

Julie Somes is Director of the Mountain Urgent Care Centers of Colorado. She would like to evaluate the Colorado Front Range from dual perspectives. She would like to create a competitive analysis for her Board of Directors. The Board has also asked that she compose a preliminary evaluation of the region for the possibility of establishing additional Urgent Care facilities.

Julie has three fundamental questions to answer; where are the current Urgent Care services along the front range and what are their names? What are the prospects for establishing additional Urgent Care centers in the present? What are the prospects for the growth of new centers in the future?

The Process

Julie will generate a data set that includes the providers that match the desired specialty and location.

• She will then export the data set to Microsoft EXCEL where she will review it, and make any adjustments that she needs.
• She will add population estimates and compile information from an external source.
• She will use the EXCEL spread sheet as a data source in the Microsoft MapPoint program to generate maps of her findings.

Step 1 Enter Result Parameters:

Julie wants to include all possible organizations that provide urgent care in her result. Because Urgent Care is provided by all acute care hospitals, she will include all Acute Care Hospitals and all Urgent Care Clinics in her answer. To include the urgent care centers located at hospitals, she selects All Specialties and All Addresses from the Result Parameters (Figure 1).

Figure 1

Step 2 Enter Search Criteria:

Julie enters ‘Hospital : Acute Care’ and ‘Urgent Care Clinics’ as her specialties selections. She enters the 9 counties that comprise the eastern slope of the Colorado Rocky Mountains (the Front Range) as her County selections. She selects the search button to initiate the query.

Julie will now do most of her analysis in MS EXCEL so she immediately exports the answer to Excel and review the results from within the actual spread sheet.

Step 3 Export the Result to MS Excel.

Julie opens the TIMBERLINE MPR reporting program and selects the ‘Directory of Search Results” from the Choose Report drop down tool (Figure 2).

Figure 2

Julie selects the ‘Export’ Button on the ‘Export Plus’ pop up display and opens the ‘Export Dialog’ box. She names the file “UrgentCare” and initiates the export to MS Excel

From the tool bar she selects the Export button to initiate the export to MS Excel. The Export to Excel will prompt her for a new file name to export to (Figure 3). She decides that “urgentcareplan is a good file name and exports the file by selecting the save button.

Figure 3

Julie can now browse for the urgentcareplan.xls file and open it (Figure 4). Note that only selected columns are show for purposes of illustration.

Figure 4

Step 4 Evaluate the Information

Julie sorts the data on ‘County’ then ‘Specialty’ so that she can begin to build a new table to visualize the number of Hospitals and Urgent care centers within her geographical area (Figure 5).

Figure 5

From the sorted table; Julie counts the Hospitals and Urgent Care Centers to build a table that relates the availability of services in each service area (County).

Figure 7

Julie reviews the services vs. population data and decides that it would be helpful to view the expected change in populations. This way, she can visually show the best areas of expected growth.

Julie can now add columns to her spread sheet that calculate the forecasted changes in population over 5 year intervals.

She inserts columns into her spread sheet and inserts an equation that simply subtracts the value in the 2005 cell from the value in the 2010 cell. She repeats this procedure for each of the 5 year intervals of a 15 year period (The purple text columns in figure 8).

Figure 8

Julie now uses the data that she has compiled to create a new table that identifies the capacity of each county to serve the population. To do this, she copies the appropriate fields from the prior spread sheet and inserts a new column that simply divides the population by the number of facilities in the county. She then sorts the table for the current year’s population/facility to estimate the amount of population served. Using this estimate, she can identify which counties have the best opportunity for building new Urgent Care Facilities (Figure 9).

Figure 9

From the two spread sheets, Julie is able to identify Jefferson County as an area of current need, but it has relatively low growth projected over the next 10 years. Adams, El Paso and Weld Counties have strong current need and moderately good growth potential over the next 10 years.

Julie is now able to use her spread sheets to produce charts and graphs for presentation to the Board of Directors. One such presentation that provides a very powerful visualization is generating geographical maps of the region.

Step 5. Create a map of the current facilities

Julie now imports the original Excel file into MS MapPoint that visualizes the region using blue pushpins to identify Hospitals and red pushpins to identify Urgent Care centers (Figure 10).

Figure 10

Microsoft MapPoint is a powerful tool for importing spread sheet data into mapping displays. Julie could as easily map the population data in a number of ways to illustrate the expected change in population with respect to time. Or she could map the ratio of population to current operating centers.

Julie has answered the two basic questions that she was presented with. She knows where all of the Urgent Care services are in her region. She has identified that there are areas that present the opportunity for establishing Urgent Care Centers both in the present as well as those centers will have sustained growth potential in the future.