Summer Project 2017

Project 2	The project relates to material covered in Chapter 2. Please post generic questions to the discussion board office forum or
Name: TYPE in your name here	e-mail me if you have a specific question and are unsure if it should be posted to the discussion board.
	Submit your completed project by uploading your solution using this Excel Template to the project 1 assignment area .
	Make sure you type in your name in cell A2 and save your file with the first initial of your first name, last name, and title of project.
	You will be graded on the accuracy of your answer and the usage of excel.
	Requirements 2-4 requires the usage of excel cell referencing and developing 2 charts.
	The project solution is worth a total of 10 pts. I will take off 5 pts. if you do not use the excel application appropriately.
	I have a demonstration exercise that demonstrates the development of the statements, which you can access by going
	to the Chapter 2 folder. The requirements below are a little bit different than my demonstration problem and textbook,
	but there are a lot of similarities. We have to realize that real life is not a textbook project.
	You should use this worksheet as your as your data reference sheet. In the web links area of our course I have excel tutorials you may find helpful,
	but feel free to ask questions in the office forum on Excel.
	I do not pre-grade, so if you email me make sure it is a specific question and not asking for me to review your solution.
	If you are using Excel 2013 or 2016, please make sure your file is compatible with Excel 2010 by saving in Excel 2010.
Objectives of Project 2
1. Analyze and classify product and period costs.
2. Develop the Cost of Goods Manufacturing statement for a manufacturing company.
3. Develop comparison charts.
4. Evaluate the costs of the production and operations.
Grading Rubric for Project 2	Possible points
Requirement #1-.1 each classification and .25 for calculated amounts	3
Requirement #2
COGM--.10 each	1.2
Each Calculation 1-6(.5 each)	3
Requirement #3
Charts - 1 pt. each	2
Comment on each chart (.15 each)	0.3
Requirement #4 (#1 worth .1 pt. and #2 and #3 each worth .2 pts.)	0.5
Total points possible	10
A deduction of 5 pts. if cell references are not used on requirements 2-4

Data and requirements for Project 2:
This company makes a single product.
The following information has been taken from the company's production, sales, and cost records for the month.
The ?? Areas will need to be calculated as you move through the requirements for this project.

Production in units	29,000
Sales in units	??
Beginning Finished Goods in Units	0
Ending Finished goods in units	??
Total Sales in dollars	$700,000
Unit selling price	$25
	Beginning of the month	End of the month
Inventories:
Raw Materials	$13,000	$12,000
Work in process	$30,000	$28,000
Finished goods	$0	??

Costs:
Direct labor	$80,000
Advertising Expense	$70,000
Indirect production labor	$6,950
Raw materials purchased	$130,000
Building rent(production uses 82% of the space, administrative and sales offices uses the rest of the space)	$35,000
Utilities, Factory	$32,000
Royalty paid for use of production patent, $2.35 per unit produced	??
Maintenance, factory	$20,000
Rent for special production equipment, $3,000 per month plus $.20 per unit produced	??
Selling and administrative Salaries	$140,000
Utilities, selling and administrative	$20,000
Part time janitor in the factory	$11,000

There are 4 requirements to this project.
Requirement #1: Use the scenario above to fill in the chart below with the applicable information.
A cost item can 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 Direct labor to help get you started. Also, the selling and admin. Costs have been combined into one column.
	Product costs					Period Cost
Cost item	Direct Materials	Direct Labor	Manufacturing overhead	Prime costs	Conversion Costs	Selling and Administrative Expenses
Direct labor		$80,000		$80,000	$80,000
Advertising Expense
Indirect production labor
Raw materials used							Calculated amount
Building rent(production uses 82%of the space, administrative and salesoffices uses the rest of the space)							Calculated amount
Utilities, Factory
Royalty paid for use of production patent, $2.35 perunit produced							Calculated amount
Maintenance, factory
Rent for special production equipment, $3,000 per month plus $.20 per unit produced							Calculated amount
Selling and administrative Salaries
Utilities, selling and administrative
Part time janitor in the factory
Totals


Requirement #2:	Use the information in requirement #1 and the data given above requirement #1 to complete the Schedule of Cost of Goods Manufactured.
	I have set up a separate worksheet for you to develop your COGM statement. Look at the sheet tab COGM. Use exhibit 2-7 on page 45 as your model .
	Also calculate the following on the COGM worksheet using cell references:
	1. Computation of Cost per unit: COGM/units produced
	2. Calculate the number of units that were sold
	3. Calculate the number of units in the finished goods inventory at the end of the month
	4. Calculate the cost in ending finished goods inventory at the end of the month.
	5. What is Cost of Goods Sold(COGS)?
	6. What is Income before taxes?
	You should not type any number into a cell on the Schedule of COGM or your calculations for 1-6.

Requirement #3:	Prepare 2 doughnut charts. I have a tutorial in the chapter 2 folder 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 of product costs.
	Insert these charts on separate sheets and comment on each of your chart results on the comment worksheet.

I have prepared a doughnut 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	$700,000	Based on the comparison in the chart of sales to advertising we can see that a small percentage is
	Advertising	$70,000	going towards advertising. The company seems to have this cost under control, but may want to
			look at opportunities that could show an increase in sales based on increased advertising.


Requirement #4:	Based on the current economic pressures, the company has decided to eliminate the part time janitor and the cost of that position
	and have the production workers clean up the area. Answer the following question:
	1. What will be the new COGM?
	2. What is Cost of Goods Sold(COGS)?
	3. What is Income before taxes?
	If you have used only cell references on your COGM statement you should only have to eliminate the figure in requirement #1 and
	the COGM statement will change automatically along with the other questions asked for on the COGM worksheet. Make sure you change the information back to the original information before submitting.
	Answer Requirement #4 on the Comments worksheet

2017summerproject2_1_1.xlsx

9 years ago

Report issue

Get Excellent grades

NOT RATED

Purchase the answer to view it

summer_project.xlsx