MISY 5330 Assignment: Inter-State Healthcare Profit Analysis Objective: Develop a Decision Support System (DSS) in Excel to analyze healthcare profits for five states using the dataset provided below. Your spreadsheet must calculate total revenue, deductions, expenses, and net income for each state. Dataset (Table II) Texas California New York Florida Ohio # of hospitals 430 390 220 210 180 Beds per 1,000 pop 2.7 2.3 3.4 3.1 2.9 Cost per bed $88 $92 $78 $75 $70 Inpatient days/1,000 620 550 980 715 570 Exp. per inpatient day $1,520 $1,800 $1,430 $1,420 $425 Outpatient visits/1k 1,600 1,530 2,470 1,370 980 Exp. per outpt visit $118 $130 $115 $112 $105 ER visits/1,000 385 405 400 295 360 Exp. per ER visit $2,050 $3,100 $2,000 $1,650 $550 Formulas (Table I): Use the following formulas in your spreadsheet model: Revenues: Inpatient Revenue = Inpatient Days Expense per Day Emergency Revenue = ER Visits Expense per Visit Outpatient Revenue = Outpatient Visits Expense per Visit Bed Revenue = # of Beds Cost per Bed # of Hospitals Total Revenue = Sum of all above revenues Deductions = 5% of Total Revenue Net