Excel Assignment

Q1

QUESTION 1 Conduct a Sampling experiment to compute the OUTCOME variables for each of the following scenarios using the information given.

Part a The time required to play a game of Battleship™ is uniformly distributed between 13 and 46 minutes. Based on the random numbers given below, use the uniform distribution formula

to obtain a sample of 10 outcomes and compute their mean, minimum, maximum, and standard deviation

Outcome RANDOM# Outcome low 13

1 0.27094 high 46

Mean 2 0.34858

3 0.83965

Maximum 4 0.26445

5 0.28724

Minimum 6 0.03304

7 0.61903

Standard deviation 8 0.51164

9 0.17732

10 0.98450

Part b The Outcome variable has a NORMAL distribution with mean 13 and standard deviation 4.98 Based the random numbers given beow, use the normal distribution formula

to obtain a sample of 10 outcomes and compute their mean, minimum, maximum, and standard deviation.

Outcome RANDOM# Outcome mean 13

1 0.95908 stdev 4.98

Mean 2 0.25985

3 0.09234

Maximum 4 0.60659

5 0.45326

Minimum 6 0.95475

7 0.10638

Standard deviation 8 0.56453

9 0.47262

10 0.66033

Q2

QUESTION 2 Given the DISCRETE distributions for Fixed Cost, Unit Cost and Demand for a product with probabilities shown on the tables below, use the Random numbers given for each variable to

conduct a sampling experiment to generate 10 Profit estimates assuming the company sells everything that it’s produced and that the Price of the product is set to $27.36 per unit.

Calculate the mean, minimum, maximum and standard deviation of the 10 profit estimats.

Fixed Cost Probability Unit Cost Probability Demand Probability price

$6,000 0.36 $2.00 0.21 800 0.24 27.36

$8,500 0.3 $3.30 0.38 2,000 0.38

$8,000 0.16 $3.80 0.22 3,100 0.12

$13,500 0.18 $4.50 0.19 3,800 0.26

Profit PROFIT RANDOM# Fixed Cost RANDOM# Unit Cost RANDOM# Demand

1 0.19149 1 0.70878 1 0.54601

Mean 2 0.66504 2 0.32790 2 0.29340

3 0.50635 3 0.45502 3 0.39534

Maximum 4 0.51273 4 0.72188 4 0.91342

5 0.20460 5 0.66716 5 0.02858

Minimum 6 0.60089 6 0.64452 6 0.03757

7 0.86002 7 0.75598 7 0.09717

Standard deviation 8 0.29025 8 0.45831 8 0.66814

9 0.63859 9 0.93085 9 0.41107

10 0.92505 10 0.16976 10 0.65748

Q3

QUESTION 3 A government agency is putting a large project out for low bid. Bids are expected from ten contractors and will have a normal distribution with a mean of $5.17 millions and a standard deviation of $0.31 millions

Devise and implement a sampling experiment for estimating the distribution of the minimum bid and the expected value of the minimum bid.

Mean 5.17 Bid 1 Bid 2 Bid 3 Bid 4 Bid 5 Bid 6 Bid 7 Bid 8 Bid 9 Bid 10 Winner Winning Bid

1

std deviation 0.31 2

3

Average Winning Bid 4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

Q4

QUESTION 4 A formula in financial analysis is the following: Return on equity = net profit margin × total asset turnover × equity multiplier.

Suppose that the equity multiplier is fixed at 4.0 but that the net profit margin is normally distributed with a mean of 3.0% and a standard deviation of 0.3%

and that the total asset turnover is normally distributed with a mean of 1.93 and a standard deviation of 0.25

Set up and conduct a sampling experiment to find the distribution of the return on equity. Show your results as a histogram to help explain your analysis and conclusions.

Equity Multiplier 4 Net Profit Margin Total Asset Turnover Return on Equity Insert Histogram for Return on Equity below this point

1

Net Profit Margin mean 3.00% 2

3

Net Profit Margin std dev 0.30% 4

5

Total Asset Mean 1.93 6

7

Tota Asset std dev 0.25 8

9

Average Return on Equity 10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

Q5

QUESTION 5 A chocolate manufacturing company produces two types of chocolate: A and B. Ingredients required for manufacturing the products include milk and cacao only .

Each unit of type A chocolate requires 2 units of milk and 4 units of cacao.

Each unit of type B chocolate requires 1 units of milk and 3 units of cacao.

The company’s production plant has a total of 22 units of milk and 46 units of cacao available.

On each sale the company makes a profit of $6.20 for every unit of chocolate of type A and $4.20 for every unit of type B.

Develop a linear programming model to determine the manufacturing quantity for each type in order to maximize profit..

Replace the image below with a screenshot of your solver settings

Type A chocolate units

Type B Chocolate units

Profit $

Q6

QUESTION 6 ChildrenFun Inc. manufactures toys two models of plastic toys: Tetra and Ultra. There are 2,000 units of plastic available each day for production..

Tetra toys require 20 units of plastic per unit while Ultra models require 12 units of plastic per unit.

Both of these toys require a production time of 5 minutes. Total working hours are 9 hours a day .

If Tetra toys are sold at $ 25 each and Ultra toys are sold at $20 each, how many units of each model should be produced to maximize profit ? .

Replace the image below with a screenshot of your solver settings

Tetra Toys units

Ultra Toys units

Profit $

Q7

QUESTION 7 Island Publishing Company publishes two types of magazines on a monthly basis: a restaurant and entertainment guide and a real estate guide. The company distributes the magazines

free to businesses, hotels, and stores on Hilton Head Island in South Carolina. The company’s profits come exclusively from the paid advertising in the magazines.

Each of the restaurant and entertainment guides distributed generates $0.5 per magazine in advertising revenue, whereas the real estate guide generates $0.8 per magazine..

