10 Sep Complete a three-part assessment in a supplied Excel template in which you apply regression analysis to decision making,

Complete a three-part assessment in a supplied Excel template in which you apply regression analysis to decision making, determine unit costs, and analyze overhead using a predetermined rate.

 

Introduction

Cost estimation involves the use of a familiar accounting and finance tool: cost/benefit analysis. Through cost/benefit analysis, managers can explore and evaluate multiple alternatives to choose the one most beneficial to the organization’s bottom line. When evaluating alternatives, it is imperative that comparisons are “apples to apples;” that is, when estimating costs across multiple alternatives, cost data must be expressed in terms of variable and fixed costs, along with the drivers of these costs.

 

In Assessment 2, you will present information about estimating costs using multiple analysis methods, such as account analysis, statistical analysis, and regression output. Upon completion of this assessment, you will have demonstrated an understanding of the role that cost estimation plays in capturing and using existing accounting information for decision making by managers.

 

This assessment includes alternative cost systems, beginning with product and service costs.

 

Before beginning this assessment, take time to review the following topics:

 

Regression analysis.

Operations costing.

Overhead analysis.

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