Need these answered fully with excel as well as the work/image attached underneath each question from the spreadsheet. This is due Sunday 3/21 at 11:00 pm. Thanks!
*NO CHEGG OR COURSEHERO
Don't use plagiarized sources. Get Your Custom Essay on
Cost Estimation & Financial Analysis
Just from $13/Page
Cost Estimation & Financial Analysis
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.
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.
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 .pdf 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.
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.
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 #1A 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/unitVariable Cost = $201.37/unitInitial Investment = $2,450,000The 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 BreakevenQuestion #2 Learning CurvesQuestion #2Given 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.ABCDEInitial Cost estimated at$1,000Wright’s EquationLR95.000%Initially set to 95%b =-0.074000581-0.02914635-0.415037499Test LR95.000%98%75%UnitActualPredicted LRLR too lowLR too high10$618.65$843.33$935.09$384.5620$599.18$801.17$916.39$288.4230$524.99$777.49$905.62$243.7540$529.16$761.11$898.06$216.3150$499.36$748.64$892.24$197.1860$460.48$738.61$887.51$182.8170$477.24$730.23$883.53$171.4880$464.01$723.05$880.10$162.2490$430.98$716.78$877.08$154.50100$402.35$711.21$874.39$147.89Chart A:Question #3 Overhead AllocationThe lead accountant has compiled the following data in preparation for allocating overhead to three cost drivers.Monthly Overhead Costs Utilities$610,000Utilities (phone, electric, water, gas, etc.)Admin/MGMT$1,020,000CEO, VPs, Supervisors, Secretary staff costsFactory Costs$1,870,000Building 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 WidgetDirect Labor Cost per WidgetStd Labor Rate ($/hr):$30.00Widget-14$120.00Widget-28$240.00Widget-316$480.00Drivers Units Produced in Factory (all inclusive)45,000Widget-125,000units per month Widget-215,000units per month Widget-35,000units per month Direct Labor Hours300,000Widget-1100,000hours per month Widget-2120,000hours per month Widget-380,000hours per month Raw Material Costs$25.00per unit of Widget-1 $50.00per unit of Widget-2 $200.00per 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 ForecastingYear30 Year (%)19727.29%19737.54%19748.58%19758.82%19768.73%19778.75%19789.35%197910.50%198016.32%198115.58%198216.89%198312.78%198413.65%198513.20%19869.94%19879.83%198810.20%198911.05%199010.37%19919.50%19928.85%19937.46%19948.32%19958.32%19967.93%19978.14%19987.14%19996.92%20008.15%20017.08%20026.99%20035.81%20045.83%20055.86%20066.51%20076.18%20085.92%20094.81%20105.10%20114.84%20123.91%20133.45%20144.34%20153.67%20163.61%20174.05%20184.54%Question #4 (Continued)Chart B:Question #5 True/FalseAnswer 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 False2. Cutting costs is important and should be a perpetual effortTrue False3. Efficient markets seek equilibrium conditionsTrue False4. According to Standish, more than 50% of all IT projects experience cost overruns of 51% or moreTrue False5. Learning is linear.True False6. Class 1 estimates are the initial/basic estimate, usually involving the largest range of errorTrue False7. When developing a cost estimate, determining the estimates “purpose” is one of the most important stepsTrue False8. Tetlock believes Hedgehogs make better forecasters due to their cognitive style that is biased towards a single idea or approach.True False9. Updating an estimate is recommended only when it is easily achievedTrue False10. Engineering cost estimates are quick and easy to developTrue FalseExtra 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 ConservatismHalo Effect Normalcy OverconfidenceRecency Search for Supportive EvidenceSelective Perception Underestimating UncertaintyB. Tetlock determined that “Expert Judgement” has no role in forecasting. True or FalseBelow 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 lineprovided. Unfortunately, this “Linear” trend line includes significant error as evidenced by the low R2value 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/feasible5-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 thecorresponding 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” years2019 thru 2022.(4) C: Through observation, provide TWO key reasons this forecast can/should be considered “flawed.”