Computer company, a manufacturer of computer systems, started production in November 20XX.

1. Utility cost on the factory is $500 per month.

2. Janitorial costs in the factory are $1,500 per month.

3. Utility cost on the administrative building is $100 per month.

4. Administrative supplies used are $200 per month.

5. Raw material cost for a computer total $120 per computer system. The company purchased enough materials to produce 206 computer systems.

6. Workers on the production lines are paid an average rate of $24 per hour. (Direct labor is based on the number of units produced times the hourly time to make times the hourly rate.)

A computer system usually takes 3 hours to complete.

7. The rent of the equipment used to produce computer systems amounts to $4,000 per month.

8. Indirect materials cost $5 per computer that are produced.

9. Advertising costs for the computer systems will be $2,500 per month.

10. The factory building depreciation expense is $100 per month.

11. Property taxes on the factory building is $616 a month.

12. The company is leasing a building that houses a small administrative staff for $2,000 a month.

13. The administrative staff earns $3,000 a month.

14. The company just started the business, so you should realize that there are no beginning inventory amounts.

15. The company has completed everything in production, so there is no ending work in process.

There are 4 parts to this project.

Part 1: Using the scenario above fill in the charts below with the applicable information.

Data:

Units Produced

200

Direct Material

Work in process (WIP)

Finished Goods (FG)

Units sold

198

Beginning Inventory

0

Beginning WIP

0

Beginning FG

0

Sales Price

$580

Material purchased

???

Ending WIP

0

Calculate ending FG on COGM Worksheet after you compute the cost to make one unit

???

Ending Inventory

???

Material Used

???

Income Tax Rate

33%

A cost item could affect multiple areas. You are making your calculations based on this month's production and sales.

Total each of the columns after you have assigned all the costs to the applicable column(s)

I have entered the Factory utility costs to help get you started.

Product costs

Period Cost

Cost item

Direct Materials

Direct Labor

Manufacturing overhead

Prime costs

Conversion Costs

Selling and Administrative Expense

1. Utility costs on factory

500

500

2. Factory Janitor's Salary

3. Utility costs on admin. bldg.

4. Admin. Supplies

5. Materials used

6. Direct Labor

7. Equipment Rental

8. Indirect materials

9. Advertising

10. Factory building Depreciation

11. Property taxes on factory

12. Lease of Admin. Building

13. Administrative staff salaries

Totals

Part 2:

Use the information in part 1 to complete the Schedules of Cost of Goods Manufactured and Cost of Goods Sold in addition to the Income Statement on the separate

worksheets I have set up for you using only cell references. Look at the sheet tab COGM, COGS, and IS.

Also calculate the production cost per unit and the ending finished goods information on the COGM worksheet.

Use exhibit 2-7 on pages 45 and 46 as your model.

You should not type any number into a cell on the income statement or the Cost of Goods Manufactured Statement.

If I would change a number in the data field it would automatically change on the statements.

Part 3:

Prepare 2 column charts. I have a tutorial in the chapter 2 folder in course documents to assist you and developing a chart.

One chart should compare the total product costs to the total period costs, and another chart should compare

the three types product costs.

Insert these charts on separate sheets and comment on your chart results on the comment worksheet.

I have prepared a column chart comparing sales and advertising costs so that you can use the process as

a guide to prepare the 2 charts I have asked for. I have found from past experience that some students are not

familiar with how to prepare a chart in excel. If you are having problems

with the charts, please post questions to the discussion board.

A key to your success is setting up the data you want in your chart like I did

in the blue area below. Make sure you have the labels in the left column and in the next column the applicable $ amount.

Comment:

Sales

$114,840

Based on the comparison in the chart of sales to advertising we can see that a small percentage is

Advertising

2,500

going towards advertising. The company seems to have this cost under control, but November want to

look at opportunities that could show an increase in sales based on increased advertising.

Part 4:

The two situations below are independent of each other, so make sure you change your figures back to the original data

after you answer each of the independent questions on the comment worksheet.

a. What if you feel you can increase the price of the computer system to $600 without impacting the sales

volume, which statement or statements will be effected and what will be the new net income?

If you have used only cell references on your statements you should only have to change the figure in part 1 and

the applicable statement(s) will change automatically.

b. What if the company decides to increase the average rate to $25 per hour for production line workers

which statement or statements will be effected and what will be the new net income?

If you have used only cell references on your statements you should only have to change the figure in part 1 and

Need COGM, COGS, IS