ALLUB_HW_BS

xlsx

School

Houston Baptist University *

*We aren’t endorsed by this school

Course

4340

Subject

Industrial Engineering

Date

Dec 6, 2023

Type

xlsx

Pages

Uploaded by lululas1

PROBLEM 1 Cox Electric makes electronic components and has estimated the following for a new design of one of its products: Fixed Costs $10,000.00 Material Cost per Unit $0.15 Labor Cost per Unit $0.10 Revenue per Unit $0.65 (a) Build mathematical model for calculating profit and find the resulting profit if Cox Electric makes 12,000 units of a new product. Cox Electric Breakeven Analysis Parameters Revenue per Unit $7,800.00 Fixed Costs $10,000.00 Material Cost per Unit $1,800.00 prod prof ($5,200.00) Labor Cost per Unit $1,200.00 0 10,000 20,000 Model 30,000 40,000 Production Volume 12,000 50,000 60,000 Total Revenue $7,800.00 70,000 80,000 Material Cost $1,800.00 90,000 Labor Cost $1,200.00 100,000 Fixed Cost $10,000.00 Total Cost $13,000.00 Profit / Loss ($5,200.00) (b) Construct one-way data table with production volumn as the column input and profit as the output. Vary production from 0 to 100,0 b- the break even would occur at the interval of 30.000 in increments of 10,000. In which interval of production volume does breakeven occur? the location of the production volume in the model. Model Production Volume 25,000 Breakeven volume Total Revenue $195,000,000.00 Material Cost $300,000,000.00 Labor Cost $0.00 Fixed Cost $0.00 Total Cost $300,000,000.00 Profit / Loss ($105,000,000.00) (c) Use Goal Seek to find exact breakeven point. Assign Set cell: equal to the location of profit, To value: = 0, and By changing cell: equal to

PROBLEM 2 Anna operates a consignment shop where she sells clothes for women and children. The average number of consignments sold per month is 1,000. The average material cost and the selling price of each consignment are $8 and $20, respectively. The monthly fixed costs to run the business are given below: Rental cost $750 Utilities $150 Advertising $35 Insurance $100 Labor cost $4,000 Use a two-way data table to show profit changes as a function of different number of consignments sold per month and different material costs. Vary the number of consignments from 400 to 1,200 in increments of 100. The eight different material costs are $5.45 $6.23 $6.95 $7.54 $8.23 $8.88 $9.00 $9.45 Parameters Two-Way Table Material cost $5.45 Selling price $20.00 Number of consignments 400 Number of Consignments Sold material cost Fixed cost $5,035 $785 400 500 600 700 800 900 1000 1100 1200 Rental cost $750 $5.45 Utilities $150 $6.23 Advertising $35 $6.95 Insurance $100 $7.54 Labor cost $4,000 $8.23 $8.88 Model $9.00 $9.45 Total selling price 8000 Total fixed cost $5,035 Total material cost $2,180 Total cost $7,215 Profit $785

PROBLEM 3 Richardson Ski Racing (RSR) sells equipment needed for downhill ski racing. One of RSR's products ins fencing used on downhill courses. The fence product comes in 150-foot rolls and sells for $215 per roll. However, RSR offers quantity discounts. The following table shows the price per roll depending on order size: Quantity Ordered From To Price per Roll 1 50 $215 51 100 $195 101 200 $175 201 and up $155 The following list contains 172 orders that have arrived for the coming six weeks. (a) Use the VLOOKUP function with the price table above to determine the total revenue from these orders. (b) Use the COUNTIF function to determine the number of orders in each price bin. Order Quantity Price per Roll Revenue 1 86 $195 $16,770 Quantity Ordered 2 452 $155 $70,060 From To Price per Roll 3 492 $155 $76,260 1 50 $215 4 191 $175 $33,425 51 100 $195 5 356 $155 $55,180 101 200 $175 6 148 $175 $25,900 201 and up $155 7 342 $155 $53,010 8 382 $155 $59,210 9 276 $155 $42,780 10 118 $175 $20,650 Total Revenue 11 464 $155 $71,920 12 188 $175 $32,900 13 25 $215 $5,375 From To Price per Roll # Orders % of Orders 14 427 $155 $66,185 1 50 $215 25 14.7% 15 30 $215 $6,450 51 100 $195 29 17.1% 16 111 $175 $19,425 101 200 $175 67 39.4% 17 161 $175 $28,175 201 and up $155 49 28.8% 18 314 $155 $48,670 Total 170 100.0% 19 442 $155 $68,510 20 429 $155 $66,495 21 451 $155 $69,905 Note: Use COUNTIF formula in cells I33 - I36. 22 32 $215 $6,880 Note: Use VLOOKUP formula in cells in column C. 23 181 $175 $31,675 Note: Calculate Revenue based on Quantity and Price per Roll. 24 163 $175 $28,525 25 336 $155 $52,080 26 240 $155 $37,200 27 68 $195 $13,260 28 317 $155 $49,135 29 435 $155 $67,425 30 211 $155 $32,705 31 76 $195 $14,820 32 374 $155 $57,970 33 415 $155 $64,325 34 408 $155 $63,240 35 196 $175 $34,300 36 302 $155 $46,810 37 312 $155 $48,360 38 31 $215 $6,665 39 317 $155 $49,135 40 394 $155 $61,070 41 348 $155 $53,940 42 49 $215 $10,535 43 500 $155 $77,500 44 490 $155 $75,950 45 94 $195 $18,330 46 285 $155 $44,175 47 237 $155 $36,735

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

