Question

Prosperity Land Ltd (PLL) is a commercial property developer. It designs and builds malls for customers...

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 maximise profit while attracting many buyers. This assignment requires you to practise principles of correct spreadsheet construction and design to help PLL to improve its decision making to meet its business challenges. (a) 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
Mall Traffic (visitors per day) 15,000 10,000 6,000 4,000
Selling Price per square meter $14,000 $11,000 $7,200 $3,600
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.

Mall    CityPoint BishanCentre TuasSquare
Mall Traffic (visitors per day) 12,000 9,000 6,000
Selling price per square meter ? ? ?
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).

b) To attract customers to purchase larger units, PLL provides discounts as shown below: Floor Area Discount (% of Selling Price) Less than 100 sq. meters 0% 100 to less than 150 sq. meters 5% 150 sq. meters or more 10%

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 area 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 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 instalments (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. Property Payment Calculator

Name of Mall ?
Selling Price per sq. meter ?
Floor Area (sq. meters) ?
Selling Price before discount ($) ?
Discount ($) ?
Selling Price after Discount ?
Down payment (% of Selling Price) ?
Down payment ($) ?
Total Loan ($) ?
Bank Annual Interest (%) ?
Number of pay back years (yrs.) ?
Monthly Payment at the end of each month ($) ?
Cozy Eatery, a F&B company, has purchased a 175-square metre unit at Tuas Square. The owner intends to put 13% down payment and pay the loan over 16 years. ABB will charge the loan at annual interest rate of 1.5%. Use the model to calculate Cozy Eatery’s monthly payment to the bank.

(c) Demonstrate in a Data Table how the Monthly Payment will be affected by the “Number of pay back years” and “Down payment (% of Selling Price)”. There shall be 5 rows at increment of 1 year per row and 5 columns at increment of 1% per column. Cozy Eatery’s owner wants to keep the monthly payment just below $15,000. Identify the optimal combination of percentage of down payment and 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).

Mainly i need to know how to do Q(b) and (c).

Homework Answers

Answer #1

Solution:

The accounting history can be found in ancient times. In certain respects, the very art of writing was developed to accounting data record. Although it might seem like a exaggeration, but the fact that accounting is a long one is not denied history. Records can be traced back to the old Chinese, Babylonian, Greek and Egyptian cultures. Accounts have been used to maintain records of labour costs and supplies used Construction of great Pyramid-style structures.

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
The following balances have been extracted from the accounting system of Buloon (Pty) Ltd at 31...
The following balances have been extracted from the accounting system of Buloon (Pty) Ltd at 31 March 2016, their most recent financial year end. Mr Duffy, the owner, has approached you to assist him in planning for the next quarter ending on 30 June 2016. R Share Capital         220 000 Loan BAO Bank         160 000 Furniture and Equipment at Cost price         240 000 Vehicles at Cost price         200 000 Inventory           10 000 Debtors           80...
Question 1. (Total: 25 marks) You want to buy a car valued at $48,000. You wi...
Question 1. (Total: 25 marks) You want to buy a car valued at $48,000. You wi ll make an upfront down -payment of $5,000 on the car , and borrow the rest of the money from your bank. Your bank will give you a 5- year loan at 2.5% APR compounded semi -annually . You plan to make biweek ly payments (i.e., one payment every two weeks) on the loan . The bank requires that you make the first payment...
Kimmel Gnomes DATE:​March 1, 2018 TO:​CCSU Consulting FROM:​Candice Kimmel, President, Kimmel Gnomes SUBJECT:​Master Budget for the...
Kimmel Gnomes DATE:​March 1, 2018 TO:​CCSU Consulting FROM:​Candice Kimmel, President, Kimmel Gnomes SUBJECT:​Master Budget for the fiscal year July 1, 2018 – June 30, 2019 ---------------------------------------------------------------------------------------------------------------------------------- Our controller, Richard Kimmel is negotiating with potential new Clay suppliers in Kentucky. We need the Large Gnome Division’s Master Budget for the fiscal year ended June 30, 2019 for our corporate strategic planning process, and we cannot wait for Richard’s return from Kentucky. We would like you to prepare the Large Gnome Division’s...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT