Summer Project 2017

2017summerproject2_1_1.xlsx

Home >Business & Finance homework help >Accounting homework help >Summer Project 2017

Project 1

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	Cindy Nye: Guidance: When determining product costs you should include those costs that are used in production. You must calculate materials used and not material purchased for your calculation of product cost, since a portion of the materials purchased are still in raw state. The calculation here should match the material used your compute on the COGM statement.						Calculated amount
Building rent(production uses 82% of the space, administrative and sales offices uses the rest of the space)							Calculated amount
Utilities, Factory
Royalty paid for use of production patent, $2.35 per unit 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

COGM

Company, Inc.
Schedule of Cost of Goods Manufactured
For the Month ended May 31, 2017
Make sure you are using cell references and not typing in the number
Direct materials:
Beginning Raw materials inventory,May 1
Add: Purchases of Material
Materials available for use
Deduct: Ending Raw materials, May 31
Raw materials used
	Cindy: Guidance: This number should match with what you have as the material used in Requirement #1.	Direct labor
Manufacturing overhead: List out each MOH below
Total Manufacturing overhead
Total manufacturing costs
Add: beginning work in process
Subtotal
Less: Ending work in process
Cost of Goods Manufactured
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
			Cindy Nye: Guidance: Ending finished goods is calculated by adding beginning finished goods+units produced-units sold	Cindy: Guidance: Click on the cell and you will not see a number, but a cell reference. So I can change the data field and my statement will automatically change. That is why we use excel, so we can automatically change data and all the linked statements will change automatically.	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?

Sales and Adv. Chart

Comparison of sales to advertising

Sales Advertising 700000 70000

Comments

Requirement #3 comment on both chart results:
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 questions (do not use cell referencing for this requirement, since you should convert back to the original data):
	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.