Sample/practice exam 2019, questions PDF

Preview

Summary

Warning: TT: undefined function: 32 Warning: TT: undefined function: 32CORE1305 Spreadsheet Modeling and AnalyticsAssignment 2(Due date and time: October 17 , Thursday @ 3:30pm)Submission Instruction: There are five questions in this assignment. Please do all of them independently. Total = 50 points...

Description

SMU Classification: Restricted

CORE1305 Spreadsheet Modeling and Analytics Assignment 2 (Due date and time: October 17, Thursday @ 3:30pm) Submission Instruction: 1) There are five questions in this assignment. Please do all of them independently. Total = 50 points = 45 points for solution + 5 points for documentation. 2) Use one workbook (one file) to do this assignment. Please put each question in one separate worksheet and document your work properly. 3) Label your filename in the following format: Section_LASTNAME_Firstname_A2.xlsx. For example, my submission file in G16 will be “G16_GUO_Zhiling_A2.xlsx”. Please submit the excel file through the submission link in eLearn Assignments → Assignment 2 folder in your individual session G16 (not the combined session) before the due date and time. 4) All electronic submissions will be time stamped by the elearn system. Submission within the first 15 minutes of class (12:15pm) will be considered on time. Submission within 3 hours (before 3pm) is subject to 20% late penalty and within 6 hours (before 6pm) is subject to 40% late penalty. No assignment will be accepted after elearn submission link closes by 6pm on the due date.

Question 1 The vending machine located at SMU basement has two kinds of drinks: Fruit juice costs $1.00 each, Soda costs $1.20 each. Each morning at 8am the delivery person stops by and fills the vending machine so it has its capacity of 120 cans of juice and 100 cans of soda. Every ten minutes with 30% probability no one buys any juice, 35% probability one can of juice is sold, 25% probability two cans of juice are sold, and 10% probability three cans of juice are sold. Similarly, every ten minutes the purchase probabilities for soda are: 40% probability no one buys soda, 30% probability one can of soda is sold, and 30% probability two cans of soda are sold. Once the machine runs out of juice (or soda), no more juice (or soda) can be purchased. a) Construct a lookup table that shows the cumulative purchase probabilities and the corresponding outcomes (the number of cans being sold for each product). (2 points) b) Create a spreadsheet model to simulate the operation of the vending machine over 24 hours beginning at 8am. For each ten-minute interval, the model shows the number of cans of juice being sold, the number of cans of soda being sold, the number of cans of juice remaining in the vending machine and the number of cans of soda remaining in the vending machine. Please choose the “h:mm” format to customize the cells that show time. (4 points) 1

SMU Classification: Restricted

c) Use separate well-labelled cells in the worksheet to show the following values: (4 points) i)

How many cans of juice and soda are sold, respectively? What are the daily revenues for the two products?

ii)

At what time is the vending machine out of stock of juice and soda, respectively? If a product is still in stock at the end of the simulation, please show “in stock.”

Question 2 The tortoise and the hare are having a race. Whoever can travel the most distance in an hour wins. For the tortoise, each minute there is equal chance that he rests (doesn’t move), he travels forward 1 foot, 2 feet, and 3 feet, respectively. For the hare, each minute there is equal chance that he moves backward one foot, doesn't move, and 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, tortoise position, hare position, who is ahead. At the top of the worksheet give the winner for the simulation. In the case of a tie, please show “tie” (3 points). b) Create a chart that has minutes along the X axis and position in feet from the starting line along the Y axis. The chart should show the positions of the tortoise and the hare during the race. When pressing F9 to simulate a new race, you should allow the chart to be updated automatically (3 points). c) Please use data table to simulate 1,000 runs of the game. What is your estimated probability that Hare will win the game (2 points)? Question 3 The Super Cherry slot machine has three wheels that spin independently when the handle is pulled. For each wheel the probabilities are 15% that a Cherry will appear, 25% that a Plum will appear, 30% that an Ace will appear, and 30% 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 $10. 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 $20. a) Create a worksheet to simulate a maximum of 30 spins. You should have 30 rows, one for each spin. However, your model should stop simulating more rows once you run out of money. You should have columns at least for the spin number (1 through 30), outcome for each of the three wheels, the total number of Cherry you get for each play, your gain/loss for each play (the amount you win or lose on the spin), and the remaining balance you have at the end of the spin. Each time you press F9 to recalculate, you should 2

SMU Classification: Restricted

get a different set of spins and results according to the above probabilities. (so you cannot play anymore). (5 points) b) In order to increase the accuracy of your prediction, please build a data table to simulate 300 sessions (each with 30 spins) of the game outcome. Please calculate the average ending balance at the end of the game. (2 points) Question 4 The manager of catering services for the Rock Hotel has a problem. The banquet hall at the hotel cab be booked for each weekday evening. The expected number of tables for each day is shown in the following: Day Tables Reserved

Monday 400

Tuesday 300

Wednesday 250

Thursday 400

Friday 350

