Predictive Modeling and Business Analytics Guidelines
Creating spreadsheet models is an art that requires practice.

Below there are various great examples of things that can be done with spreadsheet. Some numbers and details have been changed so please use the information provided within this file and the files posted in Sakai.
Provide the final answer in Word and the Excel spreadsheet.

Predictive Modeling and Business Analytics Problems

Excerpts from Business Analytics by J. Evans 2nd Edition. While creating the models for each of the problems, make sure to follow the guidelines for spreadsheet quality.
Linear Optimization
1. Valencia Products makes automobile radar detectors and assembles two models: LaserStop and SpeedBuster. The firm can sell all it produces. Both models use the same electronic components. Two of these can be obtained only from a single supplier. For the next month, the supply of these is limited to 4,000 of component A and 3,500 of component B. The number of each component required for each product and the profit per unit are given in the table.
Components Required/Unit
A
B
Profit/Unit
LaserStop
18
6
$24
SpeedBuster
12
10
$40
a) Identify the decision variables, objective function, and constraints in simple verbal statements.
b) Mathematically formulate a linear optimization model.
c) Implement the linear optimization model that you developed in Excel and use Solver to find an optimal solution. Interpret the Solver Answer report and identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constrains.
2. Burger Office Equipment produces two types of desks, standard and deluxe. Deluxe desks have oak tops and more-expensive hardware and require additional time for finishing and polishing. Standard desks require 70 board feet of pine and 10 hours of labor, whereas deluxe desks require 60 board feet of pine, 18 square feet of oak, and 15 hours of labor. For the next week, the company has 5,000 board feet of pine, 750 square feet of oak, and 400 hours of labor available. Standard desks net a profit of $225, and deluxe desks net a profit of $320. All desks can be sold to national chains such as Staples or Office Depot.
a) Identify the decision variables, objective function, and constraints in simple verbal statements.
b) Mathematically formulate a linear optimization model.
c) Implement the linear optimization model that you developed in Excel and use Solver to find an optimal solution. Interpret the Solver Answer report and identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constrains.
3. Using the Banking Data file, do the following:
a. Use XLMiner to generate a simple random sample of 10 records from the Excel file Banking Data.
b. Construct a boxplot for the Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance. What observations can you make about these data?
c. Construct a parallel coordinates chart for Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance. What conclusions can you reach?
d. Construct a scatterplot matrix for Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance. What conclusions can you reach?
e. Construct a variable plot for all the variables.
Data Mining
4. Apply cluster analysis to the numerical data in the Excel file Credit Approval Decisions. Analyze the clusters and determine if cluster analysis would be a useful classification method for approving or rejecting loan applications.
OPRE 605: Business Analytics
Predictive Modeling and Analytics

Predictive Modeling and Business Analytics Guidelines

Creating spreadsheet models is an art that requires practice. Below there are various great examples of things that can be done with spreadsheet. Some numbers and details have been changed so please use the information provided within this file and the files posted in Sakai.
Provide the final answer in Word and the Excel spreadsheet.
Problems
Excerpts from Business Analytics by J. Evans 2nd Edition. While creating the models for each of the problems, make sure to follow the guidelines for spreadsheet quality.
Linear Optimization
1. Valencia Products makes automobile radar detectors and assembles two models: LaserStop and SpeedBuster. The firm can sell all it produces. Both models use the same electronic components. Two of these can be obtained only from a single supplier. For the next month, the supply of these is limited to 4,000 of component A and 3,500 of component B. The number of each component required for each product and the profit per unit are given in the table.
Components Required/Unit
A
B
Profit/Unit
LaserStop
18
6
$24
SpeedBuster
12
10
$40
a) Identify the decision variables, objective function, and constraints in simple verbal statements.
b) Mathematically formulate a linear optimization model.
c) Implement the linear optimization model that you developed in Excel and use Solver to find an optimal solution. Interpret the Solver Answer report and identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constrains.
2. Burger Office Equipment produces two types of desks, standard and deluxe. Deluxe desks have oak tops and more-expensive hardware and require additional time for finishing and polishing. Standard desks require 70 board feet of pine and 10 hours of labor, whereas deluxe desks require 60 board feet of pine, 18 square feet of oak, and 15 hours of labor. For the next week, the company has 5,000 board feet of pine, 750 square feet of oak, and 400 hours of labor available. Standard desks net a profit of $225, and deluxe desks net a profit of $320. All desks can be sold to national chains such as Staples or Office Depot.
a) Identify the decision variables, objective function, and constraints in simple verbal statements.
b) Mathematically formulate a linear optimization model.
c) Implement the linear optimization model that you developed in Excel and use Solver to find an optimal solution. Interpret the Solver Answer report and identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constrains.

Predictive Modeling and Business Analytics and Banking Data Usage

3. Using the Banking Data file, do the following:
a. Use XLMiner to generate a simple random sample of 10 records from the Excel file Banking Data.
b. Construct a boxplot for the Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance. What observations can you make about these data?
c. Construct a parallel coordinates chart for Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance. What conclusions can you reach?
d. Construct a scatterplot matrix for Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance. What conclusions can you reach?
e. Construct a variable plot for all the variables.
Data Mining
4. Apply cluster analysis to the numerical data in the Excel file Credit Approval Decisions. Analyze the clusters and determine if cluster analysis would be a useful classification method for approving or rejecting loan applications.