80.2K

Verified Solution

Link Copied!

Finance for Engineers:

Setting up your file: Start with a new Excel file and answer each question on a separate sheet within your file (2 sheets for question #2). You will have 6 sheets in your file. Please make sure you use cell references wherever appropriate (everywhere possible). As in the example Excel file and Excel video files on Moodle, list the model inputs (the information provided in the question) at the top. Once you start working on developing your model below the inputs, you should NOT be typing any numbers or answers in your cells; instead all cells should include cell references and Excel functions wherever possible. DO NOT use discount factors within your Excel file; use Excel functions whenever you need to compute a future value or present value. Perform all of your calculations directly in the Excel cells; do not calculate anything outside of Excel; Excel functions you may need to use: PV, FV, PMT, RATE, NPER, NPV, and IRR. Some questions ask for a written answer, in addition to calculating an answer in Excel; please make sure you answer all questions asked; create a box below your calculations where you type in your written answer whenever required.

Note: Always assume cash flows occur at the end of the period.

Question 4

RC Sport assembles and sells low-end hockey goals, designed for youth and recreational hockey. At the moment, the company is buying all of the components required to assemble each hockey goal from an external supplier: the goal frame for $50, padding for $15, and netting for $10. The company employs part-time labor, paid $5 to assemble one goal. The annual fixed cost, consisting of insurance, property taxes, and rent is $20,000. The selling price of one goal is $125. Ignore taxes, and other possibly relevant information not given. (Note: Please include a written answer with part d.) Page 3 of 3 a) Calculate the contribution margin per unit. b) Calculate the annual number of soccer goals that RC Sport needs to sell to break even. c) Construct a table with Number of Units sold, Fixed Cost, Variable Cost, Total Cost, Total Revenue, and Profit in the columns. Fill the units sold column with the following values: 0, 100, 200, 300, 400, 500, 600, 700, 800, 900, and 1000. Fill in the remaining columns with formulas and cell references, as necessary. Insert two Excel Charts, one showing Number of Units (x) and Profit (y), the other showing Number of Units (x), Total Revenue (y1), and Total Cost (y2). Use labels in your charts and make them look good for full credit! d) RC Sport is considering producing the goal frame in its existing facility, instead of buying it from an external supplier. In order to make the goal frames, it would need to buy a new machine. The machine would be expected to last 7 years, and sold for $8,000 at the end of year 7. Its annual maintenance and operating cost would be $50,000. RC would also need to buy $30 worth of material to produce each goal frame. The padding and netting would continue to be purchased from the same external supplier for the same price, and it would still cost $5 in labor cost to assemble one goal. Assuming RC Sport is able to produce and sell 10,000 hockey goals annually, what is the maximum purchase price of the machine that would make it economical for RC Sport to make the hockey goal frames internally? Use the annual worth method, assuming 15% MARR, and the Excel Goal Seek function to find the answer. Please make sure you save your Excel file after running Goal Seek, so that I can verify your file to make sure that you did use the Goal Seek function to arrive at your answer. (Do not use the trial and error, or another method.) Provide a written statement indicating the maximum economical price of the machine. Note: You will need to make up a purchase price of the machine to start with, which you can then override with the Goal Seek function.

Question 5

Angola Superheros, Inc. is considering launching a production of a new superhero toy. The production and sales are expected to last 4 years. The project would require a new machine, with a cost of $1,000,000. The machine is expected to be sold for $300,000 at the end of the project. The company estimates that 40,000 toys would be sold annually, with a $15 contribution margin per unit (the difference between the selling price and the variable cost per unit). In addition, the company will have to pay fixed costs equal to $35,000 each year. The minimum attractive rate of return is 13%. (Note: You will need to provide a written answer for part a, and written comments discussing the results of your analysis in part b.) (Note: Do not include the selling price or the variable cost per unit in your analysis. Also, you will need to work with the total contribution to profits for all units, and fixed costs, instead of Revenues and Costs.) a) Compute the projects cash flows for years 0-4, calculate the present worth, annual worth, and the rate of return of the project, and determine whether the project should be accepted, based on the information given (disregarding any sensitivity analysis). Explain your answer. b) Conduct the following sensitivity analysis, using the projects ANNUAL WORTH. Create a table where you vary the following variables, one at a time: the contribution margin per unit, the annual number of units sold, the annual fixed cost, and the machines salvage value at the end of the project. Create a table, starting with a Percent Change in its first column, with the following values: -50%, -40%, -30%, -20%, -10%, 0, 10%, 20%, 30%, 40%, and 50%. Follow with each variable and an Annual Worth column, corresponding to each of the variables you vary. You can use the Excel example file posted under Week 7 as your guide (your tables and set up will be slightly different, but similar). Vary each of the variables ranging from -50% to 50%, one at a time, while keeping all of the other variables at their baseline values, recomputing the projects annual worth each time. Insert a chart with the percent change on the horizontal axis, and the contribution margin, number of units, fixed cost, and salvage value on the vertical axis. Comment on the sensitivity of the projects annual worth to changes in each of the four variables, and the implications for accepting or rejecting the project.

Answer & Explanation Solved by verified expert

See Answer