Excel Problem dealing with management accounting

Problem 1 Send your completed problem via the dropbox in the student tools area.
Due June 24. You will be graded on the accuracy of your answer and the usage of excel.
Parts 2,3, 4, & 5 must use excel cell referencing and the chart wizard.
The problem solution is worth a total of 10 pts. I will take off 5 pts. if you do not use the excel application
appropriately. You need to use only cell references on the 2 statements you are developing.
I have a demo exercise that demonstrates the development of the statements, which you can access by going
to the course documents area Chapter 2. The requirements and problem
below are a little bit different than my demo problem, but there are a lot of similarities. I have set up
the data field for you, so just use this sheet as your reference. Also, I have already started the 2
statements on separate sheets for you. Make sure you look at the comments I inserted.
Toy Company produces dolls. For the year ended December 31, 2002, the company reported the following information.

DATA FIELD:
Direct materials inventory, January 1 $15,000
Direct materials inventory, December 31 25,000
Direct materials purchased 80,000
Salary of the production supervisor 38,000
Direct labor used in the factory 80,000
Rent on the office building 24,000
Property tax on the factory 10,000
Maintenance on factory equipment 20,000
Office supplies used by sales personnel 2,000
Sales commissions paid 15,000
Administrative wages 24,000
Depreciation, factory equipment 4,000
Utility cost, factory 7,000
Work in process Inventory, January 1 8,000
Work in process Inventory, December 31 12,000
Finished Goods inventory, January 1 20,000 Units 110,000
Finished Goods inventory, December 31 ???? Units ????

Number of units produced 45,000 Units
Number of units sold 58,000 Units
Selling price per unit $8 per unit

Required:

Part 1: Using the information above fill in the chart below with the product and period costs being incurred in 2002. A cost
item could affect multiple areas.
Total each of the columns after you have assigned all the costs to the applicable column(s)
I have entered the direct materials used to get you started.
Product costs
Direct Manufacturing Period Prime Conversion
Cost item Direct Materials used Labor overhead Costs costs Costs
Material used 70,000 70,000

Totals

Part 2: Use the data field I have set up above starting in cell A13 to produce the following statements. Do not type in numbers into your statements.
a. Prepare a Statement of Cost of Goods Manufactured for the year ended 2002 like the one on page 45.
b. Compute the unit product (manufacturing) cost to produce one doll.
Remember to use the data field on this sheet to reference the information in preparing your Statement
of Cost of Goods Manufactured and Income Statement. Use a separate sheet for each statement.
I have started each of the statements for you. Look at the sheet tab COGM and IS.

Part 3: Prepare an income statement like page 44, assuming the company sold 58,000 dolls for $8 each.
Assume that the company uses a FIFO inventory flow assumption.
The numbers on the statements should only appear because of cell references. If I change a number
in the data field it should automatically change on the statements.
Please note that you should not be typing in any numbers into the statements but using only cell
referencing.

Part 4: Prepare 2 pie charts.
1. One chart should compare the total product costs to the total period costs.
2. Another pie chart should compare three types product costs.
Insert these charts on separate sheets and comment on your chart results.

Part 5: The three situations below are independent of each other, so make sure you change your figures back to answer
the questions.
a. What if you feel you can increase the price of the doll to $9 without impacting the sales
volume, which statement or statements will be affected and what will be the new net income?
If you have set up your data field correctly and only cell referenced in the statements you will only need to change one number and
the applicable statement(s) will change automatically.
b. What if the temperatures drop to unreal lows, which increases
utility costs in the factory to $9,000, which statement or statements will be affected and what will be the new net income?
If you have set up your data field correctly you will only need to change one number and
the applicable statement(s) will change automatically.
c. What if the rent on the office building goes up to $28,000. Which statements will change?
What will be the new net income?
If you have set up your data field correctly you will only need to change one number and
the applicable statement(s) will change automatically.