I am having difficulty finding the formula to work this excel problems 20-55. I am seeking an example only of this assignment.

Name First Name Last name Question: The profile of your patients is such that the average collection rate is 75%. Assuming you have 100 visits of each type each month, what amount of new revenue will you generate in the next 12 months? Your clinic provides four kinds of services: Comprehensive initial medical consultation is priced at $250 Established patient limited visit is priced at $75 Established patient intermediate visit is priced at $125 Established patient comprehensive visit is priced at $250 Net RevenueScenario Type of Service Comprehensive initial medical consultant Established patient limited visit Established patient intermediate visit Established patient comprehensive visit Total Gross Revenue Average Collection Rate Total Net Revenue Price Each Annual Volume Gross Revenue per year Name: Assignment: Fixed/Variable Cost Scenario You have performed a cost analysis of your health service organization and have determined the following: based on latest three years of information, your annual cost of operations is $1,600,000 with annual volume of 10,000 procedu You have determined that certain of your supply items are fixed in nature (those marked with an F) while others are (marked with a V). Supply Items F/V Supply item 1 Supply item 2 Supply item 3 Supply item 4 Supply item 5 Supply item 6 Supply item 7 Supply item 8 Supply item 9 F F F F F F V V V $2 1 5 3 2 Annual Cost of Operations 1,6 Question: An insurance company that is considering directing its 1,000 units per year of procedure business to your organization has approached you. Your board has mandated that you make $5 of profit from each of the procedures obviously want the highest possible price, but as you enter the negotiations, what is the lowest possible price you wo willing to accept from this payer? Hint: Calculate the variable cost. Fixed/Variable Cost Scenario Supply item 1 Supply item 2 Supply item 3 Supply item 4 Supply item 5 Supply item 6 Supply item 7 Supply item 8 Supply item 9 Total Cost Annual Volume Variable $500,000 $300,000 $200,000 $1,000,000 $10,000 Fixed $220,000 $180,000 $75,000 $50,000 $25,000 $50,000 $600,000 Variable Cost per Unit Profit Target Total (lowest possible price) 100 $5 $105 d have determined the following: based on the 000 with annual volume of 10,000 procedures. hose marked with an F) while others are variable Average Annual Amount $220,000 180,000 75,000 50,000 25,000 50,000 500,000 300,000 200,000 1,600,000 ts per year of procedure business to your e $5 of profit from each of the procedures. You s, what is the lowest possible price you would be Total $220,000 $180,000 $75,000 $50,000 $25,000 $50,000 $500,000 $300,000 $200,000 $1,600,000 Cash Flow Scenario Month July, 20X2 August September October November December January, 20X3 February March April May June Question: If you have $380,000 of cash on hand January 1, 20X2, how much cash will you have at the end Month January 20X2 February March April May June July * August September October ** November December January 20X3 February March April May June 20X3 Total Cash on Hand June 20X3 *Open for Business ** 90 Billing Cycle Your new business venture will begin operation on July 1, 20X2. You will hire staff effective January 1, 20X2 with a cost of $40,000 per month. You know from experience that collections lag billing by 3 months (in other words, once you bill for a service, you must wait 90 days for the payment to be received. Your business volume is projected to be as follows: Volume 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 ave $380,000 of cash on hand January 1, 20X2, how much cash will you have at the end of June 20X3? Assume a 1 January 20X2 Opening Cash Balance = $380,000 Cash Balance $380,000.00 nd June 20X3 Billing $100,000 $100,000 $100,000 $100,000 $100,000 $100,000 $100,000 $100,000 $100,000 $100,000 $100,000 $100,000 e end of June 20X3? Assume a 100% collection rate. Monthly Expense Monthl y Billing Monthly Collections Ending Cash Balance You manage lab services in a large hospital. You have the following data on both the hospital's budgeted patient days and visits for budget year 20XX along with the ratio of lab tests to patient days or visits. RAW DATA Patient Days/Visits Budget Chemistry Hematology Bacteriology Cost per test Tests per FTE 2 North Bldg 2 South Bldg 19800 3.6 1.2 3.2 $20.00 200,000 ICU 21900 4.3 2.1 5.6 8760 2.9 1.4 3.6 Question: Based on this raw data provided , how many lab tests would you anticipate for the coming budget year? If each test is priced at $20.00, how much gross revenue would you budget? Assuming each full-time lab technician (FTE) can perform 200,000 tests each year, how many full-time lab technicians would you plan for? TEST COUNT BUDGET Chemistry Hematology Bacteriology Total 2 North Bldg 2 South Bldg 71280 23760 63360 158400 ICU 94170 45990 122640 262800 25404 12264 31536 69204 GROSS REVENUE BUDGET 2 North Bldg 2 South Bldg ICU Chemistry $1,425,600.00 $1,883,400.00 $508,080.00 Hematology $475,200.00 $919,800.00 $245,280.00 Bacteriology $1,267,200.00 $2,452,800.00 $630,720.00 Total $3,168,000.00 $5,256,000.00 $1,384,080.00 Staffing FTE Budget Chemistry Hematology Bacteriology Preliminary Final 4.95427 3.91007 6.58768 5 4 7 Total 15.45202 16 OPD 200000 4 3.5 5.5 OPD Total 800000 700000 1100000 2600000 OPD 990,854 782014 1317536 3,090,404 Total $16,000,000.00 $14,000,000.00 $52,000,000.00 $82,000,000.00 $19,817,080.00 $15,640,280.00 $56,350,720.00 $91,808,080.00 Staffing and Supply Budget Scenario Calculate the supplies budget necessary to operate your unit for the fiscal year beginning January 1, 20X8. It is your e budget year. The following spending data is available for the period January 1 to March 31, 20X7 during which time pr considered fixed, those marked (V) are considered variable. Inflation is planned at 4%. Expense Item Amount Billing Supplies (F) IV Solutions (V) Med/Surg Supplies (V) Miscellaneous (F) Office Supplies (F) Stock Drugs (V) $24,400.00 $288,108.00 $411,480.00 $18,400.00 $42,650.00 $42,650.00 In reviewing performance to date, you note that in January, you purchased $150,000 of D5W fluid replacement charge addition, you returned $2,800 of office supplies for credit from the vendor in February. These supplies were purchased Expense Account Orig. Base Period Amt Adjustments Adjusted Base Period Amt $24,400.00 Billing Supplies (F) $24,000.00 $288,108.00 -$ IV Solutions (V) 112,500.00 Med/Surg Supplies (V $411,480.00 Misc. (F) $18,400.00 Office Supplies (F) $42,650.00 $ 2,800.00 Stock Drugs (V) $42,650.00 Total $827,688.00 $ (109,700.00) You also need to prepare the salary budget for the same fiscal year. You have determined that staff needs are for 6.5 F the current staff with FTE values and hourly rates of pay as of January 1, 20X8: Position/Incumbent Hardy Roset Chang Martinez FTE Value Pay Rate 1.0 0.5 0.5 1.0 $16.30 $16.80 $16.50 $16.00 Jones 0.5 $16.75 A pay raise will be given to all staff on October 1st of each year at a rate of 8 percent. In making your calculations, alw whole dollar for annual salary amounts, but keep pennies in the hourly pay rates. New staff begins the new fiscal year Incumbent Hardy Roset Chang Martinez Jones Vacant Total FTE Value Pay Rate: Jan 1 20X8 Base Budget Amt eginning January 1, 20X8. It is your expectation that you will perform 24,820 procedures in the March 31, 20X7 during which time procedure volume amounted to 3,240. Items marked (F) are t 4%. 00 of D5W fluid replacement charged to IV solutions, which represents an entire year's supply. In uary. These supplies were purchased in a previous fiscal year. Base Period UAmount/Unit 3240 3240 3 3 3240 Budget Period UnitsBase Amt. Budget Inflation Rate 4% 4% 4% 4% 4% 4% 24820 24820 12 12 24820 ermined that staff needs are for 6.5 FTEs. The following are Total: $ - ent. In making your calculations, always round to the nearest New staff begins the new fiscal year at $16.00 per hour. Salary Increase% Months of Increase Increase Amt Salary Budget Inflation Amt Final Budget Amt