First two questions using excel solver and linear programming
FIN40401 Project 3 Due 4/24/2015 Please submit to the TA All information should be entered directly into the project 3 spreadsheet Exercise 1 Solver You own an internet firm and ship products throughout the US. Your clients understand that it will take 10-14 days for them to receive any item that they purchase through your website. You have negotiated a flat rate shipping rate. However, your flat rate shipping rate is determined by the day of the week that the item is shipped and what region of the country the item is shipped to. Shipping rates by day of the week and region are listed below. While your shipping department is extremely efficient it can only ship 1,500 orders per day. All orders must ship within seven days of receipt. Shipping costs -in dollars Region East Southea st Midwest Southwe st West Units Ordered 1200 1200 Monday Tuesday Thursda y 7 7 Friday Saturday 6 5 Wednesd ay 4 4 5 5 8 7 9 8 2000 1200 4 7 5 6 3 6 7 9 8 8 7 12 1500 8 7 7 10 11 12 In addition to the shipping costs there is a labor cost per item to ship each item. The labor cost varies by the day of the week: Monday $1/unit Tuesday $1/unit Wednesday $1/unit Thursday $1/unit Friday $2/unit Saturday $2.5/unit There is also a fixed cost to operate the warehouse of $5,000 per month and a fixed cost for support staff of $6,000 per month. Note: make sure that you handle fixed and variable costs appropriately. You need to find the lowest shipping costs for the company to ship the units listed in the above schedule. Your spreadsheet must include an answer report. Exercise 2 Scenario Manager You now own a manufacturing firm and your firm makes 6 products. Each product has the following cost components: Material & Labor. In addition, there is a fixed cost for the firm. Cost of item in production units Producti on units Material Units Labor units Producti on units cost per unit Material Labor Retail Price Quantity Fixed cost: AZ BD FG HE JT KI 2 2 1 1 3 1 1 1 1 2 2 1 $6 $5 $25 $4 $4 $20 $7 $5 $18 $6 $6 $22 $7 $5 $40 $5 $5 $15 500 750 650 900 250 600 $12,000 Use scenario manager to conduct the following stress tests by determining the effect of the following scenarios upon net profit: #1 Increase retail price, material cost and labor cost by 10%. #2 Increase retail price by 5%, increase labor cost by 5% and hold material cost static. #3 Decrease retail price by 5%, increase labor cost by 5% and increase material cost by 5%. #4 Decrease retail price by 10%, decrease labor and material cost by 5% and decrease fixed cost by $2,000. #5 Increase retail price by 15%, material and labor cost by 12% and increase fixed cost by $5,000 Please make sure that all of your changing and output cells are named by using the define name function. Exercise 3 Find the following using countif, countifs, sumif, sumifs, averageif, and averageifs. Create all formulas and calculations directing in Excel. 1. How many songs are sung by Madonna? 2. What is the average length of a Cher song? 3. Out of the Eminem songs that are longer than 4 minutes, what is the average number of times the song was played by Slick Willie? 4. How many Springsteen CD's did DJ Willi play? 5. What is the average rating of the Manilow LPs that were released after 1/1/2004 played by everyone except Big Boy? 6. How many times has Wolfman played a Spears song? 7. What is the average song length of a Cher MP3 song? 8. Which singer has Disco Boy played most often? 9. Out of the songs that Nancy has played, which performer's songs have the highest average rating? 10. How many songs have been played more than 4,000 times? 11. Of the songs that have been played more than 5,000 times, what is the average rating of a Springsteen CD last longer than 3 minutes
