1 1 on sheet 2 calculate the variable cost component

This preview shows page 1 - 3 out of 4 pages.

1
Image of page 1

Subscribe to view the full document.

1. On Sheet 2 , calculate the variable cost component ($/unit), the fixed cost component, and then summarize these two amounts in a final cost formula for each of the High/Low method and the Regression method . (see section below headed Creating a String) High/Low Method : -Go to a separate area on the worksheet and copy the two sets of input data to this area using paste special values (you can leave out the date column). Assign the following named ranges to the two ranges of data ---costunits for x and costdollars for y . -Sort the data with the primary key being the named range for the cost driver units. -Calculate the variable rate and the fixed component of the cost and put both in a cost formula using a string such as & or concatenate-----see example using & on next sheet. Regression Method : -use the same schedule from the High Low method and the same named ranges from the High/low method -use the functions SLOPE and INTERCEPT to calculate the variable and fixed components respectively and put into a equation cost formula using a string such as & or concatenate------see example using “& “ on next sheet . Variable costs should be stated to 3 decimal places and Fixed costs to zero decimals. The format of the cost component calculations and the final formulas should be the same for both methods (i.e., this is a comparison of methods). Include the name of cost and the cost-driver units (both referenced from input). Cell protect output cells and input cell headings 2. On Sheet 3 , prepare and output table that consists of four columns : Columns 1 and 2 will be a duplicate of the sorted cost driver units and related cost data (used previously on Sheet 2) except the column headings will now read, Actual units and Actual costs ; Column 3 will be the calculated cost data based on the High/Low cost formula (cost driver units x high/low cost formula)----called High Low cost ; Column 4 will be calculated cost data based on the regression cost formula (i.e., cost driver units x regression cost formula)-----called Regression cost . 3. On Sheet 3 , Prepare an XY chart with two cost lines: one for the high/low cost data, and one for the regression cost data, and the actual data points. (see detailed instructions below under Graphing Requirements ) Submit a printout of your input sheet, output sheets and cell contents for all sheets.
Image of page 2
Image of page 3
You've reached the end of this preview.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern