Hide Assignment Information

Instructions

Attached is data on the transactions (samples) conducted by Martin Diagnostics at their multiple locations for the first six months of 2019. Martin Diagnostics has main offices in Florence and Inglewood, branch offices in Tampa and Fresno, and a satellite office in Tullahoma. Administrative costs are $25,000 per month for main offices, $9,000 per month for branch offices, and $2,750 per month for satellite offices. Invoicing and collection costs $380,000 per year, and Martin has the capacity to process 25,000 invoices per year. Variable processing costs are $2.50 per transaction at main offices, $4.00 per transaction at branch offices, and $7.00 per transaction at satellite offices. In addition, Martin Diagnostics licenses the Fleming technology for $480,000 per year and has the capacity to perform 12,000 tests using Fleming technology each year. The technology used to perform Minimally Invasive tests costs $250,000 per year. California taxes diagnostic services at 5% of pretax income, while Tennessee and Florida have no income tax on diagnostic services.

You have several assignments:

Create an income statement, by state, assigning costs to offices and transactions as appropriate.

Evaluate the profitability of each office (test center), using an absorption costing approach. Any recommendations?

Evaluate the profitability of each procedure, using an absorption costing approach. Any recommendations?

Evaluate the profitability of each client, using a variable costing approach. Any recommendations?

If tests are performed at a branch office or main office for samples collected at a satellite location, there would be an additional $20 per transaction transportation costs. Would you recommend closing satellite offices?

Management is considering changing from a fixed monthly amount of administrative costs for each office to an allocation approach where each procedure is assigned an administrative cost of 8.5% of cost of services. What impact would this have on the reported profitability of each office?

Recommended approach

Transform the dataset into a table. Create additional columns as necessary to facilitate the analyses specified above. HINT: those could include columns representing date subdivisions, assignment of costs to transactions for processing costs and licensing fees, or important subtotals to support your income statements.

Create pivot tables as necessary to summarize transaction data for evaluations required above. HINT: Good analytical practices include making notes or labels adjacent to each pivot table to remind you of what you are trying to do with each pivot table.

Summarize your analyses and conclusions on a separate tab in your workbook, creating necessary reports in good form. Clear documentation will be helpful here, too.

Submit your formatted workbook to Assignments in D2L by the end of the day on Monday July 17..

Data Definitions:

Index: A number assigned to each transaction reported in the system

State: The state of residence for each test center

Test Number: A tracking number that identifies the department performing the test, the year the test was performed, and a sequence number

Test Date: The date the test was performed

Dept: The department performing the test. All tests using the Fleming technology are performed in the Fleming department.

Procedure: A general description of the test performed.

Test Center: The location where the test was analyzed (not necessarily where the samples were drawn),

Client: The company or entity that will be billed for the test.

Net Revenue: Amount billed for the test after consideration of contractual allowances and other adjustments to gross revenue.

Cost of Service: The cost recorded for the specific service. Costs can be from a subcontractor, from a standard price list, or from an algorithm that takes into account the time required for the test, the equipment and supplies needed, the intensity of the test, and the diagnostic code associated with the test results.