Reference no: EM131112816

Skill Building Exercises

1.) Develop a one-way data table for the Airline Pricing model for Airplane capacities ranging from 200 to 360 in increments of 20.

2.) Develop a two-way data table for the Airline Pricing model for Airplane capacities ranging from 200 to 360 in increments of 20, and fixed costs ranging from $60,000 to $120,000 in increments of $10,000.

Note: Please provide detailed step by step instructions how to create this data table in Excel.

Skill Building Exercises

1.) Open the New Store Financial Model Spreadsheet and setup and run the Crystal Ball simulation as described in the chapter. Find the probabilities of a positive cumulative discounted cash flow for each of years 1 through 5. Create a spreadsheet summary of the simulation statistics and percentiles for each year.

2.) Open the New Store Financial Model Spreadsheet and setup and run the Crystal Ball simulation using Crystal Ball functions instead of defining assumptions in the model.

Note: Please provide detailed step by step instructions how to solve these problems using Excel.

Using Crystal Ball:

Crystal Ball: Excel add-in that performs Monte Carlo simulation for risk analysis. To use Crystal Ball:

1.) Develop the spreadsheet model.

2.) Define assumptions for uncertain variables, that is the probability distributions that describe the uncertainty.

3.) Define the forecast cells, that is, the output variables of interest.

4.) Set the number of trials and other run preferences.

5.) Run the simulation.

6.) Interpret the results.

Problems and Applications
1.) A supermarket has been experiencing long lines during peak periods of the day. The problem is noticeably worse on certain days of the week, and the peak periods are sometimes different according to the day of the week. There are usually enough workers on the job to open all cash registers. The problem is knowing when to call some of the workers stocking shelves up to the front to work the checkout counters. How might decision models help the supermarket? What data would be needed to develop these models?

2.) Four key marketing decision variables are price (P), advertising (A), transportation (T) and product quality (Q). Consumer demand (D) is influenced by these variables. The simplest model for describing demand in terms of these variables is:

D = k - pP + aA +tT +qQ

Where k, p, a, t, and q are constants. Discuss the assumptions of this model. Specifically how does each variable affect demand? How do the variables influence each other? What limitations might this model have? How can it be improved?

3.) Total marketing effort is a term used to describe the critical decision factorsthat affect demand: price, advertising, distribution, and product quality. Define the variable x to represent total marketing effort. A typical model that is used to predict demand as a function of total marketing effort is based on the power function:

D = axb

Suppose that a is a positive number. Different model forms result from varying the constant b. Sketch the graphs of this model for b = 0, b = 1, 0 < b < 1, b < 0, and b > 1. (We encourage you to use Excel to do this.) What does each model tell you about the relationship between demand and marketing effort? What assumptions are implied? Are they reasonable? How would you go about selecting the appropriate model?

Note: Please provide detailed step by step instructions how to create this data in Excel.

Chapter 10 Case Study

After reading Chapter 10 in the Evans text, please read the "Bellin" Case on page 373 of your text and answer the following questions:

CASE

THE BELLIN PROJECT- Page 373 is below:

Jennifer Bellin has been put in charge of planning her company's annual leadership conference. The dates of the conference have been determined by her company's executive team. The Excel file chapter 10 "Problem Data" contains information about the activities, predecessors, and activity times in the worksheet Bellin Case. Assume a BetaPERT distribution for the activity times. How far in advance of the conference must Jennifer begin the project activities to ensure a 95% chance of completing the project by the scheduled date? How sensitive are the results to the distributional assumption? For example, suppose a triangular or normal distribution was used to model the activities times. How do the results change? Are the differences significant?

Using the tools in Chapter 10, address this case study. Answer the following questions in your write-up of 3-5 pages:

•How far in advance of the conference must Jennifer begin the project activities to ensure a 95% chance of completing the project by the scheduled date?
•How sensitive are the results to the distributional assumptions?
•For example, suppose a triangular or normal distribution was used to model the activity times. How do the results change?
•Are the differences significant?

•Please complete both an Excel file and written report (MS Word file)

Note: Please provide detailed step by step instructions how to solve the problem using Excel.

Attachment:- problems.rar