INTRODUCTION

The purpose of this MS Excel Major Assignment is to give you some experience with several of the advanced “decision support” features of the Microsoft Excel spreadsheet.  These features include:

• Data analysis using pivot tables
• Comparison of different scenarios using scenario manager
• Working with multiple worksheets
• Spreadsheet organisation using grouping & outlines
• Advanced MS Excel functions

DECISION MAKING SCENARIO

The purchase of a house usually entails some exchanges of prices between buyers and sellers.  The realtor is there to facilitate the transactions.  The seller has an asking price but typically settles for less.  The commission paid to Happy Realty (for property priced above $400,000) is 9% commission on the actual selling price for the first $200,000, and a further 2.5% for the remaining selling price.  A flat commission of 7.5% is charged (for property priced at $400,000 & below). The realtor in turns gets 40% of the total commission paid to Happy Realty.

Happy Realty wishes to encourage its realtors to try to sell the house as close to the asking price of the sellers as possible.  To do that Happy Realty will pay additional bonus for house sold at a certain percentage of the asking price.  The distribution of extra bonus is calculated as follows:

• A extra 1.65% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house at 95% or more of the asking price.
• A extra 1.25% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house sold at 90% or more of the asking price.
• A extra 1.05% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house sold at less than 90% of the asking price
• No extra bonus will be paid for houses sold at less than 80% of the asking price.

Happy Realty revenue is calculated from the commissions paid by the seller minus the commission and extra bonus paid to the realtor.  Calculate the percentage of $ earned for each home based on the actual final transacted price.  Develop a worksheet to be used by the CEO of Happy Holdings Group of Companies to allow him/her to identify the key profit earned at Happy Realty.

Also use pivot tables to organise Salesperson’s commission by month with total commission for the first half of the year for each salesperson.  Also in another separate pivot table, organise your data to reflect the net profit by house and by month.

REQUIREMENTS

You are required to create on the 1^st spreadsheet (Commissions) a spreadsheet-based decision support model that allows the CEO of Happy Holdings Group of Companies to understand how the economic climate will affect the revenue earned at Happy Realty to compare different scenarios (e.g. Varying percentages collected from owners: 7%, 9%, 11% for the 1^st $200,000; the 2.5% is fixed for all scenarios).  Rather than having a separate model for each scenario, you are expected to design a single model and employ “scenarios” to change only those aspects of the model that varies with the scenarios being considered.  At the same time, he is able to use the same workbook to forecast income for his group of companies.

Use advanced MS Excel built-in functions (IF, HLOOKUP, VLOOKUP, etc.) in your spreadsheet wherever you see fits.  Also use pivot tables to organise Realtor’s commission by month with total commission for the first half of the year for each realtor.  Also in another separate pivot table, organise your data to reflect the net profit by house and by month.

On a 2^nd worksheet (Forecast), also plan a 5-year forecast of the Group’s corporate taxable income based on the following assumptions:

Organise your spreadsheet (if possible) into grouping & outlines.

Total Corporate Taxable Income = Group Income – Other Operating Expenses

On the 3^rd spreadsheet, (Loan) prepare a worksheet for all salespersons to use with the assumptions that if customer needs to take a house loan, it will normally be 75% of final transacted price with 25% being down payment.  The loan interest rate is prime rate (3%)  plus 0.75%, (assume prime rate to be 3%, and can be changed anytime).  Period of loan is usually 15 years.  All price used are net of GST and PST.

Calculate the following: (You may pick one of the houses listed in the 1st worksheet)

• Purchase Price of a house (may use linking worksheets feature)
• Monthly mortgage payment & Total house payments (including down payment) over the 15 years
• Total Interest for the house loan with Interest & Principal paid per month
• Starting & Ending dates of payment
• Beginning Principal & Ending Balance at the end of each month till the end of the loan

Example: 

…

…

DESIGN ISSUES

The following is a short list of generic design issues that you should consider when building your application:

• Never use a number in a formula. The purpose of a separate table of assumptions is to allow you to identify your assumptions and change them easily
• Named ranges and cells should be used where practical to make your formulas more readable.
• Use scenarios (instead of 2 separate workbooks) to compare situations that share the same basic model, but which have different values for critical decision inputs.
• Create 1st 2 spreadsheets in ONE workbook named Happy.xls and the 3rd spreadsheet in another workbook named Dream.xls and link them. Use linking formula across workbooks/worksheets wherever possible.

SUBMISSION

You are required to submit all relevant softcopies of your designed spreadsheets via email by the deadline.  You are required to e-mail to me with the e-mail subject (example FirstName_LastName_Campus) (example: your subject line should read JohnSmith_NW or JohnSmith_DL).

You should also make a backup of the system.  Late submissions will not be graded.

4

DATA (You MUST use the following data for your assignments.  )

Sales for the 1^st 6 months as follows: