sessment item 3

Assignment 3

Value: 20%

Due date: 11-May-2018

Return date: 04-Jun-2018

Submission method options

Alternative submission method

Task

QUESTION 1 Decision Analysis

Show all calculations to support your answers. You may follow the methods shown in the mp4 on Decision Analysis for a way to do part (c) of this question if you wish.

Guide to marks: 20 marks - 3 for (a), 3 for (b), 14 for (c) (2 for each of 7 parts)

(a) Describe what is involved in the decision making process, explaining the steps required.

(b) What is an alternative? What is a state of nature? Give an example of each which are related.

(c) Every Thursday a fish vendor sets up a van in Wagga Wagga and sells seafood during the day. One of his best selling items is fillets of Atlantic salmon. The fillets are purchased from a city fish market for $15 per kg and sold for $30 per kg. Unsold fillets at the end of the day are disposed of for $10 per kg to a local take-away food proprietor.

Sales records for the last two years (100 weeks – he takes 2 weeks leave each year at Christmas time) are as follows:

Sales (kg) Number of times

10 10

15 20

20 40

25 20

30 10

1. Construct a conditional profits matrix showing the 5 possible alternatives (strategies) and the 5 possible sales levels, and fill in the profits associated with each of the 25 cells.

2. If the fish vendor were an optimist, how many kg would he buy each week?

3. If the fish vendor were a pessimist, how many kg would he buy each week?

4. If the fish vendor used the Laplace criterion, how many kg would he buy each week?

5. If the fish vendor used the criterion of regret, how many kg would he buy each week?

6. If the fish vendor based his decision on maximising expected monetary value, how many kg would he buy each week?

7. Suppose that weekly demand for the fillets is normally distributed with a mean of 20 kg and a standard deviation of 5 kg. How many kg should he buy each week to maximise expected profit (to the nearest kg)?

QUESTION 2 Value of information

Show all calculations to support your answers. You may follow the methods shown in the mp4 on Value of info for a way to answer this question if you wish.

Guide to marks: 20 marks - 4 for (a), 2 for (b), 6 for (c), 2 for (d), 6 for (e)

Round probability calculations to 2 decimal places.

A firm is considering marketing a new product which will be a success or a failure. The prior probability of success is judged to be 0.3.

If the product is marketed and is a success the firm expects to earn $1,000,000, while a failure is expected to lead to a loss of $600,000.

(a) Should the product be marketed? Why?

(b) What is the expected value of perfect information about the success or failure of the product?

The firm is considering a market survey whose results can be classified as favourable or unfavourable. Given past experience with the market survey personnel, the conditional probabilities are p(favourable|success) = 0.7 and p(unfavourable|failure) = 0.8.

(c) Revise the prior probabilities in light of these likely survey results.

(d) What is the posterior probability of success given a favourable survey result?

(e) What is the maximum the firm should pay for the market survey?

QUESTION 3 Simulation

This is a work integrated assessment item. The tasks are similar to what would be carried out in the workplace.

Guide to marks: 20 marks - 8 for (a), 3 for (b), 5 for (c), 4 for (d)

You have just been hired as an analyst to assist the manager of Heartbreak Hotel. Your first assignment is to examine and report on the reservations policy in the hotel.

Heartbreak Hotel routinely experiences no-shows (people who make reservations for a room and don’t show up) during the peak season when the hotel is always full. No-shows follow the distribution shown in the attached Excel proforma in cells A3:A9 and C3:C9.

In order to reduce the number of vacant rooms the hotel overbooks three rooms, i.e. accepts three more reservations than the number of rooms available. The hotel’s policy is to send any guests who miss out on a room to a competing hotel down the street at Heartbreak’s expense of $125 for each such guest. If the number of no-shows is more than three the hotel has vacant rooms resulting in an opportunity cost of $50 per room.

(a) Using Excel set up a model to simulate 1 month (30 days) of operation to calculate the hotel’s monthly cost due to overbooking and opportunity loss.

You can use this template to guide you:

A B C D E F G H I J

