Analysis
Need thEHERO
Cost Estimation & Financial Analysis
Examination #2
General
Information and Instructions
Test Value and Point Structure
The exam is worth 15% of the final grade. It is graded on a 100-point scale.
General
1. Read every question carefully.
i. If you have multiple spreadsheets append a number to the naming format
b. Your work should be submitted as two attachments (as indicated above). Your answers should be presented as a report in a text document and include all conclusions (& include all charts, tables, and snapshots of your excel file). You should clearly state where assumptions are being made. Your spreadsheets must SHOW all calculations/work.
Formatting
1. There are no page limitations. Make your point concisely, precisely, & convincingly
2. Use a 12-point font for ease of reading
3. Use graph/charts/tables to clearly convey a point
4. Be sure to clearly state your conclusions
Note on grading & partial credit
Grading is primarily based on the materials that you present in the report (i.e. provided in your document). The spreadsheets are accessed as necessary to evaluate the appropriate level of partial credit, if any.
Cumulative Errors:
This exam may require you to reference your own work as you progress through the problem (for example, your own cost estimations may be used to build projected cash flows). All attempts will be made to grade in such a manner that mistakes made early in the assignment do not impact later work – which makes clearly organized spreadsheets critical to quickly assess where mistakes arise.
Legibility and organization of work:
It is imperative that your report & spreadsheets be of
professional quality
. You are all in professional careers and the quality of your work is assessed immediately. Your work must be clear and easy to follow. This is particularly important for your spreadsheets. I will deduct points for badly written reports. I cannot give credit for work I cannot follow or comprehend.
Point Weighting:
Points are assigned to each problem/sub-problem. Ensure you address all requested criteria and answer all questions in order to be eligible to receive full credit.
Due Diligence
It is your responsibility to demonstrate knowledge of the concepts, theories, and techniques being tested. You should apply that knowledge to critically analyze and answer the problems.
Requirements
Submit your results in the form of a well-supported written report. This means that every table, calculation, number needs to be included. Use diagrams, etc. to synthesize your findings where appropriate. Ensure all information is presented in the written report – the excel spreadsheet will be referenced ONLY for partial credit, if any.
Submit an Excel spreadsheet that contains all working calculations/formulas. The spreadsheet should be tidy and clear. You will forfeit points and incur progressive or cumulative errors if your spreadsheet is not easy to navigate, clearly labeled, or otherwise well organized.
Question #1
A production manager has obtained the following estimates of costs/revenues for radios. Their plan is to produce this model of radio for at least 5 years.
Yearly sales = 50,000 units/year (steady state/no growth expected)
Sales Price = $250/unit
Variable Cost = $201.37/unit
Initial Investment = $2,450,000
The manufacturing process is fully automated, so no learning is expected.
(10) A. How many units does the company need to produce/sell to breakeven?
(4) B. Does the company break even during the first year?
(3) C. How much PROFIT does the company make over a 5-year production run?
(3) D. Suppose the company can reduce the Variable Costs by $15.00 per unit if they invested in the “latest technology” for an additional $1,500,000 (FC Total $3,950,000). Based upon the potential profitability over the 5-year period only, should the company invest in the latest technology? How much additional profit, if any, does the investment in technology provide over the 5-year period?
Question #1 Breakeven
Question #2 Learning Curves
Question #2
Given the initial cost of $1,000 per unit determine the Learning Rate (LR) that “best fits” the actual observed cost data in GREY Fill by changing the LR value in YELLOW Fill/RED Text.
(15) A. What is the LR Value in cell with YELLOW Fill/RED Text that best fits the “Actual” data observed in GREY Fill? Refer to Chart A for initial visual indicators… (percentage must be presented to at least FOUR decimal points)
(5) B. If employee turnover significantly increases to the point few employees ever reach full efficiency, what do you suspect will happen to the observed learning rate for your company? Will it drift towards 99% or towards 75%? Explain why in 30 words or less.
A
B
C
D
E
Initial Cost estimated at
$1,000
Wright’s Equation
LR
95.000%
Initially set to 95%
b =
-0.074000581
-0.02914635
-0.415037499
Test LR
95.000%
98%
75%
Unit
Actual
Predicted LR
LR too low
LR too high
10
$618.65
$843.33
$935.09
$384.56
20
$599.18
$801.17
$916.39
$288.42
30
$524.99
$777.49
$905.62
$243.75
40
$529.16
$761.11
$898.06
$216.31
50
$499.36
$748.64
$892.24
$197.18
60
$460.48
$738.61
$887.51
$182.81
70
$477.24
$730.23
$883.53
$171.48
80
$464.01
$723.05
$880.10
$162.24
90
$430.98
$716.78
$877.08
$154.50
100
$402.35
$711.21
$874.39
$147.89
Chart A:
Question #3 Overhead Allocation
The lead accountant has compiled the following data in preparation for allocating overhead to three cost drivers.
Monthly Overhead Costs
Utilities
$610,000
Utilities (phone, electric, water, gas, etc.)
Admin/MGMT
$1,020,000
CEO, VPs, Supervisors, Secretary staff costs
Factory Costs
$1,870,000
Building lease & equipment
$3,500,000
(10) A. Allocate the $3,500,000 of monthly overhead costs to the following three cost drivers: 1) Units Produced 2) Direct Labor Hours
3) Raw Material Costs to determine the final product cost per unit (which includes Direct Labor, Raw Materials, and Overhead)
Labor Hrs Per Widget
Direct Labor Cost per Widget
Std Labor Rate ($/hr):
$30.00
Widget-1
4
$120.00
Widget-2
8
$240.00
Widget-3
16
$480.00
Drivers
Units Produced in Factory (all inclusive)
45,000
Widget-1
25,000
units per month
Widget-2
15,000
units per month
Widget-3
5,000
units per month
Direct Labor Hours
300,000
Widget-1
100,000
hours per month
Widget-2
120,000
hours per month
Widget-3
80,000
hours per month
Raw Material Costs
$25.00
per unit of Widget-1
$50.00
per unit of Widget-2
$200.00
per unit of Widget-3
(5) B. Which of the three methods causes the Final Product Cost of Widget-3 to exceed $900? In 30 words or less, why did this occur?
(5) C. If the current sales price of Widget-3 is only $1,000, as the lead accountant, name at least TWO strategies you should consider to
make Widget-3 a more viable product to sell/produce? Increasing the sales price is not an option due to prevailing market
competition.
Question #4 Forecasting
Year
30 Year (%)
1972
7.29%
1973
7.54%
1974
8.58%
1975
8.82%
1976
8.73%
1977
8.75%
1978
9.35%
1979
10.50%
1980
16.32%
1981
15.58%
1982
16.89%
1983
12.78%
1984
13.65%
1985
13.20%
1986
9.94%
1987
9.83%
1988
10.20%
1989
11.05%
1990
10.37%
1991
9.50%
1992
8.85%
1993
7.46%
1994
8.32%
1995
8.32%
1996
7.93%
1997
8.14%
1998
7.14%
1999
6.92%
2000
8.15%
2001
7.08%
2002
6.99%
2003
5.81%
2004
5.83%
2005
5.86%
2006
6.51%
2007
6.18%
2008
5.92%
2009
4.81%
2010
5.10%
2011
4.84%
2012
3.91%
2013
3.45%
2014
4.34%
2015
3.67%
2016
3.61%
2017
4.05%
2018
4.54%
Question #4 (Continued)
Chart B:
Question #5 True/False
Answer the following True/False questions (20 points):
[Utilize BOLD TEXT to indicate the correct answer]
1. Underestimation is a primary cause of project failure.
True False
2. Cutting costs is important and should be a perpetual effort
True False
3. Efficient markets seek equilibrium conditions
True False
4. According to Standish, more than 50% of all IT projects experience cost overruns of 51% or more
True False
5. Learning is linear.
True False
6. Class 1 estimates are the initial/basic estimate, usually involving the largest range of error
True False
7. When developing a cost estimate, determining the estimates “purpose” is one of the most important steps
True False
8. Tetlock believes Hedgehogs make better forecasters due to their cognitive style that is biased towards a single idea or approach.
True False
9. Updating an estimate is recommended only when it is easily achieved
True False
10. Engineering cost estimates are quick and easy to develop
True False
Extra Credit (2):
A. The list below are a few examples of Cognitive Bias in forecasting. Which of the below are NOT forms of bias cited by Kavanagh/Williams:
Anchoring Availability Conservatism
Halo Effect Normalcy Overconfidence
Recency Search for Supportive Evidence
Selective Perception Underestimating Uncertainty
B. Tetlock determined that “Expert Judgement” has no role in forecasting. True or False
Below are ACTUAL 30-year average mortgage rates over tha past 47 years, as graphically shown in Chart A below. In addition, there is a “Linear” trend line
provided. Unfortunately, this “Linear” trend line includes significant error as evidenced by the low R
2
value of 0.549974.
(6) A. Tasking: Using the data provided, create your own chart, with linear trend line (show your own final chart with trend line & equation).
(10) B. Following sound forecasting principles, select a different trend line that not only fits the overall data, but generates a reasonable/feasible
5-Year forecast out through 2022 by choosing ONE of the following:
1) Exponential 2) Linear 3) Logarithmic 4) Polynomial (order 2 thru 6) 5) Power 6) Moving Average (periods 2 thru x)
Show your forecast using your chosen method (1 thru 6). NOTE: You MUST show your forecast going out thru 2022 along with the
corresponding CALCULATED values using your chosen forecasting algorithm.
Chart A:
C. In Chart B below, the average 30-year mortgage rate is shown over the past 10 years. A “Poly4” trend line is added to “forecast” years
2019 thru 2022.
(4) C: Through observation, provide TWO key reasons this forecast can/should be considered “flawed.”