Complete the following problem.Set-up an excel Excel spreadsheet using the format and data from the table under PROBLEM 1, which is attached. With the knowledge that there are 4,500 visits projected for Fiscal Year 20xx, the CPT Code utilized for each visit is 78306 - Whole Body Bone Scan, and that it is billed at $250.00 per visit:

1.1)For each Payor, on a per visit basis, calculate to the nearest dollar amount:

a)Full Rate

b)Contracted Rate (Contractual Rate) (note that you are given the Discount on the Full Rate).

c)Contractual Allowance, based on the Contracted Rate and Full Rate.

1.2)Calculate the number of visits projected for Fiscal Year 20xx per payor, based on the percent of total visits projected, as provided in the above table in column labeled "% Total Visits".

1.3)Based on your previous calculations and the information given, calculate for each payor and the total for all payors the following for Fiscal Year 20xx:(please round to the nearest dollar)

a) Total, if all payors paid at the Full Rate (Billed Rate),

b) Contract Rate, and

c) Contractual Allowance.

1.4)Determine the Payor Mix percentages for each Payor, based on projected Contractual Rate revenues.(Round to the 100ths of a percent)

1.5)Calculate the Average per Visit dollar amount (to the nearest cent) for the Full Rate, Contracted Rate and the Contractual Allowance. (Be sure to calculate this based on the weighting of the number of visits projected for each payor.)