1 HEART BREAK HOTEL

2

3 Prob Cum prob No-shows

4 0.10 0 Rooms overbooked 3

5 0.15 1

6 0.30 2 Cost per room short $125

7 0.15 3 Cost per room vacant $50

8 0.20 4

9 0.10 5 Average daily cost

10

11 Day RN No Short Short Vacant Vacant Total

12 no-shows shows rooms cost rooms cost cost

13 1 0.10491

14 2 0.45405

You need to complete the Cumulative probabilities.

All data are shown in rows 3:9 except for cell J9 which will contain a formula.

There should be no numbers in your model which should consist of all formulas from row 13 onwards except for column A.

Column A shows the day number (1 to 30).

In column B random numbers are generated.

In column C there is a LOOKUP function to simulate the number of no-shows to be entered in column C.

In column D compute the number of short rooms (unavailable for guests) by comparing the number of no-shows with the number of rooms decided to be overbooked: e.g. =Max($J$4-C13,0). If the number of rooms overbooked exceeds the number of no-shows there is a shortage of available rooms, else if no-shows exceed rooms overbooked there is no shortage, but possibly vacancies (the formula would be negative but by placing a maximum of zero in the formula it comes out zero (no shortage)).

The short cost in column E is found by multiplying the cell J6 by D13 etc.

In column F (vacant rooms) the formula is the reverse of the one in column D: = max(C13-$J$4,0).

The cost of vacant rooms in column G is the product of the cost in J7 and the number of vacant rooms.

Total cost in column H sums col E and col G.

Copy formulas down to day 30, sum the total costs in col H and divide by the 30 to put the result in I9.

(b) Now print 2 copies of your model showing row and column numbers. Copy 1 should show the output, and copy 2 should show the formulas.

(c) Now test to find the number of rooms that Heartbreak Hotel should overbook each day. Test for 0,1,2,3,4,5 checking the total average daily cost each time. All you should have to do is change cell J4 and observe the change in average daily cost and tabulate them somewhere in your model. State the number of overbookings which gives minimum average daily cost over the 30 days. You need to take care of getting the same figure for each level of overbooking by either tabulating the results each time manually, or using an IF statement.

(d) You present your findings to the hotel manager with your recommendation as to how many rooms should be overbooked each day. The report must be dated, addressed to the Manager and signed off by you.

(Word limit: No more than 150 words)

QUESTION 4 Regression Analysis

Guide to marks: 20 marks - 12 for (a), 3 for (b), 5 for (c)

Barry Smith is on a work visa in the USA for 3 years and wishes to buy a second-hand car to use over the 3-year period. He is particularly interested in buying a Volkswagen Jetta. He thinks that the market price is related to the mileage covered and the age of the car. Barry examines previous sales from the local area and compiles a list of data on 10 cars, as shown below:

Car Price Mileage Age (years)

1 $16,200 10,600 1

2 $16,000 21,800 1

3 $12,500 34,000 3

4 $11,300 41,700 3

5 $14,800 53,500 4

6 $12,900 57,200 5

7 $11.500 65,800 7

8 $9,900 72,100 6

9 $8,200 76,500 8

10 $9,500 84,700 9

(a) Using Excel, perform three regression analyses to regress Price against Mileage, then against Age, then against both of them simultaneously. Paste your results into Word. State the cost equation for each. Analyse and comment on the results of each regression as you perform it and determine the best one to use as a basis for future use.

(b) If you had to settle for the results of a simple regression, which one would you use and why? Explain why negative coefficients for Mileage and Age are acceptable.

(c) Should Barry use both Mileage and Age as a guide to price? The multiple regression should provide the answer to this. Also you could check correlation between the independent variables.

QUESTION 5 CVP Analysis

Guide to marks: 20 marks - 8 for (a), 6 for (b), 6 for (c)

We start with a simple use of Excel in CVP analysis. Copy this model into Excel:

A B

1 Known parameters

2 Selling price per unit 10

3 Fixed cost 1000

4 Variable cost per unit 5

5

6 Variables

7 Number of units X 1000

