Assignment:
Decision Support Systems allow us to analyze complex relationships and to examine what if situations. Excel is an excellent tool for performing this type of analysis. In this assignment you will use Excel to solve basic quantitative analysis problems based in Linear Programming, Network Analysis, and Work Assignment.
Instructions:
Using the techniques, develop decision models using Microsoft Excel’s add-in tools (i.e. Solver and Goal Seeking) for each of the problems below. Your models should show include the following decision making elements:
• Decision Variables
• Objective Function
• Constraints
Run your models to produce an outcome using the information provided for each problem. For each model, you will need to save the Solver output sheet as part of Excel workbook. Label the tabs/worksheets for each problem in the Excel file you turn in (i.e. 3.3.1 Model, 3.3.1 Solver, etc.). Be sure to save the file so that the decision data and Solver result data remain.
Basic Problem Solving:
Problem 1. Consider the following linear programming problem.
Maximize: 4 A + 7 B
Subject to:
A + 3 B ≤ 100
5 A + 2 B ≤ 150
2 A + 2 B ≤ 90
Problem 2. Consider the following linear programming problem.
Minimize: 1 M + 2 N
Subject to:
2M + 6 N ≥ 180
8 M + 2 N ≥ 160
3 M + 2 N ≥ 120
N ≤ 70
Managerial Decision Support – Linear Programming
The advertising director for Diversity Pant and Supply, a chain of four retail stores on Chicago’s North Side, is considering two media possibilities. One plan is for a series of half-page ads in the Sunday Chicago Tribune newspaper and the other is for advertising time on Chicago TV. The stores are expanding their lines of do-it-your-self tools, and the advertising director is interested in an exposure level of at least 40% within the city’s neighborhoods and 60% in the northwest suburban areas.
The TV viewing time under consideration has an exposure rating of 5% in city homes and 3% in the northwest suburbs. The Sunday newspaper has corresponding exposure rates of 4% and 3% per ad. The cost of the half-page advertisement is $925: a television spot costs $2000.
Diversity Paint would like to select the least costly advertising strategy that would meet desired exposure levels.
Managerial Decision Support – Transportation/Assignment:
The Krampf Line Railway Company specializes in coal handling. On Friday, April 13, Krampf had empty carts at the following towns in the quantities indicated:
|
Town
|
Supply of Cars
|
|
Morgantown
|
35
|
|
Youngstown
|
60
|
|
Pittsburg
|
26
|
By Monday, April 16, the following towns need coal cars as follows:
|
Town
|
Demand for Cars
|
|
Coal Valley
|
30
|
|
Coaltown
|
45
|
|
Coal Junction
|
24
|
|
Coalsburg
|
20
|
Using a railway city-to-city distance chart, the dispatcher constructed a mileage table for the preceding towns. The result is as follows:
|
From/To
|
Coal Valley
|
Coaltown
|
Coal Junction
|
Coalsburg
|
|
Morgantown
|
50
|
30
|
60
|
70
|
|
Youngstown
|
20
|
80
|
10
|
90
|
|
Pittsburg
|
100
|
40
|
80
|
30
|
Using a railway city-to-city distance chart, the dispatcher constructed a mileage table for the preceding towns. The result is as follows: