COR1305-Assignment 2-2020T2 PDF

Preview

Summary

1COR1305 Spreadsheet Modeling and Analytics (SMA)Assignment 2 (TOTAL = 43 Marks) – AY2019/20 Term 2 - Due in Week 7Label your filename as follow: e. JackTan_A2_GX where GX refers to section GX; A2 refers to Assignment 1 and drop it into eLearn Dropbox Assignment 2.Note the date due policy. All submi...

Description

COR1305 Spreadsheet Modeling and Analytics (SMA) Assignment 2 (TOTAL = 43 Marks) – AY2019/20 Term 2 - Due in Week 7 Label your filename as follow: e.g. JackTan_A2_GX.xlsx where GX refers to section GX; A2 refers to Assignment 1 and drop it into eLearn Dropbox Assignment 2. 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. Late submissions will have marks deducted accordingly. Question 1 (8 marks) Concert tickets are priced as follows: Premier tickets Floor tickets Balcony tickets Standing Room

22 and under $65 $50 $30 $15

over 22 $90 $70 $30 $20

(a) Create a model that allows a person to enter his/her age and the preferred ticket location (Premier, Floor, Balcony, Standing Room) and automatically gives the price of the ticket. Use a lookup table. You need to use data validation for ticket location. (b) Create 2 test cases to demonstrate the solution. (Hint: input for criteria and ticket prices; validate location and age number) Question 2 (11 marks) United Manufacturing is planning on spending up to $4,000,000 on new automated assembly machinery. They have decided to purchase two different models of equipment and are trying to decide how many of each model they should purchase.   



The F300 model costs $350,000 each. The S250 model costs $220,000 each. The F300 has some extra features so at least one third of the models purchased should be the F300. The annual maintenance budget for the new equipment is set at $120,000. The annual maintenance cost of each F300 is $12,000. The annual maintenance cost of each S250 is $7,500. Each F300 will produce 125 units per day. Each S250 will produce 81 units per day.

Set up a model and then use Solver to determine how many of each model to purchase so that the daily production is maximized as long as all the constraints are met.

1

Question 3 (13 marks) Our soccer team has 12 games scheduled. The coach figures that for each game the probabilities are: Our Score Opponent's Score 10% the team scores 0 goals 35% the opponent scores 0 goals 20% the team scores 1 goal 10% the opponent scores 1 goal 40% the team scores 2 goals 10% the opponent scores 2 goals 20% the team scores 3 goals 10% the opponent scores 3 goals 10% the team scores 4 goals 35% the opponent scores 4 goals Note that the probabilities for the team's goals are independent of the probabilities for the opponent's goals. Create a model that "predicts" the season for 12 games. You should have columns for:  Game number (1 thru 12)  Random number for our team  Number of goals we score for our team,  Random number for opponent  Number of goals they score  Result of game for our team (win, lose, draw) At the top of the model show the output of the prediction in terms of the number of wins, losses, and draws. (Hint: you need to generate CRF table using the probabilities of X goals, X=0, 1,2,3,4, and then simulate the number of goals for each game using LOOKUP() or VLOOKUP()). Question 4 (11 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, 35% he travels forward 2 feet, 10% he travels forward 3 feet. For the hare, each minute the probabilities are 10% he moves backward one foot, 65% he doesn't move, 25% he moves forward 5 feet. Create a model to simulate the race. You should have a row for each minute. You should have columns for: minute, tortoise position, hare position, who is ahead. At the top of the worksheet give the winner for the simulation (or possibly it's a tie), and give the number of minutes during which the hare is behind where he started (Assuming the starting position at 0, the hare’s position should be negative when he is behind where he started). (Hint: Input for tortoise’s and hare’s CRF table)

2...