If you could check the entries in the spreadsheet are correct or not, it would be great. I have attached the case, blank spreadsheet with inbuit formulas and my solution spreadsheet. Thanks!

CASE 9 8/13/13 Student Version CAMBRIDGE TRANSPLANT CENTER Marginal Cost Pricing Analysis This case analyzes a relatively straightforward marginal cost pricing analysis. It is designed to illustrate the mechanics of estimating marginal costs as well as the advantages and disadvantages of using marginal costs as the basis for pricing transplant services. The input section of the worksheet contains historical average cost data for seven cost categories along with the fixed cost proportion and growth percentage for applicable categories. The output is the marginal (variable) cost for Phase 4 of the transplant program as well as the Phase 4 cost structure. The model consists of a complete base case analysis--no changes need to be made to the existing MODEL-GENERATED DATA section. However, all values in the student version INPUT DATA section have been replaced with zeros (or dashes). Thus, students must determine the appropriate input values and enter them into the model. These cells are colored red. When this is done, any error cells will be corrected and the base case solution will appear. Students will have to modify the model as required by the case as well create their own graphics (charts) as needed to present their results. INPUT DATA: KEY OUTPUT: Phase 4 Hospital Costs: Contract volume Marginal variable cost (variable cost rate) Marginal fixed cost Total contract marginal costs Average cost per contract transplant Average Cost Category Nursing Ancillary OR Laboratory Radiology Drug Other $ Total $ New contract (marginal) volume Current proportion of fixed costs for categories that have both fixed and variable components - Note: The model assumes that radiology, drug, and other costs are completely variable, while the remaining cost categories consist of both fixed and variable costs. 0 0.0% Note: Additional fixed costs are required when contract volume exceeds 30 transplants. Current fixed costs total $5,800,440. New (marginal) fixed costs required as a proportion of current total fixed costs when marginal volume exceeds 30 Note: Additional fixed costs are required when contract volume exceeds 30 transplants. Current fixed costs total $5,800,440. 0.0% MODEL-GENERATED DATA: Phase 4 Fixed and Marginal Costs (Per Contract Transplant): Marginal Cost Existing Fixed Costs Category Nursing Ancillary OR Laboratory Radiology Drug Other Total Volume of 30 or less Volume of 31 or more $ - $ - $ - $ - $ - Total cost See calculation below Phase 4 Hospital Cost Structure: Total Marginal Costs Associated with New Contract Volume: Total costs $ - = Marginal (new) fixed costs + = Marginal (new) fixed costs + = Average marginal cost per transplant = $0 + #DIV/0! Variable costs (Variable cost rate $ - x Marginal volume). x Copyright 2014 Health Administration Press must determine ble cost (variable cost rate) marginal costs per contract transplant nd other costs categories equired when contract . Current fixed costs $ $ $ #DIV/0! equired when contract . Current fixed costs ee calculation x Marginal volume). 0 END