100%(21)21 out of 21 people found this document helpful
This preview shows page 5 - 10 out of 10 pages.
ValueCostCoefficient$B$16:$D$16$B$16Maximum Production Levels Small16157.142857020.5$C$16Maximum Production Levels Medium6200 -4.071428571434$D$16Maximum Production Levels Large2600 -3.392857142842ConstraintsFinalShadowConstraintCellNameValuePriceR.H. Side$E$11Bending/Forming Available Time2340000$E$12Welding Available Time2340000$E$13Painting Available Time46800 -14.6428571430
Microsoft Excel 16.0 Limits ReportWorksheet: [BUSN625 Week 7 Assignment.xlsx]Klein IndustriesReport Created: 7/23/2017 4:28:49 PMObjectiveCellNameValue$E$4Unit Profit Total Profit$651,221.43 VariableLowerObjectiveUpperCellNameValueLimitResultLimit$B$16:$D$16$B$16Maximum Production Levels Small16157.1428571400060700016157.142857$C$16Maximum Production Levels Medium62006200651221.436200$D$16Maximum Production Levels Large26002600651221.432600
To Whom It May Concern:After your request on how to maximize sales for air compressors, my team and I developed a scenario that I believe you wibe impressed by. After running the numbers in excel solver, based on the monthly projected sales and the timing information you provided, it seems as though monthly sales of 16,157 small, 6,200 medium and 2,600 large air compressorwould maximize profits at $651, 221.42 at the optimum level. That leaves a lot of wiggle room for other projects I must sayTake a look at the attached scenario and let me know what you think. I would be happy to discuss in more detail. Thanks.-Krystal
Based on the sensitivity report, when the shadow price is “0”, there is no change in profit if production hours increase or decrease in bending/forming as well as welding. However in the painting department, an increase or decrease in production hours will cause a 14 point change in profits respectively.