Computer Project # 2 Due April 1 * If you are familiar with \"Excel\" do the fol

Question

Computer Project # 2

                                               Due April 1

* If you are familiar with "Excel" do the following problem using “the High-Low Method” or “Regression Analysis” and turn in the Excel file including your recommendation. If you use Mac, refer to Instructions posted in Bb Assignment.

*To run the regression using the Excel Program, use Tools, Data Analysis, Regression and choose x – cost driver and y – costs we want to predict. If you use regression for the first time, click Tools, Adds-in, Analysis toolpak, Regression just for setting up the regression program once.

Problem: The King Corporation is developing a model to explain and predict overhead costs. It produces only one product-line so that a simple count of the number of units produced each month may be a good measure of activity to begin with. The company has collected data for the past twelve months:

Month Overhead     Production                                            Cost            Units

---- ------------   ----------   

1     $250,000       34,000        

2      184,000       25,000     

3      165,000       21,000     

4      178,000       23,000     

5      192,000       26,000     

6      225,000       29,000     

7      210,000       28,000     

8      230,000       29,000     

9      195,000       27,000    

10      224,000       29,000     

11      200,000       30,000       

12      240,000       32,000     

Required:

Open a spreadsheet and enter the data.

Create another worksheet and enter formula to calculate variable cost per unit and total fixed costs. Use y = aX + b formula. Enter a (variable cost per unit) in Cell 20B and b (total fixed costs) in Cell 20C.

Create another worksheet and run regression (Least Squared Method) and save the output.

Create another worksheet and save both outputs by linking worksheets. Determine whether the High-Low method or Regression Analysis is better to predict monthly overhead costs. Explain which model is the better? Why or why not?

Use absolute cell address in y = aX + b formula (Use absolute August X Value) to predict January and December overhead costs. Do you think these predicted overhead costs are reasonable? (Refer to our textbook from pp. 209-211 for regression analysis details.)

Explanation / Answer

Utilize the high low technique to determine:variable cost per unit , add up to settled expenses, and the aggregate cost condition.

Stage 1: Select the movement with the most elevated and least sums (miles is the action). Pick 49,000 and 79,000 miles (March and April).

Stage 2: Select the costs contiguous those exercises: $80,000 and $101,000. Note that the cost sums neighboring these action levels will be utilized, despite the fact that these cost sums are not the most elevated and least 'costs.' Activity level standards.

Stage 3: Determine the incline (the variable cost per unit):

[$101,000 - $80,000]/[79,000 - 49,000] = $0.70 per unit

Note that the movement levels and costs utilized are those of the two months picked in stage 1.

Stage 4: Plug the variable cost from stage 3 and the movement (miles) and aggregate cost from either the high or the low point into the aggregate cost condition, to settle for settled costs (FC):

TC =

VC x

+ FC

$101,000 =

$0.70 (79,000)

+ FC

           So FC = $45,700

The total cost equation is then: TC = 0.70x + 45,700

TC =

VC x

+ FC

$101,000 =

$0.70 (79,000)

+ FC

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.