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.