1. Save this file and include your last name in the filename added on at the very end of the existing filename.
2. Make sure you are working in the new file you saved with your last name included in the filename. Click on the first tab called impact_puf19.
• Search on this tab for the hospital “Name” called WHEATON FRANCISCAN HEALTHCARE- ALL SAINTS
a. What row # is this facility listed in? ___________
b. What is their Provider Number? ___________
c. What is their FY 2019 Wage Index? ___________
d. What is their Operating CCR? ___________
e. What is their Capital CCR? ___________
f. What is their FY 2019 GAF? ___________
3. Click on the second tab called CLFS 2019
• Within Row 1, find the column for North Carolina (i.e., search for “NC”)
a. Which column is NC located in along with their associated fee schedule rates? ___________
b. Hide every column except the one that contains the rates for North Carolina.
c. What is the rate for HCPCS code 88245 for NC? $ ___________
d. In cell BH4, create a formula that adds the rates for HCPCS codes 80169 + 81213 + 87521. What is the rate for these three HCPCS codes added together? $ ___________
1. Write your formula here showing in cell BH4: ______________________________
e. In cell BH5, type the decimal equivalent of 125%. What is this value? ___________
f. In cell BH6, multiply cells BH4 and BH5 together. What is this value, with all of the numbers behind the decimal included? $ ___________
1. Write your formula here showing in cell BH6: ______________________________
g. Change the background color of cell BH6 to yellow.
h. Create a formula in cell BH7 that will result in 75% of the value in cell BH6. What is this
result if you rounded it to the nearest 100th decimal place? $ ___________
1. Write your formula here showing in cell BH7: ______________________________
i. At the end of Column AP add up the Total of all rates added together. Format the result with dollar sign. What is the total?________________
4. Click on the third tab called MPPR 2019 for the following questions.
a. Sort the data by Code using the sort order Smallest to Largest. What code and fee amount appear first on the list? Code: _______________
Fee Amount: $________
b. Now sort the data by Short Descriptor using the order A to Z. What code and fee amount appear first on the list? Code: _______________
Fee Amount: $________
c. Highlight the list of codes. How many codes are in this list? ___________
d. Apply a Filter to the column labeled “Short Descriptor” and filter for the word therapy in the Search box and click OK.
e. How many codes are present when you filter for “Short Descriptor” of therapy? ___________
f. Using the Find & Replace feature replace all instances of the word therapy with the word PMMC. How many replacements were made? ___________
g. Now in column D multiple the “Fee Amount” by a markup percentage of 200% for every code in the filtered list. Once completed, total up the result of all marked up rates in column C and write the result here? __________________