8

9 Results

10 Total revenue =B2*B7

11 Fixed cost =B3

12 Total variable costs =B4*B7

13 Total costs =B11+B12

14 Profit =B10-B13

And the answer is:

A B

1 Known Parameters

2 Selling price perunit 10

3 Fixed cost 1000

4 Variable cost per unit 5

5

6 Variables

7 Number of units X 1000

8

9 Results

10 Total revenue 10000

11 Fixed cost 1000

12 Total variable costs 5000

13 Total costs 6000

14 Profit 4000

If we want to calculate the BEP we can use Goal Seek (Data/What if analysis/Goal Seek).

______________________

Goal seek ? X

Set cell $B$14

To value 0

By changing cell $B7

OK Cancel

________________________

and this produces zero in cell B14 and the breakeven units in cell B7 (ie 200).

(a)

A manufacturer can make product A. The following data are available:

Selling price per unit $15, Variable cost per unit $7, Fixed cost $2,400.

Modify the model shown above and invoking Goal Seek, determine the number of units required to break even.

(b)

Use the modified model again to determine the number of units required to earn a profit before tax of $1,600.

(c) Now a second product is added, B:

Selling price per unit $20, Variable cost per unit $10, Fixed cost $3,600

He decides to manufacture both A and B this year in the ratio of 2 of A to 1of B.

Assuming total fixed costs are the sum of the fixed costs allocated to each product, how many of each product must be sold to earn a profit of $20,000?

Assignment 3

Value: 20%

Due date: 11-May-2018

Return date: 04-Jun-2018

Submission method options

Alternative submission method

Task

QUESTION 1 Decision Analysis

Show all calculations to support your answers. You may follow the methods shown in the mp4 on Decision Analysis for a way to do part (c) of this question if you wish.

Guide to marks: 20 marks - 3 for (a), 3 for (b), 14 for (c) (2 for each of 7 parts)

(a) Describe what is involved in the decision making process, explaining the steps required.

(b) What is an alternative? What is a state of nature? Give an example of each which are related.

(c) Every Thursday a fish vendor sets up a van in Wagga Wagga and sells seafood during the day. One of his best selling items is fillets of Atlantic salmon. The fillets are purchased from a city fish market for $15 per kg and sold for $30 per kg. Unsold fillets at the end of the day are disposed of for $10 per kg to a local take-away food proprietor.

Sales records for the last two years (100 weeks – he takes 2 weeks leave each year at Christmas time) are as follows:

Sales (kg) Number of times

10 10

15 20

20 40

25 20

30 10

1. Construct a conditional profits matrix showing the 5 possible alternatives (strategies) and the 5 possible sales levels, and fill in the profits associated with each of the 25 cells.

2. If the fish vendor were an optimist, how many kg would he buy each week?

3. If the fish vendor were a pessimist, how many kg would he buy each week?

4. If the fish vendor used the Laplace criterion, how many kg would he buy each week?

5. If the fish vendor used the criterion of regret, how many kg would he buy each week?

6. If the fish vendor based his decision on maximising expected monetary value, how many kg would he buy each week?

7. Suppose that weekly demand for the fillets is normally distributed with a mean of 20 kg and a standard deviation of 5 kg. How many kg should he buy each week to maximise expected profit (to the nearest kg)?

QUESTION 2 Value of information

Show all calculations to support your answers. You may follow the methods shown in the mp4 on Value of info for a way to answer this question if you wish.

Guide to marks: 20 marks - 4 for (a), 2 for (b), 6 for (c), 2 for (d), 6 for (e)

Round probability calculations to 2 decimal places.

A firm is considering marketing a new product which will be a success or a failure. The prior probability of success is judged to be 0.3.

If the product is marketed and is a success the firm expects to earn $1,000,000, while a failure is expected to lead to a loss of $600,000.

(a) Should the product be marketed? Why?

(b) What is the expected value of perfect information about the success or failure of the product?

The firm is considering a market survey whose results can be classified as favourable or unfavourable. Given past experience with the market survey personnel, the conditional probabilities are p(favourable|success) = 0.7 and p(unfavourable|failure) = 0.8.

