One-variable data table

Each procedure performed by a 2020 Vision Care ophthalmologist has associated fees and costs. The company monitors the data to help negotiate its reimbursement rates and to clarify its costs to patients. In this data table, the company wants to analyze proceeds if the physicians charge is varied for a common procedure related to retinal detachment surgery.

Cryopexy

• In cell C7, create a formula to subtract the copay and payout amounts from the fee. In cell C11, use a formula to calculate total costs. In cell C13, calculate the proceeds by subtracting costs from revenues.
• Build a one-variable data table by following these steps:

1. In cell B18, key 600. Key 650 in cell B19. Fill down to 1000 in cell B26.
2. In cell C17, key a reference to the total proceeds cell. In cell D17, key a reference to the total costs formula.
3. Select cells B17:D26 and create a one-variable data table where the column input is the charge from the physician.

• Create conditional formatting for B18:B26 that indicates if the cell value is equal to the charge from the physician. Also create conditional formatting for C18:C26 that indicates if the cell is equal to the total proceeds.
• Label your columns. Add formatting to the table of your choice, and make sure that all your values are formatted correctly.
• In cell C8, change the fee to $750.

Two-variable data table

Purchases by patients of some products in a 2020 Vision Care office are subject to sales tax. Although taxes are computed by their billing system, the offices would like to have a reference table for quick answers to patient questions. Each state has a different tax rate, so you can build a two-variable table for the $100$125 range.

Taxes

• In cell C7, create a formula to determine a sample tax amount in dollars. (This should just be the tax amount, not the tax and total amount.)
• In cell B10, create a reference to this formula. Make the font in B10 white so that the formula or value do not show in the cell.
• Fill the amount range in column B so that it reaches $125.
• Create a two-variable data table in B10:F36, where your row input references the tax rate, and your column input references the amount spent.

• Create conditional formatting for B11:B36 that indicates if the cell value is equal to the amount spent in your formula reference.
• Create conditional formatting for cells C11:F36 to indicate if the value is the same as the tax added in your reference formula.
• Label your columns. Add formatting to the table of your choice, and make sure that all your values are formatted correctly.
• Change the amount in cell C5 to $113 and the tax rate to 6.25%.

Discussion-In a few sentences, discuss what you have evaluated in each of the data tables

2020 Vision Care Cryopexy Procedure Analysis Cryopexy is a procedure used during retinal detachment surgery to freeze the area around the repaired hole/tear. Negotiated network fee Average patient co-pay Average insurance payout 4500 100 3150 Net revenue 850 Physician charge Facility charge Administrative charge 250 50 Total costs Total proceeds 2020 Vision Care State Sales Tax Table Sample Calculation Amount Spent X State Tax Rate Tax added $100.00 4% Amount Boston Dallas Seattle Chicago 5% 6.25% 6.25% 6% $100.00 $101.00 $102.00 $103.00 $104.00 $105.00 2020 Vision Care Cryopexy Procedure Analysis Cryopexy is a procedure used during retinal detachment surgery to freeze the area around the repaired hole/tear. Negotiated network fee Average patient co-pay Average insurance payout 4500 100 3150 Net revenue 850 Physician charge Facility charge Administrative charge 250 50 Total costs Total proceeds 2020 Vision Care State Sales Tax Table Sample Calculation Amount Spent X State Tax Rate Tax added $100.00 4% Amount Boston Dallas Seattle Chicago 5% 6.25% 6.25% 6% $100.00 $101.00 $102.00 $103.00 $104.00 $105.00