Schedule of Cost of goods manufactured, Cost of goods sold

Computer company, a manufacturer of computer systems, started production in November 2011.
Utility cost on the factory is $600 per month.
Utility cost on the administrative building is $300 per month.
Raw material cost for a computer total $120 per computer system. The company purchased enough materials to produce 504 computer systems.
A computer system usually takes 3 hours to complete.
In addition, the rent of the equipment used to produce computer systems amounts to $4,000 per month.
Indirect materials cost $6 per computer that are produced.
Production supervisor's salary for the month is $7,000.
Advertising costs for the computer systems will be $2,000 per month.
Administrative supplies used are $200 per month.
The factory building depreciation expense is $500 per month.
Property taxes on the factory building is $300 a month.
The company is leasing a building that houses a small administrative staff for $2,500 a month.
The administrative staff earns $3,000 a month.

The company just started the business, so you should realize that there is no beginning inventory amounts.
The company has completed everything in production, so there is no ending work in process.
The company produced 500 computer systems and had no unfinished goods(work in process). They sold 496 computer systems for $425.

Required: Using the scenario above to fill in the missing information below with the applicable information.
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.

Part 2: Use the information in part 1 to complete the Schedule of Cost of Goods Manufactured, Schedule of Cost of Goods Sold, and 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 ending finished goods information on the COGM worksheet.
Use exhibit 2-7 on pages 48-49 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 your data field it would automatically change on the statements.

Part 3: Prepare 2 bar charts and comment on the results. I have a tutorial in the chapter 2 folder in course documents to assist you and developing a chart using Excel .
One chart should compare the total product costs to the total period costs, and another chart should compare
the three types of product costs. Comments should be typed onto the comment worksheet.
Insert these charts on separate sheets and comment on your chart results on the comment worksheet.

I have prepared a bar 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 Revenue: $210,800 Based on the comparison in the chart of sales to advertising costs we can see that a small dollar amount is

Advertising: $2,000 going towards advertising costs. The company seems to have this cost under control, but should monitor this cost and make sure it is generating increased sales.

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 $450 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 $22 per hour for the 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 the applicable statement(s) will change automatically.