Provide a step-by-step Excel documentation for the following question:

A research group is tracking the inpatient unit throughput across the United States in the last ten years. The collected data includes the number of participating hospitals in each state, the total number of beds, total number of discharges (cases treated), total patient days, and revenue. Patient Days means the total number of patients occupying beds in a long-term care facility for all days in the calendar period for which an assessment is being reported and paid. The average charge per case is the total revenue divided by the number of cases treated. The data is shown below:

1. What are the independent and dependent variables? (10 points)

2. Prepare a scatter chart examining average charge per case (y) and the number of cases treated (x). What is the relationship between average charge per case and the number of cases treated? (15 points)

3. Find the correlation between Hospitals, Beds, Discharges, and Patient Days, and show the results in a table (10 points).

4. Develop linear regression models to identify the relationship between average charge per case and Hospitals, Beds, Discharges, and Patient Days (separately). In each case, provide the regression equation and R-square value. Comment on the accuracy of your regression models. Please note that you are not asked to do a multiple regression here but only separate linear models (20 points).

5. Predict the revenue when the number of hospitals is 200, beds 1000, discharges 10,000, and patient days are 5000 (separately) using the linear regression models developed in the previous part (20 points).

6. Develop a multiple regression model to determine the relationship between the total revenue and all other factors. Then, predict the revenue when hospitals are 100, beds 500, discharges 5,000, and patient days 2,500 (15 points).

State	Hospitals	Beds	Discharges (Cases Treated)	Patient Days	Revenue ($000)
AK - Alaska	10	1,231	45,314	240,048	$4,968,296
AL - Alabama	92	15,736	584,412	2,845,013	$56,763,433
AR - Arkansas	52	8,018	320,061	1,398,070	$25,140,741
AZ - Arizona	69	13,677	606,086	2,636,380	$72,549,051
CA - California	342	74,678	3,116,644	14,287,334	$399,507,696
CO - Colorado	54	8,493	399,265	1,751,981	$61,489,836
CT - Connecticut	34	9,161	368,797	1,703,391	$36,346,754
DC - Washington D.C.	7	2,151	93,097	559,726	$11,731,612
DE - Delaware	8	2,042	92,991	460,579	$7,242,217
FL - Florida	215	59,933	2,493,411	11,916,304	$301,915,431
GA - Georgia	113	22,118	937,009	4,576,146	$95,612,086
HI - Hawaii	14	2,516	91,066	530,206	$7,209,765
IA - Iowa	39	6,319	267,686	1,175,372	$22,940,725
ID - Idaho	17	2,431	117,937	471,297	$10,970,369
IL - Illinois	143	30,136	1,244,144	5,597,339	$140,252,715
IN - Indiana	98	16,126	681,429	3,105,090	$74,943,977
KS - Kansas	57	6,434	273,441	1,163,942	$30,634,743
KY - Kentucky	75	13,965	539,225	2,579,067	$53,932,073
LA - Louisiana	105	14,478	511,396	2,418,303	$52,788,610
MA - Massachusetts	73	15,089	737,656	3,521,677	$62,177,625
MD - Maryland	52	10,963	573,212	2,821,418	$18,638,627
ME - Maine	18	2,944	112,744	523,871	$10,100,054
MI - Michigan	105	23,677	1,093,978	4,849,751	$88,936,148
MN - Minnesota	55	10,706	499,781	2,175,057	$41,564,049
MO - Missouri	84	16,883	680,602	3,213,182	$68,501,467
MS - Mississippi	70	10,536	324,469	1,485,521	$33,516,746
MT - Montana	16	2,180	80,148	342,133	$6,919,723
NC - North Carolina	107	22,099	967,524	4,674,534	$93,439,730
ND - North Dakota	10	2,018	76,706	349,678	$7,823,373
NE - Nebraska	27	4,199	176,615	806,288	$13,328,480
NH - New Hampshire	14	2,219	105,034	496,476	$12,032,157
NJ - New Jersey	74	20,250	892,079	4,284,602	$117,357,458
NM - New Mexico	38	4,044	166,796	759,357	$16,811,603
NV - Nevada	29	5,629	278,250	1,387,656	$39,573,038
NY - New York	190	56,414	2,180,091	11,693,214	$223,034,099
OH - Ohio	146	27,834	1,239,809	5,525,860	$138,046,436
OK - Oklahoma	91	10,598	398,634	1,844,474	$43,725,519
OR - Oregon	37	6,181	323,503	1,399,152	$25,400,406
PA - Pennsylvania	180	36,095	1,474,191	6,892,323	$197,932,001
RI - Rhode Island	11	2,438	107,009	496,175	$9,413,919
SC - South Carolina	63	11,831	475,029	2,324,540	$54,555,000
SD - South Dakota	24	2,767	96,055	389,263	$10,455,712
TN - Tennessee	104	18,681	781,952	3,678,209	$80,860,531
TX - Texas	362	58,605	2,621,039	12,022,797	$293,571,442
UT - Utah	36	4,600	206,433	821,528	$17,767,528
VA - Virginia	90	17,848	722,695	3,460,192	$74,434,302
VT - Vermont	7	828	37,398	186,431	$4,207,068
WA - Washington	64	10,273	536,394	2,494,704	$60,077,168
WI - Wisconsin	76	11,213	493,349	2,172,778	$51,789,599
WV - West Virginia	37	6,057	213,157	1,030,471	$16,990,641
WY - Wyoming	14	1,212	32,288	114,869	$2,685,722