Please review the attached excel project, need assistance

Please review the attached excel project, need assistance

ATTACHMENT PREVIEW

Download attachment

Please make sure you read the academic honesty policy, since I want to make sure everyone realizes that they should not be discussing
this problem outside the discussion board.
This is an individual assignment and should be completed by each student on their own.
I realize some of you maybe best friends or relatives, but this is an individual assignment and you should not be working as a team.
Academic Honesty Policy:
Each student is assumed to be a mature and
responsible adult with high academic and moral standards.
There is no
place in the business profession or the classroom for dishonesty.
Therefore, any student found cheating or presenting material in a dishonest
fashion would be dismissed from the course and given a course grade of
“F.”
You should not exchange files or discuss turn in problems or take
home exams outside the discussion board.
It is unfair to your classmates
to discuss turn-in problems and take home exams via e-mail or to send
files with the solution to any member of the class.
I always have a
discussion thread set up for students to ask questions, so this is the place
you should ask questions on these assignments and exams.
Remember, it
does not matter what type of assignment it is, if you are found cheating you
will receive an F for the course and the College of Business will be
informed of your actions for further proceedings.

Unlock Solution Unlocking…

Project 1
The project relates to material covered in Chapter 2.
Please post generic questions to the discussion board office forum or
Due Tuesday, April 2 11:59 pm central Time
e-mail me if you have a specific question or unsure if it should be posted to the discussion board.
Name:
Heidi Shultz
Send your completed project by uploading your solution into the project 1 assignment area in #2 of this assignment area.
Make sure you type in your name in cell A3 and
save your file with the first initial of your first name, last name, and title of problem
You will be graded on the accuracy of your answer and the usage of Excel.
This problem
requires the usage 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 course documents area in 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 problem.
You should use this worksheet as your
as your data reference sheet.
I have external links to excel tutorials and feel free to ask generic questions in the office
on the discussion board.
Objectives of Project 1
1.
Classify product and period costs.
2.
Develop the Cost of Goods Manufacturing statement for a manufacturing company.
3.
Develop comparison charts.
This company makes a single product.
The following information has been taken from the company’s production, sales, and cost records for the month.
Production in units
31,000
Sales in units
??
Beginning Finished Goods in Units
0
Ending Finished goods in units
??
Total Sales in dollars
$924,000
Unit selling price
$32
Beginning of the month
End of the month
Inventories:
Raw Materials
$11,000
$13,000
Work in process
$30,000
$35,760
Finished goods
$0 ??
Costs:
Direct labor
$88,000
Advertising
$110,000
Indirect production labor
$19,000
Raw materials purchased
$140,000
$60,000
Utilities, Factory
$35,000
??
Maintenance, factory
$11,000
??
Selling and administrative Salaries
$75,000
Utilities, selling and administrative
$16,000
Part time janitor in the factory
$12,000
There are 4 requirements to this project.
A cost item can affect multiple areas.
You are making your calculations based on this month’s production and sales.
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
$88,000
$88,000
$88,000
Advertising
Indirect production labor
Utilities, Factory
Maintenance, factory
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.
Also calculate the following on the COGM worksheet:
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 year
4.
Calculate the cost in ending finished goods inventory at the end of the year.
Use exhibit 2-7
on page 48 as your model .
You should not type any number into a cell on the Schedule of COGM.
If I would change a number in your data field it would automatically change on the statements.
Requirement #3:
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 your chart results on the comment worksheet.
I have prepared a pie 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
$924,000 Based on the comparison in the chart of sales to advertising we can see that a small percentage is
Advertising
$110,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 questions:
1.
What will be the new COGM?
2. Computation of Cost per unit:
COGM/units produced
3.
Calculate the cost in ending finished goods inventory at the end of the year.
If you have used only cell references on your statements you should only have to eliminate the figure in requirement #1 and
the COGM statement will change automatically.
Make sure you change the information back to the original information before submitting.
Answer Requirement #4 on the Comments worksheet
Grading Rubric for Project 1
Possible points
4
Requirement #2
COGM–.10 each
1
Questions .50 each
2
Requirement #3
Charts
– 1 pt. each
2
Comments
0.25
Requirement #4
each question .25
0.75
10
Deduct 5 pts. If did not use cell referencing on COGM
Building rent(production uses 75%
of the space, administrative and sales
offices us the rest)
Royalty paid for use of
production patent
, $3.00 per
unit produced
Rent for special production equipment,
$3,800 per month plus $.30 per unit produced
Requirement #1
:
Using the scenario above fill in the chart below with the applicable information.
Total
each of the columns after you have assigned all the costs to the applicable column(s)
Direct
Labor
Manufacturing
overhead
Prime
costs
Conversion
Costs
Selling and
Administrative
Expenses
Raw materials
used
Building rent(production uses 75%
of the space, administrative and sales
offices us the rest)
Royalty paid for use of production patent, $3 per
unit produced
Rent for special production equipment,
$3,800 per month plus $.30 per unit produced
Prepare 2
pie
charts. I have a tutorial in the chapter 2 folder in course documents to assist you and developing a chart.
Requirement #1-.
2 each
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 stil  in raw state.  The 
calculation here should match the material used your 
compute on the COGM statement.

Show entire document

Leave a Comment