Complete a three-part assessment in a supplied Excel template, applying regression analysis to decision making, determining unit costs, and analyzing overhead using a predetermined overhead rate.
Preparation
Use the Assessment 2 Template [XLSX] to complete the following. Each part is a different tab in the template.
Part 1: In the template, interpret regression results from the delivery company.
Compare your estimates to the controller’s estimates and state your reasons for supporting or rejecting your cost equation.
Write a one-page report informing management of the correct volume that will generate $11,000 per month in operating profits before taxes.
Show all calculations.
Part 2: Compute the cost per unit of the two different models of calculators produced by Nevada Instruments based on the operations costing. Show all calculations.
Part 3: Complete calculations and analysis of overhead using a predetermined rate for Atchison Company. Show all calculations.
Submit the completed template for Assessment 2.
Instructions
Assessment 2 Part 1: Interpretation of Regression Results
Part 1 Scenario
Your company provides various delivery services. Management wants to know the volume of a particular delivery that would generate $11,000 per month in operating profits before taxes. The company charges $22 per delivery.
The controller’s office has estimated overhead costs at $9,900 per month for fixed costs and $12 per delivery for variable costs. You believe that the company should use regression analysis. Your analysis shows the results to be:
Monthly overhead=$26,501+$10.70 per delivery.
Your estimate was based on the following data:
Data Used for Estimate
Month
Overhead Costs
Number of Deliveries
1
$159,630
$12,510
2
$183,990
$15,060
3
$142,860
$11,430
4
$194,430
$15,450
5
$151,890
$12,180
6
$150,120
$11,970
7
$192,600
$15,660
8
$154,080
$12,630
9
$141,030
$11,250
10
$184,800
$15,300
11
$203,490
$12,780
12
$183,120
$14,580
13
$180,630
$14,730