Project 1
Name:
Type your Name here Complete using excel and submit by uploading to this assignment area by June 14 th 6:00 p.m. Central Time.
Your grade will be based on the accuracy of your answer and the proper usage of excel. Make sure
you use cell references, absolute value, use the IF statement to designate the variances as favorable or unfavorable and IFSUM for the subvariances.
I have some reference material in the chapter 12 folder.
Your answers should be on the analysis worksheet. I have posted the solution to Ex 12-22, 23,24, and 25
which should be reviewed and used as a model when developing your answers.
There is step by step example of this type of Strategic Analysis of Operating income starting on page 487, which you will find very helpful.
You should be using absolute value in your variance analysis, since we do not look at this analysis using positive and negatives.
Also, there is an excellent Problem for Self-Study on page 498 that can also be used as a model.
Make sure you save your file using your first initial and last name. Following a strategy of product differentiation. Computer Corporation makes a high-end Computer , High-end model.
Computer Corporation presents the following data for the years 2015 and 2016.
DATA Field:
Units of High-end model produced and sold
Selling Price
Direct materials (pounds)
Direct materials costs per pounds
Manufacturing capacity for units
Total manufacturing conversion costs
Manufacturing conversion costs per unit
Selling and customer-service capacity
Total selling and customer-service costs
Cost per customer of selling and customer -service capacity 2015
2016
20,700
20,850
$212
$215
60,000
61,000
$20
$21
25,000
25,000
$1,050,000 $1,075,000
$42
$43
60
59 Per customer
$360,000
$362,500
$6,000
$6,144 Grading Rubric:
Part A
#1
#2
#3
#4
Growth
Price-Recovery
Productivity
Part B
#1
#2
#3 0.3
0.3
0.1
3
3
2
0.3
0.5
0.5
10 Computer Corporation produces no defective units but it wants to reduce direct materials usage
per unit in 2016. Manufacturing conversion costs in each year depend on production capacity
defined in terms of units that can be produced.
Selling and customer-service costs for each year depend on the number of customers that the selling
and customer-service functions are designed to support each year. They had 50 customers in 2015 and 52 in 2016.
Required:
Your solution should be on the analysis worksheet. Rounded to the nearest Dollar. You must use the Data Field above by linking and using formulas on the Analysis worksheet.
Part A:
Please reference the analysis worksheet, which should contain your solution and I have tried to get you started.
You need to use cell references and use the "If" function to designate whether the component is Favorable
or Unfavorable. On the designation of the total of the subvariances you must use a SUMIF statement. All of your computational answers must include cell references.
If a variance is zero you do not have to designate the variance as U or F , since that will save you a little bit of time.
I will take off 1/2 pts. For each designation that does not use the IF function and SUM IF function for the totaling of each of the components-growth, price recovery, and productivity.
Variances should not be shown as a negative number, so please use the Absolute function, so none of your variances will show as a negative.
Calculate the following using only cell references on the sheet called analysis:
1. What is the operating income for 2015?
2. What is the operating income for 2016?
3. What is the change in operating income from 2015 to 2016?
4. Calculate the growth, price-recovery, and productivity components that explain
the change in operating income from 2015 to 2016. Make sure you break
the growth and price-recovery into revenue and cost effect subcomponents.
Part B: Answer the following questions based on your reading of chapter 13, application of this companies analysis, and our discussion of this topic in the discussion forum.
1. Which strategy is the company pursuing? Why?
2. Was the company's strategy successful in 2016? Why?
3. What do each of the components mean in respect to changes in operating income? You need to discuss Growth, price-recovery, and productivity. Type in name here
Part A:
Computer Corporation
Income Statement
For the Year ended
Sales
Costs
Direct material costs
Conversion costs
Selling and Customer-service costs
Total costs
Operating income 2015
$4,388,400 2016 Guidance:
Please click on the cell so that you
can see that I did not type in the
number but used cell references. Guidance:
A good check figure. The change
in Operating income should equal
the combination of your 3
components.
Guidance:
Again, please note
the cell referencing
by clicking on the cell.
Do not type in any
numbers. #3 Change in operating income
Growth Component:
Revenue
Cost: (20850-20700)*212 $31,800 F Materials
CC
Selling
Total Growth Component Guidance:
Please note that I have used
the IF statement to determine
if the variance is favorable or
unfavorable. Guidance:
Remember to use the SUMIF
statement for the Growth,
Price, and Productivity
components Price Recovery:
Revenue
Cost:
Material
Conversion Cost
Selling Costs
Total Price Recovery
Productivity:
Material
Conversion Cost
Selling Costs
Total Productivity
Verification that total components equals
change in income Part B: Answer the following questions based on your reading of chapter 13, application of this companies analysis, and our discussion of this topic in the discussion forum.
1. Which strategy is the company pursuing? Why? Worth .3 pt.
2. Was the company's strategy successful in 2016? Why? Worth 1/2 pt. 3. What do each of the components mean in respect to changes in operating income? You need to discuss Growth, price-recovery, and productivity. Worth 1/2 pt. for each component discussed.
Â
Attachments:
Answers
Status NEW
Posted 20 Jun 2017 02:06 AM
My Price 20.00
-----------
Attachments
file 1497924842-Solutions file.docx preview (56 words )
S-----------olu-----------tio-----------ns -----------fil-----------e
-----------Hel-----------lo -----------Sir-----------/Ma-----------dam-----------
T-----------han-----------k y-----------ou -----------for----------- yo-----------ur -----------int-----------ere-----------st -----------and----------- bu-----------yin-----------g m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l b-----------e q-----------uic-----------kly----------- on-----------lin-----------e a-----------nd -----------giv-----------e y-----------ou -----------exa-----------ct -----------fil-----------e a-----------nd -----------the----------- sa-----------me -----------fil-----------e i-----------s a-----------lso----------- se-----------nt -----------to -----------you-----------r e-----------mai-----------l t-----------hat----------- is----------- re-----------gis-----------ter-----------ed -----------onÂ----------- th-----------is -----------web-----------sit-----------e.
-----------
H-----------YPE-----------RLI-----------NK -----------&qu-----------ot;-----------htt-----------p:/-----------/wo-----------rkb-----------ank-----------247-----------.co-----------m/&-----------quo-----------t; -----------\t -----------&qu-----------ot;-----------_bl-----------ank-----------&qu-----------ot;-----------
-----------Tha-----------nk -----------you-----------
-----------
Not Rated(0)