FOR ALL SPREADSHEET ASSIGNMENTS: THE FILENAME SHOULD BEGIN WITH YOUR LAST

NAME FOLLOWED BY WHATEVER YOU WANT TO CALL IT. For example, John Smith would

name his file Smithss#1.xls.

NOT Jsmithss#1.xls or Johnsmithss#1.xls or Copy of Smithss#1.xls. I take off 4 points for this.

Do not protect your file or send me a read-only file as I will put your grade in the file and

send it back to you. To turn in your assignment, attach it to an email and send it to me. When

these are turned in, I put them in a sub-directory UNOPENED and UNREAD until I am ready to

grade them. So, is you include questions in your email, I wont see them. Ask me directly.

I frequently receive multiple emails with assignments attached (early versions are apparently

incorrect). Despite discouraging this, it continues to happen. So, when I grade the assignments,

I delete earlier versions and only grade the last one sent.

If you want to work together on these, I dont object. BUT, if two or more of you turn in COPIED

solutions, I assign a grade of 0 to all of them and turn the assignment in as an Honor violation.

These problems are graded on a scale of 1 to 10. At the end of the semester, I scale the

regression assignments to 100.

In other words, the top score you can receive for these assignments is 60. At the end of the

semester, I will scale that to 100, meaning a 60 will get credit for 100 points; the same as a

semester test. If you receive 30 points for the regression assignments, you will receive a scaled

grade of 50 out of 100.

Process costing: (spreadsheet assignment #1)

LSW Co. uses a process costing system to account for manufacturing costs. The following data

pertains to the period just ended:

Direct Material Cost s

Beg. Inv.

Current

Production

Conversion Costs

$5,000

$3,000

96,000

58,000

The beginning inventory consisted of 2,500 units of partially complete products that were 80%

complete in material costs and 75% complete in conversion costs. Production on 48,000 units

was begun during the period, and of those, 3,000 were incomplete at the end of the period.

Those units in ending inventory were 30% complete in material costs and 40% complete in

conversion costs.

Required:

Using Excel or some other spreadsheet software, you need to develop an automatic process

costing system based on the FIFO method that will calculate LSW's ending inventory and cost

of goods manufactured for every period. To do this, you will need to label a tab on a

spreadsheet INPUT. On this sheet, the inputs that change every period will be entered.

IMPORTANT: the input sheet has data points only. No calculations of any sort should be on

the input sheet. For example, the number of products started, the number of units in ending

inventory, the percentage of completion with respect to materials, the material costs in

beginning inventory are all examples of data points. You then need to label another tab Cost

of Production Report or OUTPUT. On this tab, you will perform all calculations for the

current period by linking the data from the INPUT tab. In this manner, for every subsequent

period, one would only have to update the information under the INPUT tab and the OUTPUT

sheet would automatically calculate the correct EI balance and Cost of Goods Manufactured

(HINT: there should not be any hard numbers on the OUTPUT sheet).

Standard Costing: (spreadsheet assignment #2)

ACTUAL RESULTS

Total overhead costs

Variable overhead costs

Fixed overhead costs incurred

Number of products produced

Direct labor costs

Direct material costs

$483,500

$185,000

$298,500

11,900 units

$104,703 (10,265 hours @ $10.20)

$599,841 (120,450 lbs. @$4.98/lb.)

STANDARDS

Direct materials

Direct labor

Variable overhead rate

Fixed overhead rate

Total overhead rate

Normal activity level

Fixed Overhead Estimate

Per unit/DLH

10 pounds @ $5 per pound

1 DLH @ $10/DLH

$15 per allowed DLH

$25 per allowed DLH

$40 per allowed DLH

12,000 units

$300,000

Required:

Using Excel or some other spreadsheet software, you need to develop an automatic standard

costing system that will calculate JCL's variances for every period. To do this, you will need to

label a tab on a spreadsheet INPUT. On this sheet, the inputs that change every period will

be entered. IMPORTANT: the input sheet has data points only. No calculations of any sort

should be on the input sheet. For example, the actual production for the period, the actual per

unit cost for materials, the actual labor hours for the period, the actual variable overhead costs

are all examples of data points. This sheet would be accessed every period and updated as

these inputs change.

You then need to label another tab STANDARDS. On this tab, you will put all the standards

JCL uses to gauge their performance as well as the expected level of activity. This sheet is

accessed periodically as JCL re-visits and revises their standards. This would probably not

happen every period.

You then need to label another tab VARIANCES or OUTPUT. No hard numbers should go

on this sheet. It should perform all variance calculations for the current period by linking the

data from the INPUT tab and the STANDARDS tab. In this manner, for every subsequent

period, one would only have to update the information under the INPUT tab (and, less

frequently, the STANDARDS tab,) and the OUTPUT sheet would automatically calculate the

variances.

Variable Costing: (spreadsheet assignment #3)

March:

Beginning inventory = 2,500 units $26,500 ($10,000 variable)

Production = 32,000 units

Sales = 31,000 units @ $50 each

VMC (DM, DL, and VOH) = $129,600

FMC = $300,000

VS/A costs = $124,000

FS/A costs = $550,000

April:

Beginning inventory = ?

Production = 33,000 units

Sales = 34,000 units @ $50 each

VMC (DM, DL, and VOH) = $133,650

FMC = $300,000

VS/A costs = $126,000

FS/A costs = $550,000

Required: Variable costing income and full-absorption costing income for both March and

April. Prepare automated reports for both income methods for both months. A proper solution

will include a separate input section (under a separate tab) in which only data items will be

entered (you may use one input tab for both months). In a separate section (under another tab),

you should link items from the input tab to an output section that contains the income reports

(you may use one section for both variable and full-absorption costing income). Using the

information above, your solution will be for these periods.

Spreadsheet assignments #4, #5, and #6:

Regression Analysis

From the Texts:

Problem #4

Problem #5

Problem #6

9th ed.

3-21

3-22

3-52

8th ed.

3-21

3-22

3-44

7th ed.

3-21

3-22

3-44

6th ed.

3-19

3-21

3-41

5th ed.

3-35

3-51

3-52

For the first problem, ignore the requirements of the problem as stated in the text. This is a

regression assignment for you, and we are only using the problem for the data.

For the first problem (either 3-21, 3-19, or 3-35), you should generate one regression model.

Same for the second problem (either 3-22, 3-21, or 3-51).

For the third problem (either 3-44, 3-41, or 3-52), you should generate three regression models.

I need this answers in three hours.