Design a Table Where Time is an Input Variable, and Calculate the Distances Moved by Alex and Betty: Excel Homework, SIM, Singapore

1. Design a table where Time is an input variable, and calculate the distances moved by Alex and Betty. Assume a distance lead of 120 meters by the Betty at time = 0, and the speeds of Alex and Betty to be 4 m/s and 2 m/s, respectively. Use Goal Seek to find the time and distance at which the two meet.

2. Alex and Betty are now in a room. Calculate the probability that the two of them to not have their birthdays on the same day. John then enters; what is the new probability that the three of them do not share their birthdays? Design a table in Excel to find out at which point (i.e., how many persons in the room) does the probability go below 50%. In other words, find the number of person intakes so that the probability of at least 2 persons share the same birthday.

3. Gwenda is excited to finally buy her dream home.

• If the prevailing interest rate is 3.5%, and she borrows SGD 800,000 from the bank, what would the monthly repayment to the bank be over a 20year period? Create a well-labeled model that allows entering different interest rates, periods, and loan amounts that calculates the monthly repayment, from first principles.
• Gwenda learns that the government has introduced a cooling measure that caps the portion of the monthly income that can be used towards loan repayments. She learns that a mortgage servicing ratio of 60% applies. Her current income is 8000 SGD per month. What’s the maximum loan amount she can get?
• Create a loan amortization table for Gwenda. You may use built-in financial functions for this part of the problem. The last row of the table should show the total amount paid in interest and in principal.

4. Kim’s restaurant specializes in serving sumptuous Peranakan cuisine. Due to the recent pandemic, patronage to the restaurant dropped drastically. Kim’s is now considering alternatives to drive up its sales. Before the pandemic hit, the restaurant offered 4 different set menus. The cost per set, the price, and the number of seats sold per day (average over the week) are provided below. The fixed costs of running the business add up to $250 (per day), and the variable cost (e.g., gas) per set is $1.50.

• Design a well-labeled model in Excel to calculate the profit margin of the restaurant prior to the pandemic.

The restaurant’s sales (for each set type) has dropped drastically during the pandemic period. Due to the sanitization procedures put in place to combat the spread of the virus, the fixed cost per day has increased to $300 (per day).

• What is the profit margin under S2?

The owner is considering raising the price of the most popular set (i.e., Set 1) to break even and survive through the pandemic period.

• What would the new price of Set 1 be?

The owner’s son intervenes. He argues that if the restaurant were to increase the price of the most economical item on the menu, the demand would further drop resulting in worse performance. He proposes two alternatives.

OPTION A (S3):

He estimates that the demand for the premium set (i.e., Set 4) would increase if the price were to drop and help turn a profit (better than breaking even!). He estimates that at a pricing of $12, the number of seats sold will increase to 60 (from 5). Given this information, using Solver, determine the price of Set 4 that will maximize the profit.

OPTION B (S4):

As a second alternative, he thinks that partnering with a food delivery service would improve sales. Due to the resulting wider reach, the son estimates that the sales will reach 50% of their original, pre-pandemic (i.e., S1) numbers. However, an additional fixed cost of $50 (per day) and a 10% commission on sales is to be paid to the service provider.

• Which alternative should the owner consider?

5. The XYZ Trading Company is keen to observe the market movement of Jet Fuel and Diesel, for the upcoming months. The two commodities are transported by sea, and the paperwork on how much of each fuel type has been shipped from Port A to Port B, on Ship C, takes more than 3 days to be made publicly available.

A data analyst at the company suggests that he can find the amounts shipped faster, based on data from a real-time ship tracking information service. The service provides information on the shipment including the draught (i.e., the depth of a ship’s keel beneath the waterline). The density of jet fuel and diesel is known to be 1.1 kg/l and 0.85 kg/l, respectively. Additionally, using historic data, the analyst approximated the relationship between the weight of the cargo and the draught to be,

draught = 0.0025*(total weight) + 0.035

For simplicity, assume that Jet fuel and Diesel are the only two types of cargo shipped from A to B.

For the shipment to be profitable, the ship would carry a total volume of at least 1000 litres.
However, the maximum volume the ship can carry is 1200 litres.

• Can the analyst find how much Jet Fuel and Diesel the ship is carrying, if the draught is 1.65 meters?
• What is the total volume of cargo the ship is carrying?

(Hint: Use Solver with constraints).

(Note that the actual volumes carried by ships are much higher than what’s stated in the example; the numbers here are intentionally fictional to keep things easier to comprehend).