Within this Excel workbook (tab 2) - Rate Structures "the red cells" need to be completed as well as (tab 3) rate structure analysis
1.Instructions Harbor City Profitability Analysis Full Costing - Basic Rate Structure Approaches Instructions Step 1. Please enter your name below. Name: Step 2. You are assuming the role Morgan Gutierrez working in a consulting firm's health care advisory practice. For your first project, you have been tasked to analyze proposed rate structures for Harbor City Community Center. Luckily, you already have information on their cost structure from a full costing step-down analysis. Harbor City's executive director, Ms. Conaway, has asked for your first and last choices of the proposed rate structure. She also wants your input on the advantages and disadvantages of each one. Please follow the directions below and on the following tabs to analyze the rate structures for Ms. Conaway. Step 3. Look over the Module 5 materials before starting this analysis. This case will lead you through the profitability analysis of three pre-determined rate structures on the "2. Rate Structures" tab. An example calculation has been given in the first rate structure. Enter your answers into the yellow cells on the Rate Structures tab. Please use formulae. Do not type in hard-coded values. Step 4. On the "2. Rate Structures" tab, you will need to analyze the profitability of three proposed rate structures. An example calculation has been given in the first rate structure. Enter your answers into the yellow cells on the Rate Structures tab. Please use formulae. Do not type in hard-coded values. Step 5. On the "3. Rate Structures Analysis" tab, review the three rate structures on the "2. Rate Structures" tab based on the Equity, Implementation, and Profitability framework. Determine your first and last choice of the rate structures and enter the rate structure title on the Rate Structure Analysis tab. Give at least two advantages and disadvantages for each of your choices. Where we go from here: Case Presenters (Morgan Gutierrez) and Respondents (Ms. Conaway, executive director for Harbor City): After preparing your analysis of Harbor City's rate structure, you will record a presentation with your recommendation of rate structure, including both advatanges and disadvantages. Presenters: You are presenting your recommendation and analysis to Ms. Conaway. Record a 2-4 minute discussion of your presentation. Respondents: You are the asking questions or seeking clarification on points discussed in Ms. Gutierrez's presentation. You can respond with some of your own conclusions, but you also should pose 2-3 questions in response to the presenter's analysis. 2.RateStructures Harbor City Community Center: Rate Structures Instructions Step 1. In Section A below, review the full cost information from the Module 4 Activity. Step 2. In Section B, examples are provided for the three rate structures. Review the description of each rate structure in the blue boxes and the example calculations. Step 3. To become more familiar with the model, fill in all RED cells for the flat fee and the cost-plus pricing. Use formulae. Do not "hard-code" answers. Step 4. When you have completed the answers in this sheet, proceed to the "3. Rate Structure Analysis" tab. Step-Down Allocation Total TotalRecordTraining &Allocated Direct CostRent1Administration2Keeping3Education4CostsVisitsCost per Visit Rent$ 208,000$ (208,000)$ - Administration$ 160,000$ 10,400$ (170,400)$ - Client Records$ 40,000$ 6,240$ 6,739$ (52,979)$ - Training and Education$ 100,000$ 24,960$ 15,403$ -$ (140,363)$ - Homemaker Service$ 143,333$ 20,800$ 26,795$ 8,052$ 35,091$ 234,0715,000$ 46.81Homemaker Service Family Planning$ 111,333$ 27,040$ 12,355$ 16,103$ 7,018$ 173,84910,000$ 17.38Family Planning Counseling$ 233,333$ 37,440$ 40,755$ 3,382$ 84,218$ 399,1282,100$ 190.06Counseling Parents' Advocacy$ 73,333$ 6,240$ 11,873$ 6,441$ 3,509$ 101,3974,000$ 25.35Parents' Advocacy Mental Health$ 105,333$ 20,800$ 17,650$ 2,254$ -$ 146,0371,400$ 104.31Mental Health Alcohol Rehabilitation$ 80,000$ 10,400$ 11,553$ 2,415$ -$ 104,3681,500$ 69.58Alcohol Rehabilitation Community Outreach$ 73,333$ 22,880$ 8,023$ 4,026$ 3,509$ 111,7712,500$ 44.71Community Outreach Referral and Placement$ 120,000$ 20,800$ 19,254$ 10,306$ 7,018$ 177,3786,400$ 27.72Referral and Placement $ 1,448,000$ -$ -$ -$ 0$ 1,448,00032,900$ 44.01 Section A. Full Cost Information ServiceFull CostVisitsCost per Visit Homemaker Service$ 234,0715,000$ 46.81 Family Planning$ 173,84910,000$ 17.38 Counseling$ 399,1282,100$ 190.06 Parents' Advocacy$ 101,3974,000$ 25.35 Mental Health$ 146,0371,400$ 104.31 Alcohol Rehabilitation$ 104,3681,500$ 69.58 Community Outreach$ 111,7712,500$ 44.71 Referral and Placement$ 177,3786,400$ 27.72 Total$ 1,448,00032,900$ 44.01 Section B. Rate Structures Rate Structure: Flat Fee$ 48.00 ServiceVisitsCharge per VisitFull Cost per VisitProfit/Loss per VisitTotal ChargesTotal CostTotal Profit/Loss Homemaker Service5,000$ 48.00$ 46.81$ 1.19$ 240,000$ 234,071$ 5,929 Family Planning10,000$ 48.00$ 17.38$ - 0$ - 0$ - 0$ - 0 Counseling2,100$ 48.00$ 190.06$ - 0$ - 0$ - 0$ - 0 Parents' Advocacy4,000$ 48.00$ 25.35$ - 0$ - 0$ - 0$ - 0 Mental Health1,400$ 48.00$ 104.31$ - 0$ - 0$ - 0$ - 0 Alcohol Rehabilitation1,500$ 48.00$ 69.58$ - 0$ - 0$ - 0$ - 0 Community Outreach2,500$ 48.00$ 44.71$ - 0$ - 0$ - 0$ - 0 Referral and Placement6,400$ 48.00$ 27.72$ - 0$ - 0$ - 0$ - 0 Total32,900$ 240,000$ 234,071$ 5,929 Profit Margin2.5% Rate Structure: Cost-Plus PricingTarget Profit Margin10% ServiceVisitsCharge per VisitFull Cost per VisitProfit/Loss per VisitTotal ChargeTotal CostTotal Profit/Loss Homemaker Service5,000$ 51.00$ 46.81$ 4.19$ 255,000$ 234,071$ 20,929 Family Planning10,000$ 19.00$ 17.38$ 1.62$ 190,000$ 173,849$ 16,151 Counseling2,100$ 209.00$ 190.06$ 18.94$ 438,900$ 399,128$ 39,772 Parents' Advocacy4,000$ 28.00$ 25.35$ 2.65$ 112,000$ 101,397$ 10,603 Mental Health1,400$ 115.00$ 104.31$ 10.69$ 161,000$ 146,037$ 14,963 Alcohol Rehabilitation1,500$ 77.00$ 69.58$ 7.42$ 115,500$ 104,368$ 11,132 Community Outreach2,500$ 49.00$ 44.71$ 4.29$ 122,500$ 111,771$ 10,729 Referral and Placement6,400$ 30.00$ 27.72$ 2.28$ 192,000$ 177,378$ 14,622 Total32,900$ 1,586,900$ 1,448,000$ 138,900 Profit Margin9.6% Rate Structure: Tiered Rates ServiceVisitsCharge per VisitFull Cost per VisitProfit/Loss per VisitTotal ChargeTotal CostTotal Profit/Loss Homemaker Service5,000$ 50.00$ 46.81$ 3.19$ 250,000$ 234,071$ 15,929 Family Planning10,000$ 26.00$ 17.38$ 8.62$ 260,000$ 173,849$ 86,151 Counseling2,100$ 133.00$ 190.06$ (57.06)$ 279,300$ 399,128$ (119,828) Parents' Advocacy4,000$ 26.00$ 25.35$ 0.65$ 104,000$ 101,397$ 2,603 Mental Health1,400$ 133.00$ 104.31$ 28.69$ 186,200$ 146,037$ 40,163 Alcohol Rehabilitation1,500$ 133.00$ 69.58$ 63.42$ 199,500$ 104,368$ 95,132 Community Outreach2,500$ 50.00$ 44.71$ 5.29$ 125,000$ 111,771$ 13,229 Referral and Placement6,400$ 26.00$ 27.72$ (1.72)$ 166,400$ 177,378$ (10,978) Total32,900$ 1,570,400$ 1,448,000$ 122,400 Profit Margin8.5% Summary Table Rate StructureProfit Flat Fee$ 5,929 Cost-Plus Pricing$ 138,900 Tiered Rates$ 122,400 Median$ 122,400 Median Profit Margin8.5% The charge per visit is fixed across all services and is linked to cell C45. The charge per visit is calculated using a profit margin percentage given in cell E60 and rounded to the nearest dollar. The charge per visit was determined by grouping services into tiers based on the cost per visit and calculating a profit margin (cell E60) on top of the average cost per visit for the services in each tier. Example calculations 2.RateStructuresCheck Harbor City Community Center: Rate Structures Instructions Step 1. In Section A below, review the full cost information from the Session 5 In-Class Activity. Step 2. In Section B, review the description of each rate structure in the blue boxes and the example calculation in the first rate structure (flat fee). Step 3. In Section B, enter answers in all of the yellow cells. Use formulae. Do not "hard-code" answers. Step 4.