1. Transportation and Production (2 points)
California Catamarans builds the Matey-20 catamaran boat in three locations: San Diego, Santa Ana, and San Jose. It ships the boats to its company-owned dealerships in Newport Beach, Ventura, and San Francisco. Production costs and capacities vary from plant to plan, as do shipping costs from the manufacturing plants to the dealerships. The following tables give cost, capacities and demands for August. Develop a production and shipping schedule for the Matey-20 catamaran for this period that minimizes the total production and shipping costs. First write down the model, then use Excel Solver to solve it.
Cost
| Plant | Production Cost per Boat | Shipping cost per boat to Newport Beach | Shipping cost per boat to Ventura | Shipping cost per boat to San Francisco |
| San Diego | $1065 | $200 | $280 | $500 |
| Santa Ana | $1005 | $125 | $280 | $400 |
| San Jose | $975 | $390 | $300 | $100 |
Plant Capacities to produce number of boats in August
| San Diego | 38 |
| Santa Ana | 45 |
| San Jose | 58 |
Demand in number of boats in August
| Newport Beach | 52 |
| Ventura | 37 |
| San Francisco | 32 |
(Hint: this is but one step further than the beef example. Here, we want minimize the combined cost of production and shipping; therefore, for example, the total cost from San Diego to Newport Beach will be 1065 + 200)
2. Jones Investment Service (2 points)
Assuming that Frank will invest all $100,000, develop a linear programming model to determine the amount to be invested in each products so that the total risk will be minimized while meeting the four portfolio goals, by a) first write out the complete model, b) then use Excel Solver to find the solution.
Hint: 1. There should be 8 DVs, X1 to X8, each representing amount to invest in each of the 8 products, respectively. 2. Objective function is total risk. Risk is calculated by amount invested on a product multiplied by its associated risk factor; for example, if $100 is invested in Atlantic Lighting, then its associated risk is 100 x 25=2500. 3. There are five constraints: four portfolio goals plus investing all 100k.
3. PivotTable and Graph (2 points)
Referring to the real-estate dataset in the lecture, answer the following questions:
- What is the average number of bathroom for houses with three bedrooms?
- What is the most common number of bathrooms for houses with three bedrooms? (This is the concept of Mode in statistics.)
- Among the colonial style houses, what is the percentage of them with both modern kitchen and modern bathrooms?
- Among houses with both modern kitchen and modern bathroom, what is the percentage of them in colonial style?
Only copy and paste as value the pivot table results for submission. Do NOT attach the raw data.
