ICT761 Week 10 Tutorial

.docx

School

Western Sydney University *

*We aren’t endorsed by this school

Course

700

Subject

Industrial Engineering

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by JusticeRabbitPerson841

ICT761 Tutorial Week 10 ICT103: Tutorial 1 Compiled by: Shaleeza Sohail W EEK 10 1. Profit Model for Electronics Company. Cox Electric makes electronic components and has estimated the following for a new design of one of its products: Fixed cost = $10,000 Material cost per unit = $0.15 Labor cost per unit = $0.10 Revenue per unit = $0.65 These data are given in the file coxelectric . Note that fixed cost is incurred regardless of the amount produced. Per-unit material and labor cost together make up the variable cost per unit. Assuming that Cox Electric sells all that it produces, profit is calculated by subtracting the fixed cost and total variable cost from total revenue. a. Build an influence diagram that illustrates how to calculate profit. b. Using mathematical notation similar to that used for Nowlin Plastics, give a mathematical model for calculating profit. c. Implement your model from part (b) in Excel using the principles of good spreadsheet design. d. If Cox Electric makes 12,000 units of the new product, what is the resulting profit? 2. Breakeven Volume. Use the spreadsheet model constructed to answer Problem 1 to answer this problem. a. Construct a one-way data table with production volume as the column input and profit as the output. Breakeven occurs when profit goes from a negative to a positive value; that is, breakeven is when total revenue = total cost, yielding a profit of zero. Vary production volume from 0 to 100,000 in increments of 10,000. In which interval of production volume does breakeven occur? b. Use Goal Seek to find the exact breakeven point. Assign Set cell: equal to the location of profit, To value: = 0, and By changing cell: equal to the location of the production volume in your model. 3. Retirement Planning. Lindsay is 25 years old and has a new job in web development. Lindsay wants to make sure that they are financially sound in 30 years. So, Lindsay plans to invest the same amount into a retirement account at the end of every year for the next 30 years. Note that because Lindsay invests at the end of the year, there is no interest earned on the contribution for the year in which Lindsay contributes. a. Construct a spreadsheet model that will calculate Lindsay’s retirement savings given a fixed annual amount saved and a fixed annual interest rate of return. b. Develop a two-way table for annual investment amounts of $5,000 to $20,000 in increments of $1,000 and for returns of 0% to 12% in increments of 1%. 4. Transportation Costs. Refer to Problem 24. Floyd’s Bumpers pays a transportation company to ship its product in full truckloads to its customers. Therefore, the cost for shipping is a function of the distance traveled and a fuel surcharge (also on a per-mile basis). The cost per mile is $2.42, and the fuel surcharge is $0.56 per mile. The file floydsmay contains data for shipments for the month of May (each record is simply the customer zip code for a given truckload shipment) as well as the distance table from the distribution centers to each customer. Use the MATCH and INDEX functions to retrieve the distance traveled for each shipment, and calculate the charge for each shipment. What is the total amount that Floyd’s Bumpers spends on these May shipments? Hint: The INDEX function may be used with a two-dimensional array: =INDEX( array , row_num , column _ num ), where array is a matrix, row_num is the row number, and column_num is the column position of the desired element of the matrix.

ICT761 Tutorial Week 10 ICT103: Tutorial 1 Compiled by: Shaleeza Sohail 5. Discount Price versus 0-Percent Financing. An auto dealership is advertising that a new car with a sticker price of $35,208 is on sale for $25,995 if payment is made in full, or it can be financed at 0% interest for 72 months with a monthly payment of $489. Note that 72 payments × $489 per payment = $35,208, which is the sticker price of the car. By allowing you to pay for the car in a series of payments (starting one month from now) rather than $25,995 now, the dealer is effectively loaning you $25,995. If you choose the 0% financing option, what is the effective interest rate that the auto dealership is earning on your loan? Hint: Discount the payments back to current dollars [see Problem 22 for a discussion of discounting], and use Goal Seek to find the discount rate that makes the net present value of the payments = $25,995.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help