Question 1 :
Lawn mowing. John's father has a motorized lawn mower. He borrowed it to earn extra money during the summer mowing the neighbors' front yards. He charges $18 for each front yard and employs a classmate at $7 a day to help. He can do at most 8 yards in a day. His competitor, Melvin, uses a manual mower. He charges $20 a yard and can do at most 5 yards a day.
a. Construct a spreadsheet model to evaluate the situation. Sketch out the Influence diagram of your model.
b. Based on the given data only, who do you think would do better? Who do you think would do better in real life?
c. Elaborate how your model may be further improved to support your argument.
Question 2 :
Taxi fare. The flag-down fare inclusive of the first kilometer or less is $3 for normal taxi, $3.90 for limousine, and $5 for Chrysler. Every 400 m thereafter or less, up to 10 km is $0.22 for normal taxi, $0.25 for limousine, and $0.33 for Chrysler. Every 350 m thereafter or less after 10 km is $0.18 for normal taxi, $0.20 for limousine, and $0.28 for Chrysler. For all three taxi-types, there is an additional charge of $0.20 for every 45 s of waiting or less.
a. Construct a spreadsheet to compute the taxi fare for some customers.
b. Create your own input data to cover a variety of cases to test your model
Question 3 :
Sales section. The section has 8 salespersons. Their base pays vary from $12,000 to $36,000 per annum, according to their seniority, and are given corresponding sales targets. In addition, they get 5% commission of all sales they bring in and an additional 1% point commission for every dollar they sell above their targets. Any salesperson who sells $1 million or more gets an additional bonus of $2,000. The salespersons are ranked according to the percentage they achieve above their targets. Only if the person exceeds target, the first and second top ranked each get an incentive trip to the annual sales convention and a five-day all expenses paid vacation there for four. This year, it is in Hawaii and the incentive package is worth $6,000.
a. Construct a spreadsheet model for this. Simulate 8 salesperson's data and compute the individual salesperson's commission, bonus, incentive, and total compensation.
b. For your data, what is the total compensation the company has to give out?
c. What percentage of the compensation is for base pay, commission, bonus, and incentive?
d. Would you recommend any changes in the pay structure? Why?
Question 4 :
Slot machine. The slot machine is a favorite among many patrons in casinos and private clubs. It has three identical reels, each reel with spots for 64 images. Theseinclude cherry, plum, melon, ace, bar, bell, diamond, lemon, gold coins, orange, and seven. After inserting one or more dollar coins, the gambler pulls the handle and the reels spin. The machine displays three images, one from each reel, when the reels stop spinning. Depending on the counts of certain images displayed and the bet inserted, the machine gives in return nothing, some coins, or an overflowing payoff.
a. Construct a spreadsheet model to simulate the slot machine. Assume in each reel, there are 4 cherry, 4 plum, 4 melon, 5 ace, 5 bar, 7 bell, 7 diamond, 7 lemon, 7 gold coins, 7 orange and 7 seven images, all randomly arranged.
b. For every $1 inserted, the payoff for 1 cherry image is $2, 2 cherry images gives $5, and 3 cherry images gives $10; 2 plum images gives $20, 3 plum images gives $30; and 2 melon images gives $40 and 3 melon images gives $60. What is the net balance after 28 pulls when the initial is $100 and bets are all $1 each?
c. How do you handle more complex payoff conditions? For example, a set of 1 gold coins, 1 diamond and 1 melon images to pay out $150 per dollar bet.