You have just been appointed as the new Director of Planning for a 150 bed community hospital. As the hospital is just beginning their budget process for the coming year, your first assignment from the Administrator is to project patient days for the next year. This projection will drive a large part of the rest of the budgeting process by providing an indication of expected in-patient utilization. You have just recently completed a statistics class and you are anxious to put some of your new skills to work, especially regression modeling. You have acquired some data from two sources - 1) your own hospital performance for the past 18 years, and 2) hospital performance for 500 similiar hospitals for the immediate past year (see attached worksheets).
Specifically, you are to make a projection of in-patient days for the hospital for the year 2014 using regression analysis. Your response variable is Patient days and you must pick the best explanatory variable(s) and regression model you can. Present your model, an explanation of the model characteristics (e.g. Coefficient of Determination, model coefficients, residual analysis, etc.), and a rationale for the explanatory variable(s) and model you picked. Make sure you clearly state any assumptions you make. You may have to use knowledge from other classes
to help explain what drives hospital patient days.
Instructions Problem You have just been appointed as the new Director of Planning for a 150 bed community hospital. As the hospital is just beginning their budget process for the coming year, your first assignment from the Administrator is to project patient days for the next year. This projection will drive a large part of the rest of the budgeting process by providing an indication of expected in-patient utilization. You have just recently completed a statistics class and you are anxious to put some of your new skills to work, especially regression modeling. You have acquired some data from two sources - 1) your own hospital performance for the past 18 years, and 2) hospital performance for 500 similiar hospitals for the immediate past year (see attached worksheets). Specifically, you are to make a projection of in-patient days for the hospital for the year 2014 using regression analysis. Your response variable is Patient days and you must pick the best explanatory variable(s) and regression model you can. Present your model, an explanation of the model characteristics (e.g. Coefficient of Determination, model coefficients, residual analysis, etc.), and a rationale for the explanatory variable(s) and model you picked. Make sure you clearly state any assumptions you make. You may have to use knowledge from other classes to help explain what drives hospital patient days. Present all explanations and relevant supporting information in this spreadsheet and submit (rename file to [your last name] + "-projection"; e.g. Zoller-projection.xlsx) to the administrator (Dr. Z). The administrator is very gruff and wants the report on his desk first thing in the morning December 10. Projection explanation: Years data PeriodYearOutpatient VisitsPatient daysTotal SurgeriesER VisitsMedical StaffFTE EmployeesYearMedical Staff 1199697319251156303161233507371996350 21997102120252923441255713797021997379 31998124889260753441229383656361998365 4199984279277358630200303628251999362 52000429772909768733945042611672000426 62001113869295364872200724005272001400 7200281424296746244174964554222002455 82003132866299036871108434448812003444 920043585431212160047134512822004451 10200555995312423815214955156322005515 1120061638753153410734464734999352006499 12200766300315995378196625216692007521 1320081664263161182653580751511012008515 142009107951318856457290395295492009529 152010137694320695658225855307502010530 16201178433322142713160485335722011533 17201224229326332935128865363712012536 182013616043490712451125735415352013541 2014 Hospitals data HospitalTotal bedsOutpatient visitsInpatient daysTotal surgeriesER VisitsMedical StaffFTE EmployeesTotal Expenses 1133192816439876351510199941351$125,000,000 2197104073485269666173249801262$92,700,000 3176199421371555081231779721249$100,000,000 4176148773548319974470829651322$141,000,000 51653115363852310949402059631259$95,100,000 6200139158402849029395419551253$119,000,000 7165311960293127923335689401196$104,000,000 81893677966663815145352409361387$141,000,000 9160315832359866292237659291222$120,000,000 101823802714632315304443799161188$121,000,000 11128227106346896066333408861159$105,000,000 12183242310402656195519158701166$90,300,000 131991738686020912592284258691238$93,100,000 14194195833448218539242758651121$79,000,000 15169131182575768288316658441139$105,000,000 16181119462383009911364788351079$89,200,000 17178141498359797323210778341117$85,000,000 18133196078292127786296698341064$71,000,000 19156213460335686581364878301118$109,000,000 20176789114041011845297048251144$116,000,000 211801200475111812740290678111273$96,700,000 221791021884900513678351638111203$104,000,000 2318094487477979377499777991169$103,000,000 2414388942411787707292197971014$78,500,000 25162107935526995731438587931157$94,200,000 26100102529160799922152667881000$76,300,000 271812600004315111938415607771106$107,000,000 28190107183461136614205847761103$111,000,000 29196266634475179508263847711228$42,500,000 3012842157642157717234621770979$82,500,000 311637706836576766427983761992$88,900,000 321922024544783116079513517591074$80,900,000 33193188338620348470522947511171$115,000,000 341846449984782010371427787501136$114,000,000 35189298892394246465229527461080$81,100,000 3618177523579671218430608744951$71,800,000 37200160010469566489219397421005$90,900,000 38193153969311573055384797381014$74,300,000 391671195254866014016307757371111$123,000,000 40195130076479349631288887361020$107,000,000 41178251914698110339207357331434$138,000,000 42172195082376126388417817271021$94,400,000 4319022263129114329629752727964$98,500,000 4419481119428449289345827251037$69,700,000 4510365788022689568633554722867$39,300,000 46145123723357574631278867181061$83,000,000 47150266426316118265358077151101$92,000,000 4812416612725072847938763715862$70,400,000 4917813042848947873627470711943$86,200,000 5019313331146920943467039709980$84,200,000 51183177367388026581255047061070$76,000,000 5219520646541650698825669697945$62,000,000 5319916955538480807363049694965$82,700,000 541579400835395741023527692908$83,300,000 55150263875315341073446473688935$79,300,000 5617772969506461019543173683965$87,000,000 571983981747090603333933681955$94,100,000 58167257333397815457296216781030$89,500,000 591779383643479751224317677921$62,700,000 6018320183544342751031683676945$80,400,000 6117234304033671768469559674936$99,700,000 6211423566224592374215188674916$59,600,000 6316116100744234802340346671919$86,200,000 6414319774137945678329577671973$93,300,000 6513427049234818403417029671991$82,900,000 661445013839677514221488667823$79,500,000 672001023703334760122370667927$89,700,000 6813417830829973639916910666879$90,300,000 6917018370328715422319159664842$74,800,000 7018032443034943846831723661872$76,900,000 71182186749354531330831456657974$84,100,000 7213513108530945572045991655928$79,100,000 7311414039225212794327443654802$68,000,000 74134159513434131414043704653906$89,100,000 7515610116232508678228684653961$88,500,000 7615420778346382548623194652991$96,100,000 771096919533881508526273652838$61,800,000 78178109207512401369026986651901$73,000,000 791469161748441374928919651835$73,000,000 801728318146677640920140651934$78,000,000 8117419255031455678225901648898$63,600,000 8214021847523005733825715647946$69,900,000 8316381776439381488448627646941$90,000,000 84166110005434181062836197646898$72,500,000 851738976939340558832024643931$74,500,000 8615180055401651018433133642899$92,700,000 87148190242352301054628708641826$56,400,000 881688325634266732522607638863$82,200,000 8911113878126290820333743638833$62,000,000 90185108734471498111427386371000$92,800,000 9116817129131228903127037637854$63,100,000 9216613316946362462330554636866$78,800,000 9317911398343038530940610635944$78,900,000 9418215525634316922117491635862$63,600,000 9511630842724124513237877633816$68,600,000 9617513500044299547936821632863$71,600,000 971988288325906836317903632863$74,300,000 981078409319613531013351632780$58,800,000 992006001460639359020596631879$60,400,000 10019212571850217522427726630864$74,500,000 10114823286629903687110843625881$85,300,000 10211510698623420781016546621828$41,500,000 1031905463161563662426193620846$79,300,000 10410115685123237425427703615891$55,100,000 10517619727232824477836242610930$69,900,000 10614714962528418539530415610856$80,300,000 10717126592237553601330018607855$71,600,000 10816812147333348699726637604839$65,000,000 10915223929632000552248430604832$64,900,000 11018213318549779608227006603835$82,400,000 11113621991937425816627075602879$78,700,000 1121425253330606722816798600775$65,900,000 1131949639347443520836202598855$87,000,000 1141253683745591620421970597822$73,400,000 11519012301139779740240846596840$81,800,000 11614788605483249355655265951157$38,900,000 1171869974938587592320222595836$66,700,000 1181809361942177813933132593858$126,000,000 11916712651929226872019424591797$62,700,000 12017320639837533789932777588895$78,800,000 1211589735048503861023999587835$63,800,000 1221128560418660513920888587749$35,100,000 123166202969426661540726511584874$95,900,000 12410517920822608383635391583777$55,800,000 12516712335335897625934955582773$72,200,000 12615814810137560959223023581852$84,700,000 12712618728731914404327309581821$68,800,000 12817312409251733352224361580760$77,400,000 12918611762148696629636020580977$70,600,000 1301498427927735863020030579825$57,000,000 1311547370242012516134982577884$80,200,000 13212028736728246425143427577734$68,300,000 13318249220432901079625636576709$67,400,000 13418914385642145832148715575933$87,000,000 13512923196729392754419550575863$69,300,000 1361635603235219259226186572590$50,000,000 1371172429851357134968941572789$69,500,000 13813322287628998381114751570837$64,800,000 13911343976324725824922142569752$53,100,000 14013212752834615498932885561800$77,700,000 1411608529041030945934331560784$76,900,000 142185138742441231464549413558721$85,200,000 14315620112034657671756836557758$78,100,000 14418110150035011771230822556822$63,900,000 1451128948438832778522190555771$63,500,000 14610918315126535557935151555759$70,500,000 14711615310428291402030099554823$58,700,000 1481656263952086930731477553815$79,600,000 14914842977290976873394505531167$46,400,000 15015113769432069565822585552750$64,100,000 15115581677389551154531156551936$98,900,000 15212410798725594685617921551773$50,400,000 15316010190037206629430530549743$61,600,000 15418617668337056773717956548766$53,600,000 155165111575349511309429735548813$86,100,000 15617615834034751460926956542747$54,600,000 1571587237239449757225755541764$85,700,000 15813614847423164711918197541700$40,600,000 1591159109819264516432188539687$71,800,000 16014319098931869598214701535677$44,300,000 16114316403924264600024195534756$57,500,000 1621358182140745286235965533747$56,100,000 16314410891048564521119338530806$75,400,000 16411817208321306919728375530737$64,300,000 16514613298937423547647735528744$66,600,000 16612416572924939482129885528741$54,900,000 1671509731925115630316123525737$58,700,000 1681446508024128419228385525747$42,700,000 16912518122631889624832639521682$67,800,000 17014710222626248293320889521679$43,200,000 17117111619734753381125807520728$73,200,000 17215819154928055448817978520683$44,500,000 17310716288427400703235643520853$74,400,000 17415312474339049386522645519718$56,000,000 1751629336037805882832374517729$59,900,000 17618299253374931124632308517794$81,400,000 17712311030026300378128428514724$52,500,000 1781486711119890471921818514707$37,900,000 17912528071723116428317464513640$36,900,000 1801437157324606284225475512716$49,800,000 18116711606423757409320031510805$73,800,000 18219710904951369811837808509757$114,000,000 18316314747741574756430017509803$39,300,000 18417523032437345849418556509612$46,400,000 1851952568640169752625686508836$69,000,000 18614814897235685706322655508701$55,000,000 187140323462369609354215525071238$95,000,000 1881205883332042570530578507736$55,000,000 18916870168306291013319073507677$59,600,000 19015012488926075344122938507636$50,600,000 1911901962184142569917128506679$45,400,000 19214810212025292344125571503702$51,200,000 1931417290525638594517540502780$66,200,000 19414619928236793851126488501662$65,400,000 1951587608736117643721305501700$60,700,000 1961998892340778402418031500684$41,300,000 19710710441620262593429926500705$54,900,000 1981277936527232242931093498692$71,000,000 1991579273833452439618675496657$59,500,000 20012711590632225521019225496646$55,100,000 20115419484527639264416501495671$51,700,000 20211120028424502463620638495641$31,100,000 2031428683729653471417608494688$62,800,000 2041233865636943283022549493621$43,000,000 2051415422336720769549331492841$65,800,000 2061708308133891468910803492694$54,000,000 2071334599233257727634768492606$59,400,000 20811135300022246479625440492747$56,000,000 20915214883936456675130180491692$78,000,000 21015319715136171774030172491679$80,900,000 21111116253026599857027916491719$60,500,000 21213214021930649735926781490800$88,000,000 21314517124138369481116325488650$59,200,000 2141085787430529670525001488663$54,300,000 21511416532818676490915628487649$63,200,000 2161667170747273429427055485785$71,400,000 21714113173224955426434541483638$63,200,000 2181056496618787349622520482650$47,700,000 2191349168621484799926065480648$49,300,000 2201254594525351606311021479768$52,400,000 22112819952032089267518835476601$53,000,000 22211911115825055486015026475644$55,000,000 22315113362338542813126169474705$48,000,000 2241257488326240523527364474599$69,300,000 225145445442389049409032474659$71,400,000 22612618527533436618321896473668$44,500,000 22712945695320831027629279473633$63,700,000 22812716604024450507122258473644$63,900,000 22917214591745147397829838472641$72,300,000 23017051877438721263739997472796$80,600,000 2311754626741683483918798470670$53,300,000 2321246778131248445522717470644$48,800,000 23311215742629747793031922470641$53,400,000 2341284088340076279520793469648$56,400,000 235196139225337651102133514469712$58,800,000 23614412595832092459625541468626$51,300,000 23710511757124788600416627468682$49,000,000 23817512035636501434430348467734$59,800,000 23911121211634961678821284467838$98,200,000 24013216801124347623320495466667$59,600,000 2411967086040368423327281463672$65,300,000 2421585093228604719913977462763$72,300,000 2431307924425317311014481460590$41,100,000 2441664902632000388127818459747$41,900,000 2451763251541516574118947457666$70,100,000 24610420499820469516335598457604$47,900,000 24714499061311521843423915456685$81,700,000 24815610681725632500725829453612$42,500,000 24913825271134011864444841452625$73,300,000 2501506630031599537819662452669$48,300,000 2511396428734499726725314451633$69,400,000 25210414092522134468725474450668$65,500,000 25315611694238829783323713449626$62,300,000 25411821565122953274216914449606$32,300,000 2551143396819944443627401448586$44,300,000 25612616957927504326022380447596$44,800,000 25714714046219223290523658447618$36,200,000 2581258216238241345612096445556$42,300,000 2591398103722494801124817444687$53,000,000 2601329281519230333027269443581$40,400,000 2611889055547723318319799442617$57,300,000 26214613859130301651823634440646$75,500,000 263152736272823683970440607$52,800,000 2641603119443966023948439645$53,600,000 26516513644436596571924225439647$72,400,000 266137154312352281235150228439685$77,500,000 2671534970231352620120563439623$46,500,000 2681238430421293860626664438653$51,200,000 26910912749017875423217440438583$45,500,000 27016412176626183679224691437608$81,600,000 27112019729824470577943373434660$54,200,000 2721325710925224705430295429543$46,500,000 27314710067137415552221991428610$44,800,000 2741561163803004350559801426618$61,600,000 27510010268921288360318816425607$38,800,000 27615312188830720647716375423637$50,900,000 27715714306224294343418835421586$48,800,000 27813210177323074476124839420604$43,300,000 2791059204625328499714982418539$58,000,000 28014810795131885645729039417549$60,900,000 2811598171832569532426504416562$45,200,000 2821106198932429341629534416559$47,700,000 2831559904924730370622442416568$51,400,000 2841419813137164584630688415645$65,600,000 28512012234031033788027980414563$56,500,000 2861245753126167616724032412607$73,900,000 28714217183235217324420112411559$45,500,000 28813680053256481140014732411569$56,200,000 2891199607924960570120911411554$45,300,000 29014130710403731223618384410626$86,600,000 2911358583117764371817750409585$52,300,000 2921117200522286338941276408560$67,100,000 2931029515518819365322619408592$37,300,000 2941175521230034377025486407561$41,900,000 29520012230758876505317778405606$45,600,000 2961966680457054533628352405659$60,400,000 29719614872833114472613281403540$36,300,000 298120443602198738478995402557$61,500,000 29913511673518763775418238402559$46,700,000 3001306152730112981850175400550$51,500,000 30111712316020504295625195400569$35,700,000 3021768679429687280812539399562$48,900,000 3031147560016089196219274398522$28,700,000 3041278266828505926337915397571$73,600,000 3051524084041352302915960395538$39,500,000 3061017907123717328018222395563$42,300,000 30714313969935104406026991393592$43,900,000 3081745250847820631726478391797$107,000,000 3091609638743563474042536391592$53,800,000 31018712427445615594621017390690$73,700,000 3111197482822416441025212390561$45,800,000 3121158500027578374221221389558$49,400,000 3131385824233585599218887388563$55,600,000 31413710630923145778316652387526$37,800,000 31520010675344116620826388383766$66,800,000 3161056038825644603217687383631$48,700,000 3171124624319868331827971383528$33,000,000 31813013040927912690417972382541$37,500,000 3191257105527307603224700382506$45,100,000 32015111386929536487220072381527$54,100,000 3211567468925429376428051379534$45,800,000 3221397560626904534323491378587$53,300,000 32310018929520633566118335373608$50,000,000 3241156037419244399421826372499$49,500,000 32514911801139638558715959371517$40,100,000 32614617294929647690423707370555$47,300,000 3271804386927569420615993369490$34,200,000 32810814646423161358019070369553$45,500,000 32910214083620197387019608368505$43,300,000 33016110417541992482913373366496$59,500,000 33117116308937008442021470366486$39,800,000 33214711837929844525621591365591$60,600,000 33312014487725346302511387365530$36,800,000 3341685393049468638119441364531$46,600,000 3351448195738209389126537363512$95,300,000 3361157549418686318319659363536$37,900,000 3371358612422797827130137362606$45,000,000 33814599065376521839028016361602$63,100,000 33910210763431653274717598361518$51,400,000 34017215994528287482534760358563$47,500,000 3411489670444440490918393357506$48,000,000 34215511853841602468812438356473$30,900,000 3431065209719362550117327355473$36,900,000 3441113222316648477315464355510$36,200,000 3451251364472943039328930352517$38,300,000 3461675972638259429224503351587$68,100,000 3471517843332214271316048351572$39,800,000 3481505540941927190012369350463$24,500,000 34917112983829876647518824347527$41,300,000 3501727276439538551623751346485$45,800,000 35111317704916154465121264346483$41,000,000 3521738905439411314430585344549$32,800,000 3531108271429459316117592344495$40,700,000 35415311831236604442121608343510$57,000,000 3551159312824585248412818343491$33,500,000 3561197935322121654518040343504$38,700,000 3571409443931573372623316341504$55,400,000 3581416322223371687718473341486$32,300,000 3591038388117040302818014341493$31,800,000 3601136760517181540028669340472$37,700,000 3611786267052030142013013339449$25,900,000 3621733033447791684921645339603$48,700,000 3631322575420924243118145339456$40,100,000 364125382513181921293420020339427$49,600,000 36517010700739906334217551337481$46,700,000 3661339083723560651724811337569$56,800,000 3671523182825819597119030335463$41,700,000 3681045817218997391911684335443$29,400,000 3691115813220778372923095333514$47,800,000 3701138138816491531235998333471$46,900,000 3711345093536511484718367332504$37,600,000 3721355976625755225216017332433$25,100,000 3731195604319680174920189332434$32,400,000 3741274819917306262915495331460$32,300,000 37513818986235487281812881329480$31,000,000 3761283777927860291622648329477$37,600,000 3771362184134711603017429327470$36,700,000 3781345997033318505315976327414$36,700,000 3791298409030455210118731326446$31,900,000 380128226932466025180326446$57,900,000 38116515012047726347213276324519$41,800,000 3821733850533756527613669324513$49,200,000 3831251724127759146817021323443$30,100,000 38410310088419060410817905323486$42,400,000 38512717749819754330411961321433$36,200,000 3861047666618702311917055318451$32,700,000 3871386867127430847325652317445$44,900,000 3881308722421895623222463317485$37,000,000 38919916351033079472427141316592$49,900,000 3901474076634681653923427315460$50,200,000 3911152897726173396826221314447$29,700,000 3921034477619745330015718314445$38,600,000 393132112969373781164121425313438$48,000,000 39410510022120149404714551313484$34,300,000 3951006757518996267019877313421$26,900,000 3961384704121844397419011312450$34,400,000 3971009905218481429915352310457$33,200,000 39811811443821691679819620309419$43,600,000 3991165881227037210717479305409$37,700,000 400184635689184189712890305417$24,800,000 4011161466062235942208879304452$45,000,000 402122355072288939457200302420$32,700,000 4031973851750455306713022301402$30,700,000 404100478392536631969700301416$41,800,000 4051484188623410230300424$16,600,000 4061654187425891280827992298416$37,900,000 4071036143125825509929520298514$54,300,000 4081247040420019272523077297418$37,900,000 4091416276821080469313722295453$35,600,000 4101374525928992301112061292399$31,900,000 4111295556025510257250524292475$30,800,000 4121067762814635285921819292462$33,300,000 4131201952419586168513005289373$22,600,000 41410110324123888371611517288404$23,100,000 415115946747768381415716288416$28,400,000 4161207539520161262218303286363$32,000,000 4171266499325272757616092285419$42,000,000 41816645307474974609010284360$19,900,000 4191422749919310237813345284384$33,200,000 4201219569919196194528541282367$63,100,000 4211273961518957240315097282399$32,800,000 422125264991082431015373282396$24,300,000 4231263797528927349127870280477$43,400,000 4241698429432938233116342278397$34,400,000 4251067916326488540742553278437$53,200,000 4261502422932633293512886277371$20,700,000 4271165071422942249616592277403$39,600,000 4281006314619206558833493276411$43,000,000 429155230952975556039319274382$43,400,000 4301414969325751697628118274486$49,000,000 4311085208620664543921583274407$40,900,000 4321084713020049209816442273380$25,700,000 4331324314411223297120161272389$35,700,000 4341285898526088186024437271381$31,100,000 4351065152021324327116343271401$27,900,000 4361094802417102428016506270411$28,700,000 4371968249342977779976745268681$211,000,000 4381968249342977779976745268681$65,300,000 4391023701524824215516420266380$45,700,000 4401305661124970166715080265364$18,800,000 4411049889718355210112796263366$29,500,000 4421855372814993404512329261354$34,800,000 4431333819217915414414741260369$27,000,000 4441254673018094272212490259407$24,200,000 4451059290221502382416260258363$25,000,000 44613411831520528574223991258363$40,800,000 4471337628126639208711523257401$28,800,000 4481152937223165203113372257341$18,900,000 449137232621538010547262256356$24,800,000 4501163127910605352814531256352$25,100,000 4511048033130044351934935255356$24,300,000 452115300542214829278337254390$25,800,000 4531954966745050208011577253375$24,200,000 454119613623030126767291251385$29,900,000 4551168200924470455621120251398$42,700,000 4561266174116187406019891249379$27,300,000 4571214102033188146311204248310$24,700,000 4581047181817787367612733248357$25,000,000 4591783714913747314216334248393$23,700,000 46014020458378206124030247328$8,708,600 4611347011230348144614840247352$21,900,000 4621184429428727245612429245336$30,100,000 463105384027642165312846243358$20,200,000 4641184148218852307116151242366$37,000,000 4651438391734334385614451241354$46,800,000 46612731007021246311214637239396$33,500,000 46711625340187491440713486238361$48,600,000 4681246042617149233522737238378$28,900,000 4691289321422178323018901237323$40,700,000 470101316301348029647950237350$30,400,000 4711593956344646165511459234306$32,400,000 4721054258913842307216732232325$28,500,000 4731084973618332159726835231311$29,800,000 47412811186372315601983230288$7,450,809 475153307752198638944697229334$23,800,000 4761445416213120126712421229338$24,100,000 4771006357823733176114651228348$28,200,000 4781533576232102325220136227307$44,100,000 4791154953417683230413722225321$20,900,000 48010726573296104124622224280$13,900,000 4811175878426652358029630223337$33,400,000 4821321225323136191510762223309$22,400,000 483174266544696715085405222306$16,400,000 4841526205121326138010704222319$20,400,000 4851178954612639468115287221376$26,800,000 4861035180023065171319237220312$21,100,000 48715422982475005462656219280$15,600,000 488158135644027811505565216318$20,500,000 4891153244628518168110588210278$16,400,000 4901073332114512310815689206320$32,600,000 4911144343516156324922553205304$24,300,000 492109287261955418766798204264$16,900,000 4931003763229789279716609203267$20,100,000 4941007588719885237216990202310$22,100,000 4951234848114763216813506193269$20,300,000 4961003135713518189212589192313$21,700,000 49712914843386417204348191263$13,200,000 4981053507018363271315090186257$23,200,000 4991155851029102148015814185272$22,100,000 5001035435913588451828671184304$26,500,000