Excel Assignment #2



Excel Assignment #1

High Low Method and Mixed Costs

Summer 2013

Teresa Company has had trouble estimating its total costs. The company knows that some of its costs are variable and some are fixed, but no idea how to separate out the variable and fixed components. They have asked you to evaluate their costs so that they can estimate them for the upcoming budget. They expect that their costs are related to Sales.

|Month |Sales |Total Cost |

|January |$3,891,619 |$3,328,497 |

|February |$4,513,412 |$3,900,160 |

|March |$4,384,983 |$3,922,667 |

|April |$3,812,266 |$3,283,780 |

|May |$3,113,116 |$3,016,046 |

|June |$3,889,124 |$3,364,680 |

|July |$3,880,982 |$2,970,610 |

|August |$3,820,982 |$3,016,816 |

|September |$4,171,268 |$3,534,433 |

|October |$4,701,841 |$3,395,087 |

|November |$3,221,694 |$3,488,124 |

|December |$3,429,072 |$2,723,931 |

1. What is the mixed cost (y-values)?

2. What is the activity (x-values)?

3. What is the high month?

4. What is the low month?

5. Using the high/low method, determine the variable rate (slope).

[Include the calculation of the change in y, change in x, and calculation of slope]

6. Using the high/low method, determine the fixed cost portion of the mixed cost (intercept).

[Formula should include link to slope calculated in part 5 and high(low) data in the original data. Use b=y-mx where, y and x are the high or low amounts from the given data and m is the slope calculated in part 5]

7. Using the excel regression formula (=slope), determine the variable rate (slope) [For a tutorial on using the =slope formula, see the video for the Excel #2 template on the ACCT 102 website].

8. Using the excel regression formula (=intercept), determine the fixed cost portion of the mixed cost (intercept). [For a tutorial on using the =intercept formula, see the video for the Excel #2 template on the ACCT 102 website].

9. If the company expects Sales to be $3,500,000, estimate the Total Costs using both the results from the high-low method (part 5 &6) and regression (part 7&8).

[Determine Total Cost using high/low method (linking to parts 5&6) and Total Cost using regression (linking to parts 7&8)]

10. If Sales are $3,500,000, prepare a contribution margin income statement using both the high-low method and regression variable cost rates and fixed costs.

11. Calculate the operating leverage.

12. Based on you calculation of operating leverage in Part 11, if sales increase by 15% then the resulting net income will be

a. Higher than 15%

b. Lower than 15%

c. Equal to 15%

Rules regarding the completion of the Excel Assignments:

NOTE: You MUST use Microsoft EXCEL and not any other spreadsheet program.

1) You MUST use the Excel Templates provided on the Accounting 102 website for this quarter.

2) You MUST download the Excel Templates using YOUR account from the Accounting 102 website.  Each template is individually coded to prevent academic dishonesty.  Any student found using the template from another student’s account AND the student that allows another student to use his/her account will also receive a zero on the assignment and be subject to further penalties for academic dishonesty.

3) You MUST sign the academic honesty pledge or you will automatically receive a zero--NO EXCEPTIONS!

4) You MUST use formulas and links whenever possible. If you simply type in your solutions rather than use formulas, you will receive a zero.

5) You MUST name your Excel files per the instruction sheet (LastNameFirstNameExcel1). Failure to name your Excel file correctly will result in a zero--NO EXCEPTIONS. Please make sure that there are no blank spaces in the file name (e.g., SmithJohnExcel1)

6) Your Instructor will inform you how to send your assignments electronically. Failure to follow YOUR INSTRUCTOR’S INSTRUCTIONS will result in a grade of zero for the assignment (note that each instructor may have his/her own required method of turning in the assignment).

7) To receive credit, your instructor must RECEIVE your assignment by the due date no later than 11pm without exception.

8) This assignment MUST be completed individually. Working together constitutes academic dishonesty and will result in receiving a failing grade for the quarter.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download