Assignment 2 - G9 PDF

Preview

Summary

Warning: TT: undefined function: 32 Warning: TT: undefined function: 32 Warning: Popup annotation has a missing or invalid parent annotation. Warning: Popup annotation has a missing or invalid parent annotation. Warning: Popup annotation has a missing or invalid parent annotation. Warning: Popup ann...

Description

COR1305: Spreadsheet Modeling and Analytics Arindam CHATTERJEE

Assignment 2 (TOTAL of 60 Marks) AY2019-20: TERM 2 – Section G9 Due in Week 11, Thursday (19th March 2020) 9:00 PM Label your assignment submission filename with your name and the assignment number: e.g. Arindam-CHATTERJEE-A2.zip where A2 refers to Assignment 2 Drop it in the eLearn Submission Folder of your section with the name Assignment 2. Each question should be answered in its own single Excel Workbook. Upload a zip file of all the Workbook you upload. Name the Workbooks Arindam-CHATTERJEE-A2-Q1.xlsx, Arindam-CHATTERJEE-A2-Q2.xlsx, etc. Note the date due policy. All submissions will be time stamped. Multiple submissions are permitted, but only the last submission will be considered in terms of time and marks, with previous files getting overwritten by subsequent uploads. Late submissions will attract penalties. Valuation Rubric: 1) Correctness: 60% 2) Documentation: 20% 3) Formatting: 10% 4) Creativity/Bonus: 10% This assignment has six questions.

1

Question 1 (15 marks) The tortoise and the hare are having a race. Whoever can travel the most distance in an hour, wins. For the tortoise, each minute the probabilities are 25% he rests and doesn't move, 30% he travels forward 1 foot, 25% he travels forward 2 feet, 20% he travels forward 3 feet. For the hare, each minute the probabilities are 20% he moves backward 1 foot, 50% he doesn't move, 30% he moves forward 5 feet. a) Create a worksheet to simulate the race. You should have a row for each minute. You should have columns at least for: minute number, tortoise distance covered, hare distance covered, who is ahead. You may use other columns and cells as you wish. At the top of the worksheet give the winner for the simulation (or possibly it's a tie). [8 Marks] b) Simulate the race 5,000 times using a DATA TABLE. On an average what are the probabilities of the hare winning the race, the tortoise winning the race and ties. Create a table showing the probabilities. Note: Please solve this using simulation methodology instead of computing probabilities analytically. [5 Marks] c) What are the probabilities if for the hare, each minute the probabilities are now 10% he moves forward 1 foot, 60% he doesn't move, 30% he moves forward 5 feet. [2 Marks]

Question 2 (10 marks) We have three factories (M01, M02, M03) and five distributors (D01, D02, D03, D04, D05) of our products. The products are shipped by truck from factory to distributor. At most 150 units of our products fit a truck. The shipping cost is $1.50 per unit to load the truck and $2.00 per unit to unload the truck plus the mileage cost. The mileage cost is based on the mileage the truck must travel between the factory and the distributor. The mileage cost is independent of how many units are in the truck. The mileage cost is the same whether there are five units in the truck or 150 units, namely $20 per mile. The maximum monthly productions of the factories are (units): M01 4000

M02 2000

M03 1500

2

The monthly demands by the distributors are: D01 1000

D02 1600

D03 1500

D04 2000

D05 1400

The mileage chart of the distances between factories and distributors is: Distributors Factories

D01

D02

D03

D04

D05

M01

100

140

200

150

35

M02

50

65

60

70

80

M03

40

150

100

90

130

Use Solver to determine the number of units to send each month from each factory to each distributor so as to minimize the total shipping costs. Your worksheet also should show the number of trucks to send from each factory to each distributor and total shipping cost. Please add a screenshot of your Solver window to the solution.

Question 3 (10 marks) The Marina Bay Cherry slot machine has three wheels that spin independently when the handle is pulled. For each wheel the probabilities are 20% that a Cherry will appear, 26% that a Plum will appear, 27% that an Ace will appear, and 27% that a Seven will appear. For each pull of the handle you put in a bet of $1. If you get three Cherries you win your bet back plus $25. If you get any two Cherries you win your bet back plus $2. If you get one Cherry you get your bet back. Otherwise you lose your bet of $1. You start with a stake of $500. Create a worksheet to simulate a session of 500 spins. You should have 500 rows, one for each spin. You should have columns at least for the spin number (1 through 500), each of the three wheels, the amount you win or lose on the spin, and the amount of money you have at the end of the spin. Each time you press F9 (recalc) you should get a different set of spins and results according to the above probabilities. [5 Marks] Create a DATA TABLE (1000 simulations) to compute the average amount of money you have at the end of the 500 spins. [5 Marks]

3

Question 4 (5 marks) On the StarSMU Student Cell Phone plan: if you use 60 minutes or less in a month, you pay $17.99 in usage charge. If you use between 60 and 120 minutes, you pay the $17.99 plus $0.25 per minute for each minute over 60 in usage charge. If you use more than 120 minutes, you pay $29.99 plus $0.15 per minute for each minute over 120 in usage charge. Additionally, there is a $10 processing charge per month. Then, there is an 7.5% sales tax on all charges. Create a well-labeled worksheet that allows you to enter the minutes used and then shows the usage charge, the processing charge, the tax, the total charge, and the average total cost per minute. Show your results for 10 minutes, 100 minutes, 2500 minutes and also the formula.

Question 5 (10 marks) A bakery shop is selling 3 kinds of pastries (P01, P02, and P03) and their selling prices are $25, $35 and $40 respectively. The raw materials to make the cakes are (R01, R02, R03, R04) and the

given in the below table: R01 R02 R03 R04

Price($)

P01

2

2

3

4

$ 25.00

P02

3

2

4

3

$ 35.00

P03

4

3

5

6

$ 40.00

Available Qty

200 150

The objective is to

300 250 .

(a) Design a spreadsheet model to show how the production manager can

[3 marks] (b) Show the Solver window screenshot in the solution sheet. [2 marks] (c) Explain the purpose of each constraint used. [2 marks] (d) What is the

and what is the

[2 mark] (e) If P01 is a much popular cake such that the , will there be a

? Please explain.

[1 mark]

4

Question 6 [10 Marks] It is not unusual to find two or more people with the same birthday even in a small group. This is surprising considering there are 365 days in a year and the group size in comparison is relatively small. What is the chance of this happening? How does this change with the size of the group? Assumptions: i) Two people have the same birthday if the month and day are the same. Ignore the year ii) All days of the year are equally likely candidates for a person’s birth date iii) Disregard February 29th. So, there are 365 days every year Model the probability that

of n people?

a) Construct a spreadsheet model for a

. Simulate the birthdays of the people

in the group [2 Mark] Hint: Use discrete Uniform Distribution for the simulation b) Write a function to in your group of 42. The or

. [2 Marks]

c) Simulate the experiment with group sizes of 30 and 42, 1000 times each to compute the probability that at least two people have the same birthday in their respective groups [6 Marks]

5...