The real estate magazine is a more sophisticated publication that includes color photos,and accordingly it costs $0.54 per magazine to print, compared with only $0.3 for the restaurant and entertainment guide.

The publishing company has a printing budget of $10,000 per month. There is enough rack space to distribute at most 18,000 magazines each month. In order to entice businesses to place advertisements,.

Island Publishing promises to distribute at least 8,000 copies of each magazine. The company wants todetermine the number of copies of each magazine it should print each month in order to maximize advertising revenue..

Replace the image below with a screenshot of your solver settings

Rest. and Entertainement Number of magazines

Real Estate guide Number of magazines

Total revenue $

Q8

QUESTION 8 A hospital dietitian prepares breakfast menus every morning for the hospital patients. Part of the dietitian’s responsibility is to make sure that minimum daily requirements for vitamins A and B are met

while maintaining the cost of the menus as low as possible. The main breakfast staples providing vitamins A and B are eggs, bacon, and cereal. The table below shows the vitamin requirements and vitamin contributions for each :

Vitamin mg/Egg mg/Bacon strip mg/cereal cup MIN Daiy requirements

A 3 4 3 20

B 4 2 1 15

An egg costs $0.06, a bacon strip costs $0.05, and a cup of cereal costs $0.04.

The dietitian wants to know how much of each staple to serve per order to meet the minimum daily vitamin requirements while minimizing total cost.

Replace the image below with a screenshot of your solver settings

Egg (mg)

Bacon (mg)

Cereal (mg)

Cost $

Q9

QUESTION 9 A family wants to finance a home mortgage and is considering 3 options: a one-year adjusted-rate mortgage (ARM) at a low interest rate, a three-year ARM at a slightly higher rate, and a 30-year fixed mortgage at the highest rate. .

Both ARMs are sensitive to interest rate changes and the rates may change, resulting in either higher or lower interest charges..

Because the family anticipates staying in the home for at least five years, they want to know the total interest costs they might incur;.

these represent the payoffs associated with their choice and the future change in interest rates and can easily be calculated using a spreadsheet. The payoff table is given below..

Outcome Probabilities

Decisions Rates Rise Rates Stable Rates Fall Rates Rise Rates Stable Rates Fall

1-Year ARM $84,500 $55,000 $52,000 0.25 0.44 0.31

3-Year ARM $79,500 $65,500 $57,000

30-Year Fixed $67,500 $67,500 $67,500

Part 1 Use excel formulas and functions to determine the following calculations.

Best and Worst Payoffs Opportunity-Losses

Minimum Maximum Rates Rise Rates Stable Rates Fall MAX

Type of decision problem 1-Year ARM 1-Year ARM

What are the payoffs ? 3-Year ARM 3-Year ARM

30-Year Fixed 30-Year Fixed

Part 2 What decision should the company make using each of the following strategies ?

a. Decision using Aggressive strategy with Payoff value = which is the value among the outcome values of the alternatives considered

b. Decision using Conservative strategy with Payoff value = which is the value among the outcome values of the alternatives considered

c. Decision using Opportunity-Loss strategy with opportunity loss = which is the value among the opportunity losses of the alternatives considered

Part 3 Compute the standard deviation of the payoffs for each decision. What does this tell you about the risk in making the decision?

a. Std Deviation of Payoffs for

b. Std Deviation of Payoffs for

b. Std Deviation of Payoffs for

c. Which Decision has Greater Risk ?

Part 4 What decision should be made using the average payoff strategy ?

a. Average of Payoffs for

b. Average of Payoffs for

c. Average of Payoffs for

d. Decision using the Average Payoff strategy

Part 5 Use the probabilities given on the problem statement section to determine what decision should be made using the Expected Value strategy

a. Expected value for

b. Expected value for

c. Expected value for

d. Decision using Expected value strategy

Q10

QUESTION 10 Slaggert Systems is considering becoming certified to the ISO 9000 series of quality standards. .

Becoming certified is expensive, but the company could lose a substantial amount of business if its major customers suddenly demand ISO certification and the company does not have it. .

At a management retreat, the senior executives of the firm developed the following payoff table, indicating the net present value of profits over the next five years..

Customers’ Response

Decision Standards Required Standards Not Required Probabilities

Become certified $82,000 $63,000 Standards Required Standards Not Required

Stay uncertified $80,000 $72,500 0.41 0.59

Part 1 Identify the type of decision problem and use excel formulas and functions to determine the following calculations.

Best and Worst Payoffs Opportunity-Losses

Minimum Maximum Standards Required Standards Not Required MAX

Type of decision problem Become certified Become certified

What are the payoffs ? Stay uncertified Stay uncertified

Part 2 What decision should the company make using each of the following strategies ?

a. Decision using Aggressive strategy with Payoff value = which is the value among the outcome values of the alternatives considered

b. Decision using Conservative strategy with Payoff value = which is the value among the outcome values of the alternatives considered

c. Decision using Opportunity-Loss strategy with opportunity loss = which is the value among the opportunity losses of the alternatives considered

Part 3 Compute the standard deviation of the payoffs for each decision. What does this tell you about the risk in making the decision?

a. Std Deviation of Payoffs for becoming certified

b. Std Deviation of Payoffs for staying uncertified

c. Which Decision has Greater Risk ?

Part 4 What decision should be made using the Average payoff strategy ?

a. Average of Payoffs for becoming certified

b. Average of Payoffs for staying uncertified

c. Decision using Average Payoff strategy

Part 5 Use the probabilities given on the problem statement section to determine what decision should be made using the Expected Value strategy

a. Expected Value for becoming certified

b.Expected Value for staying uncertified

c. Decision using Expected value strategy