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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- if the equation of regression line is y 5 then what
- formulas and relationships from linear regression
- chapter 11 simple linear regression
- excel part iv linear regression analysis
- excel assignment 2
- topic 12 regression
- regression analysis simple
- adequacy of regression models math for college
- worksheet on correlation and regression
Related searches
- excel lookup 2 criteria and return value
- 2 01 english 3 assignment advertising
- assignment 2 measurement
- assignment 2 01 english 3 flvs
- maths literacy grade 10 assignment 2 memo
- assignment 2 maths literacy grade 10
- mathematical literacy assignment 2 2021
- 2019 assignment 2 mathematics literacy grade 10
- mathematical literacy grade 10 assignment 2 2019
- mathematical literacy assignment 2 grade 10
- mathematical literacy grade 10 assignment 2 2019 memo
- memorandum assignment 2 mathematical literacy grade 10