Order Quantity Price per Roll Revenue 48 101 $175 $17,675 49 53 $195 $10,335 50 257 $155 $39,835 51 273 $155 $42,315 52 415 $155 $64,325 53 346 $155 $53,630 54 133 $175 $23,275 55 287 $155 $44,485 56 431 $155 $66,805 57 113 $175 $19,775 58 197 $175 $34,475 59 6 $215 $1,290 60 328 $155 $50,840 61 114 $175 $19,950 62 207 $155 $32,085 63 273 $155 $42,315 64 257 $155 $39,835 65 113 $175 $19,775 66 400 $155 $62,000 67 324 $155 $50,220 68 500 $155 $77,500 69 60 $195 $11,700 70 117 $175 $20,475 71 466 $155 $72,230 72 370 $155 $57,350 73 293 $155 $45,415 74 164 $175 $28,700 75 63 $195 $12,285 76 252 $155 $39,060 77 394 $155 $61,070 78 109 $175 $19,075 79 233 $155 $36,115 80 340 $155 $52,700 81 430 $155 $66,650 82 210 $155 $32,550 83 363 $155 $56,265 84 120 $175 $21,000 85 429 $155 $66,495 86 113 $175 $19,775 87 391 $155 $60,605 88 375 $155 $58,125 89 487 $155 $75,485 90 11 $215 $2,365 91 264 $155 $40,920 92 14 $215 $3,010 93 177 $175 $30,975 94 193 $175 $33,775 95 427 $155 $66,185 96 320 $155 $49,600 97 347 $155 $53,785 98 64 $195 $12,480 99 430 $155 $66,650 100 282 $155 $43,710 101 66 $195 $12,870 102 457 $155 $70,835 103 377 $155 $58,435 104 15 $215 $3,225 105 333 $155 $51,615 106 23 $215 $4,945 107 230 $155 $35,650 108 378 $155 $58,590 109 136 $175 $23,800 110 250 $155 $38,750 111 377 $155 $58,435

Order Quantity Price per Roll Revenue 112 388 $155 $60,140 113 256 $155 $39,680 114 337 $155 $52,235 115 419 $155 $64,945 116 171 $175 $29,925 117 278 $155 $43,090 118 184 $175 $32,200 119 357 $155 $55,335 120 368 $155 $57,040 121 409 $155 $63,395 122 100 $195 $19,500 123 154 $175 $26,950 124 160 $175 $28,000 125 239 $155 $37,045 126 186 $175 $32,550 127 383 $155 $59,365 128 156 $175 $27,300 129 217 $155 $33,635 130 452 $155 $70,060 131 111 $175 $19,425 132 208 $155 $32,240 133 317 $155 $49,135 134 60 $195 $11,700 135 185 $175 $32,375 136 383 $155 $59,365 137 52 $195 $10,140 138 282 $155 $43,710 139 188 $175 $32,900 140 52 $195 $10,140 141 106 $175 $18,550 142 346 $155 $53,630 143 277 $155 $42,935 144 484 $155 $75,020 145 208 $155 $32,240 146 229 $155 $35,495 147 311 $155 $48,205 148 450 $155 $69,750 149 304 $155 $47,120 150 379 $155 $58,745 151 15 $215 $3,225 152 99 $195 $19,305 153 324 $155 $50,220 154 151 $175 $26,425 155 179 $175 $31,325 156 460 $155 $71,300 157 441 $155 $68,355 158 309 $155 $47,895 159 37 $215 $7,955 160 305 $155 $47,275 161 348 $155 $53,940 162 85 $195 $16,575 163 135 $175 $23,625 164 288 $155 $44,640 165 454 $155 $70,370 166 238 $155 $36,890 167 38 $215 $8,170 168 451 $155 $69,905 169 374 $155 $57,970 170 437 $155 $67,735 171 184 $175 $32,200 172 455 $155 $70,525

