Blog

Estimating a Probability Distribution from Frequency DataUsing the same 25-day stove-sales data: Stoves Sold Frequency 2 4 3 7 4 8 5 5 6 1 What probability should Excel calculate for selling exactly 4 stoves in a day?Source: Adapted from stove-sales simulation problem, Chapter 14.

Interpreting Decreasing Marginal ReturnsA spreadsheet model estimates revenue with:where x is the number of units produced. What does the negative squared term imply?Source: Adapted from the textbook’s Chapter 11 discussion of nonproportional relationships.

Loan Screening Classification SetupA bank has historical loan data with the following variables: annual income, years employed, monthly debt payments, credit score, and whether the borrower defaulted. The bank wants to use Excel-based predictive analytics to classify a new applicant as likely to default or not likely to default. Which setup is most appropriate?Source: Adapted from the First Bank loan-application case in Chapter 2.

Expected Value of Perfect InformationUse the same decision problem: Alternative Oil Found Dry Land Drill $700,000 -$100,000 Sell $90,000 $90,000 State of Nature Probability Oil Found 0.25 Dry Land 0.75 Without perfect information, the best expected payoff is $100,000. With perfect information, the company would drill if oil is found and sell if the land is dry. Use Excel to compute the expected value of perfect information.Source: Adapted from the Goferbroke Company decision analysis example, Chapter 12.

Forecasting with a Moving AverageA department store recorded stove sales for five months: Month Stove Sales 1 15 2 18 3 12 4 17 5 13 Use Excel to compute a 3-month moving average forecast for Month 6. What is the forecast?Source: Adapted from Good-Value Department Store forecasting problem, Chapter 4.

Recognizing a Quadratic Programming ModelA spreadsheet model has this objective function:where D and W are changing cells. The constraints are linear. What type of optimization model is this?Source: Adapted from the nonlinear Wyndor Glass extension in Chapter 11.

Binding Constraint InterpretationA product-mix spreadsheet model has a labor availability constraint of 500 hours. At the optimal solution, the spreadsheet calculates labor used as 500 hours. How should this constraint be described?Source: Adapted from the textbook’s Chapter 5 discussion of constraints and slack.

Forecasting with the Averaging MethodUsing the same sales data: Month Sales 1 5 2 17 3 29 4 41 5 39 Use Excel’s AVERAGE function to forecast Month 6 using the averaging method. What is the forecast?Source: Adapted from Hammaker Company forecasting problem, Chapter 4.

Linear Programming Product Mix with SolverA company makes doors and windows. Each door earns $300 profit, and each window earns $500 profit.Resource usage and weekly availability are: Plant Door Hours Window Hours Available Hours Plant 1 1 0 4 Plant 2 0 2 12 Plant 3 3 2 18 Let D = number of doors and W = number of windows. Use Excel Solver to maximize:300D+500Wsubject to the plant-hour constraints and nonnegativity. What is the optimal solution?Source: Adapted from the Wyndor Glass product-mix example, Chapter 5.

Linear Programming Objective FunctionThe same window maker earns $60 profit for each wood-framed window and $30 profit for each aluminum-framed window.Let:W=number of wood-framed windowsA=number of aluminum-framed windowsWhich objective should be entered in Excel Solver?Source: Adapted from Whitt Window Company problem, Chapter 5.