Question 1

Prosperity Land Ltd (PLL) is a commercial property developer. It designs and builds malls for customers to operate their businesses. Instead of renting the space to the customers, PLL sells the units to them under Strata Title. PLL needs to determine the right selling prices which allows it to maximize profit while attracting many buyers.

This assignment requires you to practice principles of correct spreadsheet construction and design to help PLL to improve its decision making to meet its business challenges.

Note: you must follow the information given in this TMA case and ignore other rules and regulations concerning property sales and bank loans.

• Mall Traffic, measured by the number of visitors-per-day, is often used as a variable to predict the occupants’ business viability. PLL has collected the following information on mall traffic and selling price per square meter of four existing malls.

Data for Mall Traffic and Selling Price

Note: The Selling Price is valid only for units with areas between 50 to 200 square meters.

Record how Selling Price changes with Mall Traffic by creating a scatter plot with a Trend Line and equation shown, assuming a linear relationship between the variables.

PLL has completed building three properties with estimated mall traffic as shown below. The selling price has yet to be decided.

Using the TRENDLINE that you have computed above, compose a spreadsheet model that will allow PLL to determine the Selling Price for each of the three properties by using Excel function(s).

• To attract customers to purchase larger units, PLL provides discounts as shown below:

For example, a unit with 180 square meters will have the discount = (100 sq. meters * 0% of selling price per sq. meter) + (50 sq. meters * 5% of selling price per sq. meter) + (30 sq. meter * 10% of selling price per sq. meter)

The data is only valid for the units with floor areas between 50 square meters (the smallest unit) to 200 square meters (the largest unit).

PLL requires the customers to pay at least 10% of the Selling Price as a down payment. It has tied up with Alliance Business Bank (ABB) to provide loans to customers for the remaining amount. ABB allows the customers to re-pay the loan in equal installments (with interest) at the end of each month for up to 20 years.

Construct a spreadsheet model as shown below that will help PLL’s customers to calculate the monthly payment to the bank by entering data into the shaded cells. All non-shaded cells must contain Excel formulae without being simply linked to another cell.

Note: To illustrate your skill in Excel Modelling with good practices, you shall document any use of good Excel functions or features to make your model resilient to data entry errors.

Cozy Eatery, an F&B company, has purchased a 175-square meter unit at Tuas Square. The owner intends to put a 13% down payment and pay the loan over 16 years. ABB will charge the loan at an annual interest rate of 1.5%. Use the model to calculate Cozy Eatery’s monthly payment to the bank.

• Demonstrate in a Data Table how the Monthly Payment will be affected by the “Number of payback years” and “Down payment (% of Selling Price)”. There shall be 5 rows at an increment of 1 year per row and 5 columns at an increment of 1% per column.

Cozy Eatery’s owner wants to keep the monthly payment just below $15,000. Identify the optimal combination of the percentage of down payment and the number of years of payment to reach this target.

You shall highlight any special or advanced features in your models in Q1(a) to Q1(c).

Question 2

One of the popular items in Cozy Eatery’s menu is a cake that it buys from Tasty Bakery.

The cost depends on the total quantity of weekly order as shown below:

Tasty also charges a weekly delivery fee of $70, regardless of the quantity ordered and number of delivery trips made in the week

For example, if the order quantity is 520 pieces, then the total cost = $70 + 520 pcs * $2.70/pcs.

Cozy Eatery is evaluating whether it should make the cake itself. It will have to employ a part-time baker who is able to produce the cake with equivalent quality. While the equipment cost is considered sunk cost, the baker’s salary and equipment maintenance cost will be $600 per week. The variable cost for each piece of cake will be $1.80.

• Create a model to help Cozy Eatery to decide whether to make or buy the cake. Refer to the table below, you shall start from 250 pieces of cakes in the first column and increment in steps of 10 pieces per row, up to 650 pieces. Cells in other columns must contain Excel formulae and/or functions without simple linking to another cell.

You must illustrate conditional formatting to highlight the cell in the Make column with the Green background if “make” is the cheaper option. Do likewise for the cell in the Buy column by highlighting it in Yellow background, if “buy” is the cheaper option.

The first row for 250 pieces is shown for reference.

In addition to the exploratory model, compare the two options by constructing a Scatter Chart with Make and Buy Costs versus Weekly Total Quantity.

Interpret the results from the model and the chart to advise Cozy Eatery’s owner on whether it should make or buy from quantity between 250 to 650?

• Cozy Eatery’s owner wants to know accurately the cross over quantity where there is no difference in costs between the Make and Buy options. The model you derived in Part(a) may not provide this information precisely. Explain and demonstrate how you can determine the crossover quantity accurately,

You shall highlight any special or advanced features in your models in Q2(a) and Q2(b).

Question 3

• Relate one method which Cozy Eatery can adopt to increase its F&B business customer base in each of the following departments with reference to your experience in running a virtual company in MonsoonSIM:
  1. Retail,
  2. B2B,
  3. Marketing

• The business environment is always competitive. Innovative organizations succeed not by imitation but by re-invention. Outline plans for Cozy Eatery to harnesses innovation to design two new products or services for its F&B business. You may refer to Enterprise Singapore’s Business Excellence Framework on Process Innovation. Write based on your own creativity, research, or apply innovations from any industry to Cozy Eatery.