(c) Revise the prior probabilities in light of these likely survey results.

(d) What is the posterior probability of success given a favourable survey result?

(e) What is the maximum the firm should pay for the market survey?

QUESTION 3 Simulation

This is a work integrated assessment item. The tasks are similar to what would be carried out in the workplace.

Guide to marks: 20 marks - 8 for (a), 3 for (b), 5 for (c), 4 for (d)

You have just been hired as an analyst to assist the manager of Heartbreak Hotel. Your first assignment is to examine and report on the reservations policy in the hotel.

Heartbreak Hotel routinely experiences no-shows (people who make reservations for a room and don’t show up) during the peak season when the hotel is always full. No-shows follow the distribution shown in the attached Excel proforma in cells A3:A9 and C3:C9.

In order to reduce the number of vacant rooms the hotel overbooks three rooms, i.e. accepts three more reservations than the number of rooms available. The hotel’s policy is to send any guests who miss out on a room to a competing hotel down the street at Heartbreak’s expense of $125 for each such guest. If the number of no-shows is more than three the hotel has vacant rooms resulting in an opportunity cost of $50 per room.

(a) Using Excel set up a model to simulate 1 month (30 days) of operation to calculate the hotel’s monthly cost due to overbooking and opportunity loss.

You can use this template to guide you:

A B C D E F G H I J

1 HEART BREAK HOTEL

2

3 Prob Cum prob No-shows

4 0.10 0 Rooms overbooked 3

5 0.15 1

6 0.30 2 Cost per room short $125

7 0.15 3 Cost per room vacant $50

8 0.20 4

9 0.10 5 Average daily cost

10

11 Day RN No Short Short Vacant Vacant Total

12 no-shows shows rooms cost rooms cost cost

13 1 0.10491

14 2 0.45405

You need to complete the Cumulative probabilities.

All data are shown in rows 3:9 except for cell J9 which will contain a formula.

There should be no numbers in your model which should consist of all formulas from row 13 onwards except for column A.

Column A shows the day number (1 to 30).

In column B random numbers are generated.

In column C there is a LOOKUP function to simulate the number of no-shows to be entered in column C.

In column D compute the number of short rooms (unavailable for guests) by comparing the number of no-shows with the number of rooms decided to be overbooked: e.g. =Max($J$4-C13,0). If the number of rooms overbooked exceeds the number of no-shows there is a shortage of available rooms, else if no-shows exceed rooms overbooked there is no shortage, but possibly vacancies (the formula would be negative but by placing a maximum of zero in the formula it comes out zero (no shortage)).

The short cost in column E is found by multiplying the cell J6 by D13 etc.

In column F (vacant rooms) the formula is the reverse of the one in column D: = max(C13-$J$4,0).

The cost of vacant rooms in column G is the product of the cost in J7 and the number of vacant rooms.

Total cost in column H sums col E and col G.

Copy formulas down to day 30, sum the total costs in col H and divide by the 30 to put the result in I9.

(b) Now print 2 copies of your model showing row and column numbers. Copy 1 should show the output, and copy 2 should show the formulas.

(c) Now test to find the number of rooms that Heartbreak Hotel should overbook each day. Test for 0,1,2,3,4,5 checking the total average daily cost each time. All you should have to do is change cell J4 and observe the change in average daily cost and tabulate them somewhere in your model. State the number of overbookings which gives minimum average daily cost over the 30 days. You need to take care of getting the same figure for each level of overbooking by either tabulating the results each time manually, or using an IF statement.

(d) You present your findings to the hotel manager with your recommendation as to how many rooms should be overbooked each day. The report must be dated, addressed to the Manager and signed off by you.

(Word limit: No more than 150 words)

QUESTION 4 Regression Analysis

Guide to marks: 20 marks - 12 for (a), 3 for (b), 5 for (c)

