QBA Spring 2020 - Misc. Assignment #1 Break Even Problems Answer the following break even problems. In addition to the answers (rounded to 0 decimal places), show the spreadsheets you created to solve them like you do for homeworks in lab). Next to any cells with equations, put the equation in text. For example, if I was doing a spreadsheet to sum the number of fish my son and I caught, it would look like: 3 1 Fish Prof Miller Caught 2 Fish Prof Miller Son Caught 3 Total Fish Caught 10 Formula in cell B3 is =SUM(B1:B2) Q1 (5 points) Stew's Plastics produces a variety of CD cases. The best-selling product is the CD-50. Several products are produced on the same manufacturing line, so there is a setup cost each time a changeover is made for a new product. The setup cost for the CD-50 is $4200. In addition, it costs $1.81 for each unit (CD Case) produced, and for each 100 CD Cases they have to put them in a box that costs $2.26. If there is less than 100 CD Cases they will put them in a box (in other words, if they had 102 CD Cases, they would do 2 boxes, one with 100 CD Cases and one with 2 CD Cases) What is the break-even point (in terms of number of CD cases) if they sell them for $6.95 each? Q2 (5 points) Jerry, the manager of a small printing company, needs to replace a worn out copy machine. He is considering two machines; each has a monthly lease cost and a cost per page that is copied: Machine 1 has a $395 monthly lease with a 2.3 cents per page cost up to 250 pages, and then 1.7 cents per page after the 1st 250 pages. Machine 2 has a $525 monthly lease with a 1.8 cents per page cost up to 250 pages, and then 1.1 cents per page after the 1st 250 pages. Jerry knows the break-even point is more than 250 pages for each machine. Determine the break-even point (per month) in terms of the number of copies for each machine if Jerry charges customers 5.0 cents per copy. Based on this, which machine do you recommend? Solver Q3 (5 points) A computer plant produces mice, keyboards and flash drives. A mouse has a profit of $7.28/unit, and to produce it takes 0.21 hours of labor and. 10 hours of machine time. A keyboard has a profit of $12.95/unit, and to produce it takes 0.35 hours of labor and .18 hours of machine time. A flash drive has a profit of $2.00/unit, and to produce it takes 0.13 hours of labor and .03 hours of machine time. Each month, a total of 7,200 hours of labor and 2,850 hours of machine time are available. The maximum monthly demand is 12,000 mice, 26,500 keyboards and 14,750 flash drives. How many of each should the company make each month to maximize its profit and what is that maximum profit? Include your spreadsheet with any formulas noted. Also include a list of the constraints you used. (Question 4 on next page) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Q4 (10 points) The Minnow Bucket Bait Shop in Land O' lakes, WI raises trout, their current inventory is 15,000 fish (trout). They want to buy a mix of feed for their trout that both provides the nutrients needed and comes at the lowest cost. In order to do this, they are going to buy and mix two brands of feed. Each pound of Brand-1 feed contains 4.09 ounces of ingredient "A", 3.95 ounces of ingredient "B" and 0.52 ounces of ingredient "C". Each pound of Brand-2 contains 8.46 ounces of ingredient "A", 4.10 ounces of ingredient "B" and no (zero) ounces of ingredient "C". Brand-1 costs $0.75 per pound and Brand-2 costs $0.88 per pound. Each trout needs 75.0 ounces of ingredient "A", 58.0 ounces of ingredient "B" and 6.0 ounces of ingredient "C" per month. How many total pounds of each brand should they buy per month to keep their cost as low as possible and still meet the dietary requirements of the trout? What will the cost per month be? Include your spreadsheet with any formulas noted. Also include a list of the constraints you used. QBA Spring 2020 - Misc. Assignment #1 Break Even Problems Answer the following break even problems. In addition to the answers (rounded to 0 decimal places), show the spreadsheets you created to solve them like you do for homeworks in lab). Next to any cells with equations, put the equation in text. For example, if I was doing a spreadsheet to sum the number of fish my son and I caught, it would look like: 3 1 Fish Prof Miller Caught 2 Fish Prof Miller Son Caught 3 Total Fish Caught 10 Formula in cell B3 is =SUM(B1:B2) Q1 (5 points) Stew's Plastics produces a variety of CD cases. The best-selling product is the CD-50. Several products are produced on the same manufacturing line, so there is a setup cost each time a changeover is made for a new product. The setup cost for the CD-50 is $4200. In addition, it costs $1.81 for each unit (CD Case) produced, and for each 100 CD Cases they have to put them in a box that costs $2.26. If there is less than 100 CD Cases they will put them in a box (in other words, if they had 102 CD Cases, they would do 2 boxes, one with 100 CD Cases and one with 2 CD Cases) What is the break-even point (in terms of number of CD cases) if they sell them for $6.95 each? Q2 (5 points) Jerry, the manager of a small printing company, needs to replace a worn out copy machine. He is considering two machines; each has a monthly lease cost and a cost per page that is copied: Machine 1 has a $395 monthly lease with a 2.3 cents per page cost up to 250 pages, and then 1.7 cents per page after the 1st 250 pages. Machine 2 has a $525 monthly lease with a 1.8 cents per page cost up to 250 pages, and then 1.1 cents per page after the 1st 250 pages. Jerry knows the break-even point is more than 250 pages for each machine. Determine the break-even point (per month) in terms of the number of copies for each machine if Jerry charges customers 5.0 cents per copy. Based on this, which machine do you recommend? Solver Q3 (5 points) A computer plant produces mice, keyboards and flash drives. A mouse has a profit of $7.28/unit, and to produce it takes 0.21 hours of labor and. 10 hours of machine time. A keyboard has a profit of $12.95/unit, and to produce it takes 0.35 hours of labor and .18 hours of machine time. A flash drive has a profit of $2.00/unit, and to produce it takes 0.13 hours of labor and .03 hours of machine time. Each month, a total of 7,200 hours of labor and 2,850 hours of machine time are available. The maximum monthly demand is 12,000 mice, 26,500 keyboards and 14,750 flash drives. How many of each should the company make each month to maximize its profit and what is that maximum profit? Include your spreadsheet with any formulas noted. Also include a list of the constraints you used. (Question 4 on next page) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Q4 (10 points) The Minnow Bucket Bait Shop in Land O' lakes, WI raises trout, their current inventory is 15,000 fish (trout). They want to buy a mix of feed for their trout that both provides the nutrients needed and comes at the lowest cost. In order to do this, they are going to buy and mix two brands of feed. Each pound of Brand-1 feed contains 4.09 ounces of ingredient "A", 3.95 ounces of ingredient "B" and 0.52 ounces of ingredient "C". Each pound of Brand-2 contains 8.46 ounces of ingredient "A", 4.10 ounces of ingredient "B" and no (zero) ounces of ingredient "C". Brand-1 costs $0.75 per pound and Brand-2 costs $0.88 per pound. Each trout needs 75.0 ounces of ingredient "A", 58.0 ounces of ingredient "B" and 6.0 ounces of ingredient "C" per month. How many total pounds of each brand should they buy per month to keep their cost as low as possible and still meet the dietary requirements of the trout? What will the cost per month be? Include your spreadsheet with any formulas noted. Also include a list of the constraints you used