PROBLEM 4 Williamson produces a single product and has plants in Atlanta, Lexington, Chicago, and Salt Lake City and warehouses in Portland, St. Paul, Las Vegas, Tucson, and Cleveland. Each plant has a capacity and each warehouse has a demand. Williamson would like to find a low-cost shipping plan. Mr. Williamson has reviewed the results and notices right away Correct the errors (Hint: The model contains two errors. Be sure to check every formula.) Williamson Manufacturing Transportation Model Data Shipping Cost Per Unit Portland St. Paul Las Vegas Tucson Cleveland Supply Atlanta $2.17 $0.92 $1.75 $1.50 $0.56 10,000 Lexington $1.97 $0.61 $1.68 $1.53 $0.22 10,000 Chicago $1.71 $0.37 $1.53 $1.41 $0.34 15,000 Salt Lake City $0.63 $0.99 $0.35 $0.58 $1.57 8,000 Demand 5,000 15,600 5,000 3,750 4,570 Model Shipping Cost $20,086 Units Shipped Portland St. Paul Las Vegas Tucson Cleveland Units Shipped Atlanta 0 0 0 3,750 0 3,750 Lexington 0 2,600 0 0 4,570 7,170 Chicago 2,000 13,000 0 0 0 15,000 Salt Lake City 3,000 0 5,000 0 0 8,000 Units Received 5,000 15,600 5,000 3,750 4,570 33,920 that the total cost is way out of line. Use the Formula Auditing tool under the Formulas tab to find any erros in this model

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

PROBLEM 5 Newton Manufacturing produces scientific calculators. The models are N350, N450, and N900. Newton has planned its distribution of these products around eight customer zones: Brazil, China, France, Malaysia, US Northeast, US Southeast, US Midwest, and US West. Data for the current quarter (volume to be shipped in thousands of units) for each product and each customer zone are given below. Newton would like to know the total number of units going to each customer zone and also the total units of each product shipped. There are several ways to get this information from the data set. One way is to use the SUMIF function, which extends the SUM function by allowing the user to add the values of cells meeting a logical condition. The general form of the function is For example, Malaysia would be =SUMIF($A$15:$A$38,A17,$C$15:$C$38) (a) Use the SUMIF function to get total volume by zone (b) Use the SUMIF function to get total volume by each product Newton Scientific Calculators Customer Zone Model Volume (000 units) Malaysia N350 399.0 China N350 3,158.3 Total Volume (000) France N350 1,406.0 Malaysia 1,928.6 Brazil N350 163.5 China 5,328.5 US Northeast N350 68.7 France 3,486.6 US Southeast N350 999.7 Brazil 1,144.7 US Midwest N350 544.9 US Northeast 430.2 US West N350 1,804.0 US Southeast 3,203.3 Malaysia N450 228.0 US Midwest 4,482.7 China N450 540.9 US West 5,969.9 France N450 289.8 Total 25,974.5 Brazil N450 240.5 US Northeast N450 313.2 US Southeast N450 681.3 Total Volume (000) US Midwest N450 1,720.3 N350 8,544.1 US West N450 2,922.3 N450 6,936.3 Malaysia N900 1,301.6 N900 10,494.1 China N900 1,629.3 Total 25,974.5 France N900 1,790.8 Brazil N900 740.7 US Northeast N900 48.3 US Southeast N900 1,522.3 US Midwest N900 2,217.5 US West N900 1,243.6 =SUMIF( test range, condition, range to be summed )

ALLUB_HW_BS

Related Documents