Barry Smith is on a work visa in the USA for 3 years and wishes to buy a second-hand car to use over the 3-year period. He is particularly interested in buying a Volkswagen Jetta. He thinks that the market price is related to the mileage covered and the age of the car. Barry examines previous sales from the local area and compiles a list of data on 10 cars, as shown below:

Car Price Mileage Age (years)

1 $16,200 10,600 1

2 $16,000 21,800 1

3 $12,500 34,000 3

4 $11,300 41,700 3

5 $14,800 53,500 4

6 $12,900 57,200 5

7 $11.500 65,800 7

8 $9,900 72,100 6

9 $8,200 76,500 8

10 $9,500 84,700 9

(a) Using Excel, perform three regression analyses to regress Price against Mileage, then against Age, then against both of them simultaneously. Paste your results into Word. State the cost equation for each. Analyse and comment on the results of each regression as you perform it and determine the best one to use as a basis for future use.

(b) If you had to settle for the results of a simple regression, which one would you use and why? Explain why negative coefficients for Mileage and Age are acceptable.

(c) Should Barry use both Mileage and Age as a guide to price? The multiple regression should provide the answer to this. Also you could check correlation between the independent variables.

QUESTION 5 CVP Analysis

Guide to marks: 20 marks - 8 for (a), 6 for (b), 6 for (c)

We start with a simple use of Excel in CVP analysis. Copy this model into Excel:

A B

1 Known parameters

2 Selling price per unit 10

3 Fixed cost 1000

4 Variable cost per unit 5

5

6 Variables

7 Number of units X 1000

8

9 Results

10 Total revenue =B2*B7

11 Fixed cost =B3

12 Total variable costs =B4*B7

13 Total costs =B11+B12

14 Profit =B10-B13

And the answer is:

A B

1 Known Parameters

2 Selling price perunit 10

3 Fixed cost 1000

4 Variable cost per unit 5

5

6 Variables

7 Number of units X 1000

8

9 Results

10 Total revenue 10000

11 Fixed cost 1000

12 Total variable costs 5000

13 Total costs 6000

14 Profit 4000

If we want to calculate the BEP we can use Goal Seek (Data/What if analysis/Goal Seek).

______________________

Goal seek ? X

Set cell $B$14

To value 0

By changing cell $B7

OK Cancel

________________________

and this produces zero in cell B14 and the breakeven units in cell B7 (ie 200).

(a)

A manufacturer can make product A. The following data are available:

Selling price per unit $15, Variable cost per unit $7, Fixed cost $2,400.

Modify the model shown above and invoking Goal Seek, determine the number of units required to break even.

(b)

Use the modified model again to determine the number of units required to earn a profit before tax of $1,600.

(c) Now a second product is added, B:

Selling price per unit $20, Variable cost per unit $10, Fixed cost $3,600

He decides to manufacture both A and B this year in the ratio of 2 of A to 1of B.

Assuming total fixed costs are the sum of the fixed costs allocated to each product, how many of each product must be sold to earn a profit of $20,000?

Assessment: 02- Report / Project / Role PlayAssessment Description:This is a Holistic assessment, developed to satisfy the Performance Elements along with the Performance Evidences of the unit. The assessment...Mr Simon has spent 12 years continuous service as a line manager in a retail organisation. In the past three years he has twice been considered for promotion via a formal interview process and was unsuccessful....MSc Project Proposal Do not add any front sheets or title pages. Read the notes in italics and remove before submitting. You should also delete this paragraph of text. Minimum page limit of 3 and maximum...Adept owl scenario procedure instructor 2Please use examples from literature to support your ideas in your discussion.LOI, 2, 3 & 4 (1500 words)Consideryou might want to reflect on your experiences as a child and how these influence your...CUC107 Assessment 1: Cultural Mind MapValue: 15%Time: This task should take about 4-5 hours. Word count (250-300) images will varyDue: Monday, 11:59pm (CST), Week 5Submission:• in PDF format at the 'Assessment...Hi This is 800 words assignment based on case study and its the part B i.e. scope and time management plans Part B – Scope and Time Management Plans In this subject, you will be creating key components...**Show All Questions**