Table 1. Tables Reserved on Each Day of the Week The hotel has 500 tablecloths that can be used for these banquets. However, the tablecloths used at each banquet will have to be cleaned before they can be used again. A local cleaning service company, DC Service, will pick up the soiled tablecloths each evening after the banquet and offers overnight cleaning for $2 per tablecloth, or 2-day service for $1 per tablecloth (i.e., a tablecloth picked up Monday night can be ready Tuesday for $2 or ready for use Wednesday for $1). There are no tablecloth losses and all tablecloths must be cleaned. Due to the cleaner’s capacity constraints, the overnight service can be performed only up to 250 tablecloths. Note that there is no need to use the overnight service on tablecloths picked up Friday night because the 2-day service would be sufficient to get the tablecloths ready to use next Monday. All cloths used on Friday must be ready for use again by next Monday. The hotel wants to determine the least costly plan for having its tablecloths cleaned. We would like to create a spreadsheet model for this problem and solve it. This is one type of the business problems that can be represented graphically as networks. Network flow problems can be represented as a collection of nodes connected by arcs. There are typically three types of nodes: supply node (only has outflows), demand node (only has inflows), and transshipment node (intermediate node that has inflows and outflows). To formulate our problem as a network flow problem, we first define 10 transshipment nodes as in Figure 1: node 0 represents beginning inventory; nodes 1-5 represent transshipment at the hotel from Monday to Friday; and nodes 5-9 represent the transshipment at the cleaning service from Monday to Thursday. Based on the flows (hotel sending tablecloths to DC service or DC service sending back tablecloths to hotel), we connect the nodes by arcs as shown in Figure 1. On each arc we indicate the 3

SMU Classification: Restricted

minimum and maximum allowable limits. For example, on the arc from node 1 to node 6, the hotel has 400 tablecloths to clean on Monday night. So the minimum required outflow is 400. Because the hotel has maximum of 500 tablecloth for cleaning, the maximum allowable outflow is 500. We mark (400, 500) on this arc. As another example, node 6 represents cleaning service on Monday. For overnight delivery from node 6 to node 2, the maximum allowable flow is 250 (due to the service capacity constraint); for 2-day delivery from node 6 to node 3, the maximum allowable flow is 500. The same interpretation applies to other arcs.

Figure 1. Network Flow Model of the Decision Problem We can also put the unit cost on each arc. For example, it does not cost the hotel any money to flow from node 1 to node 6 (no charge when sending tablecloths from hotel to the cleaning service), but it costs the hotel $2 each tablecloth to flow from node 6 to node 2 (the overnight service) and costs $1 each tablecloth to flow from node 6 to node 3 (2-day service). All information in Figure 1 can be organized in a table format in a spreadsheet model (See B3:H20 in Table 1 in the screenshot in Figure 2). For each arc in a network flow model, we define a decision variable as: 𝑿𝒊𝒋 = the number of tablecloths being shipped (or flowing) from node 𝒊 to node 𝒋 We use cells A3:A20 to denote the decision variables. The shipment quantity on each arc should be bounded between the minimum and maximum allowable limits (the boundary conditions). In addition, the optimal solution should balance the inflow and out flows so that the net flow for each node is zero (the balance condition). The objective is to minimize the total cost.

4

SMU Classification: Restricted

(a) Define the net flow through a transshipment node as the total inflows to the node minus the total outflows from the node. Table 2 in the screenshot in Figure 2 is used to calculate the net flow of each node. Please fill in formula for L3, and autofill down your formula (2 points). (Hint: use the SUMIF function) (b) Cell H22 is used to calculated the total cost. Please fill in formula for this cell (1 points). (c) Set up your solver model. Please provide a screenshot of your solve model. Please also document your model by explaining the decision variables, the objective cell and the constraints you used in your models (4 points). (d) Based on your optimal solution, what is the hotel’s least costly plan for tablecloth cleaning (3 points)?

Figure 2. The Screenshot of Question 4 Template Question 5 With exactly 2,700 cm2 of cardboard, we would like to construct a box with six sides. We require the width to be double its depth. We would like to maximize the volume the box can hold. Which values of width, depth, and height will achieve our goal? a) Using initial value 1 for width, depth and height to solve the model. (Note that if you leave the decision variable cells empty, which is equivalent to using 0 as initial values, excel is unable to solve the problem. Please use 1 as initial values for all your decision variables in this question.) Provide a screen shot of your solver model showing solver parameters and the constraints you put in solver. (3 points) We have six types of nails. The following table displays the specifications of the six types of nails.

5

SMU Classification: Restricted

Type 1

Type 2

Type 3

Type 4

Type 5

Type 6

Unit Price

8

6

5

4

3

2

Unit Cost

4

3.5

3

2.5

2

1.5

Weight per Nail

1

0.85

0.7

0.5

0.25

0.1

Length (cm) per Nail

10

9

7.5

6.5

5

4

We want to put exactly three types of nails in the box. There must be no more than 10, but no less than 5, of each of three types of nails in the box. A box should contain at least 25 nails but no more than 30 nails. The nails in the box should weigh no more than 20 ounces. Note that our box is big enough so that the length and shape of the nails do not appear to be a constraint. Let’s look for the best combination with the highest profit for our nails packing in the box. (Note: for each nail, Unit Profit =Unit Price – Unit Cost. Also note that it may take long time for solver to solve this problem.) b) There are two types of decision variables in this optimization problem. Please state all the decisions relevant to this problem in your documentation (1 points). c) There are eight constraints in total for this optimization problem. Please state these constraints in your documentation (3 points). d) What are your selection of nails and your calculated profit (1 points)? e) A customer has required that type 1 and type 3 nails must be selected together if either one type is chosen (e.g., if type 1 is chosen, type 3 must be chosen as well, and vice versa). How to revise the constraints? What is your revised combination of nails and profit (2 points)?

6...