ASSIGNMENT 1 – ALLOCATE DEPARTMENTAL COSTS

QVC Academic Medical Center (AMC) facilities maintenance department had $2M in direct costs in 2017. Under current policy, these costs must be allocated to NCU AMC’s two revenue producing patient services departments using the direct method. The patient services departments cover 1M square feet and submitted 1,230 work orders. Create an Excel workbook with two tabs and calculate the allocation rate if (be sure to label the tabs accordingly):

1.Square footage is used as the cost driver.

2.Number of submitted work orders is the cost driver.

Length: Two Excel Workbooks

ASSIGNMENT 2 – DETERMINE SERVICE-LINE, BREAK-EVEN, AND PROFITABILITY

QVC AMC’s lab service is considering adding a specialty test to its lab outreach options. The following data is available to assist with the pricing of the new test:

Variable cost per test $3

Annual direct fixed costs $125,000

Annual overhead allocation $10,000

Expected annual utilization (number of tests) 25,000

In the Excel workbook you created for Part 1, add two tabs in which you answer/solve the following (be sure to label tabs accordingly):

1.What per-test price is required for the service to break even?

2.What per-test price is required for the service to earn a $23,000 profit?

Length: One Excel Workbook

PROVIDE A SUMMARY OF EXCEL FUNCTIONS USED AND METHODS USED IN DETERMINING RESULTS.

Gapenski, L., & Reiter, K. (2015). Healthcare finance: An introduction to accounting and financial management (6th ed.). Chicago, IL: Health…CHAPTER 6-7