Now that youve set things up, you can start completing the worksheets (listed above) with the relevant calculations. Use the following information to draft your sheets. These items represent the starting information. You will be responsible for completing the rest of the calculations.

Worksheet #9: DaVinci Operating Data to begin the calculations for Phase I, create this worksheet using the data and format shown in my sample sheet for Worksheet 9. The numbers in this worksheet drive most of the calculations for the first phase. SS means single surgery; DS means dual surgery (you dont have to abbreviate).

Worksheet #10: DaVinci Salary Expense For each daVinci surgical case type, you must perform basic workload calculations using the following personnel/hours (this is just for labor expense; supply and other expenses will be calculated separately). There should be SS calculations, DS calculations, monthly and annual totals. Hourly rates and required FTEs are: (1 FTE) MD/Surgeon @ $475/hour (1 FTE) Anesthesiologist @ $395/hour (1 FTE) RN @ $85/hour (2 FTEs) Surgical Techs @$60/hour Fringe benefits percentage: 33% *** Assume 3 hours for each single surgery (SS)! *** Assume 4 hours for each dual surgery (DS)! Note: Dual surgery requires two surgical teams! You do not have to double the DS volume.

Worksheet #11: DaVinci Revenue revenue for DaVinci is per case/surgery and reimbursement is as follows: Medicare Reimbursement: $3,600 (30% of cases) Medicaid Reimbursement: $1,850 (15% of cases) Commercial Insurance Reimbursement: $8,925 (55% of cases) Note: These amounts may change later (in part or in entirety, so be sure to use formulas for your calculations. There should be SS calculations, DS calculations, monthly and annual totals.

Worksheet #12: DaVinci Supply Expense this worksheet should contain supply costs per case and as well as a calculation for the allocation of the annual maintenance costs. Supply costs per DaVinci case: $3,000 for each SS case, $3,500 for each DS case. Annual maintenance costs: $300,000 (must be allocated based on the percentage contribution of case type to total cases). There should be SS calculations, DS calculations, monthly and annual totals.

Worksheet #13: DaVinci Break-Even Analysis this must include all costs associated with the implementation of the surgical system (purchase of the robot is a capital expense captured in the capital budget and financial statements and does not appear in the standard operating budget). Initial costs, all expenses, all revenues, etc. ***How many cases must be performed to break-even? There should be SS calculations, DS calculations, monthly and annual totals.

Worksheet #14: DaVinci Supply Vendor Analysis we know what supplies well need for both DaVinci and the rest of our facility. We now need to secure/contract a vendor to provide our supplies. (See separate documents from Dr. H.)

Vendor analysis in any organization, is a part of the supply chain management function. The supply chain includes all parties involved in meeting the customers needs (can be both internal and/or external customers). The goal of the supply chain (as with most other healthcare initiatives) is to meet the customer needs/organizational specifications at the least possible costmaximization of value. The supply chain often impacts organizational structure as well as longer-term management decisions. Most organizations seek at least three proposals for comparison, to get a sound representation of the markets offerings.

For the purposes of our Excel project, we must secure a suitable vendor for our DaVinci surgical supply needs. There are surely costs involved but youll calculate a series of metrics to help you determine which vendor will work best for our surgical service (attached spreadsheet). There are three vendors bidding on our business, and this analysis represents the additional administrative costs associated with securing supplies. Weve already calculated our supply costs on worksheet #12 of phase I. These costs are in addition to that, and will be shown on worksheet 14. These costs will rollup into the consolidated expense worksheet #8, and will fluctuate with the percentages as the other items do.

The calculations required for the comparison of our vendors are shown below. These calculations must be shown as formulas in worksheet #15 (bottom section). The primary data (top section) can be typed in. Remember your formatting! The model of worksheet 15 including the categories is located in Blackboard.

Calculations for Comparison: 1) Economic Ordering Quantity (EOQ) = [square root of (2 x fixed cost per order x annual usage)]/ (carrying cost x purchase price) 2) Total Carrying Costs = (order quantity/2) x carrying costs x purchase price 3) Total Ordering Costs = (annual usage/ordering quantity) x fixed cost per order 4) Total Inventory Costs = total carrying costs + total ordering costs. 5) Reorder Point = (annual usage/days per year) x delivery time

The ideal vendor will be used in your workbook calculations and will meet the following requirements: 1) Largest safety stock (provides the ability to remain operational/productive during unforeseen emergencies), 2) Total carrying costs of less than $15,000, 3) Total ordering costs of less than $10,000, 4) Total inventory costs of less than $25,000, and 5) Highest reorder point (allows for longer periods of time between orders).