Brenda's Bicycle and Surfboard Rentals leases quad-bikes each day from a supplier and rents them to customers who use them along Seawall Boulevard in Galveston, Texas. Brenda wants to estimate her daily profit situation. The following market scenarios are considered in the estimation: - In the morning of each day, Brenda decides the number of bikes to lease from her supplier based on the weather. There is 50% chance that she leases 25 bikes (on rainy days) and 50% chance to lease 35 bikes (on sunny days). - The leasing price offered by her supplier also varies day by day, following a Uniform distribution between $3 and $5 (i.e., can be decimal values). - She then rents them to her customers for a rental fee depending on weather and day of the week. The rent fee can be as high as \$18 per day, as low as \$10 per day, but most likely \$14 per day (i.e., following a symmetric triangular distribution). - Rental demand follows a Normal distribution, with a mean of 30 bikes and a standard deviation of 6 bikes. (In your model, demands MUST be rounded to the nearest integer). (1) Build a profit model for Brenda that incorporates the uncertainties in leasing cost, leased quantity, rental fee, and rental demand. (Hint: Profit = Rental_fee* Rental_demand - leasing_cost*leased_quantity) (2) Use Data Table function to generate 100 trials of profit. Copy these 100 random values, and in another blank cell in the same worksheet, paste in the values only. (Hint: Use the Uncertain Profit example as a template, BUT there are not exactly the same! You need to use the corresponding formulas to simulate each variable.) (3) Use these 100 "frozen" values to report the Descriptive Statistics (output option: Summary Statistics), and percentiles. Provide a brief summary on the firm's prospective profit. Make sure to retain all the formulas in the worksheet. If I cannot verify the formulas used and see static results only, a minimum 50% penalty will be applied. Brenda's Bicycle and Surfboard Rentals leases quad-bikes each day from a supplier and rents them to customers who use them along Seawall Boulevard in Galveston, Texas. Brenda wants to estimate her daily profit situation. The following market scenarios are considered in the estimation: - In the morning of each day, Brenda decides the number of bikes to lease from her supplier based on the weather. There is 50% chance that she leases 25 bikes (on rainy days) and 50% chance to lease 35 bikes (on sunny days). - The leasing price offered by her supplier also varies day by day, following a Uniform distribution between $3 and $5 (i.e., can be decimal values). - She then rents them to her customers for a rental fee depending on weather and day of the week. The rent fee can be as high as \$18 per day, as low as \$10 per day, but most likely \$14 per day (i.e., following a symmetric triangular distribution). - Rental demand follows a Normal distribution, with a mean of 30 bikes and a standard deviation of 6 bikes. (In your model, demands MUST be rounded to the nearest integer). (1) Build a profit model for Brenda that incorporates the uncertainties in leasing cost, leased quantity, rental fee, and rental demand. (Hint: Profit = Rental_fee* Rental_demand - leasing_cost*leased_quantity) (2) Use Data Table function to generate 100 trials of profit. Copy these 100 random values, and in another blank cell in the same worksheet, paste in the values only. (Hint: Use the Uncertain Profit example as a template, BUT there are not exactly the same! You need to use the corresponding formulas to simulate each variable.) (3) Use these 100 "frozen" values to report the Descriptive Statistics (output option: Summary Statistics), and percentiles. Provide a brief summary on the firm's prospective profit. Make sure to retain all the formulas in the worksheet. If I cannot verify the formulas used and see static results only, a minimum 50% penalty will be applied
