MX EXCEL
ASSIGNMENT
INTRODUCTION
The purpose of this 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
This project carries 13% of the total coursework marks, and it is an INDIVIDUAL assignment. The breakdown marks are as follows:
Required Tasks
%
Use of Advanced Functions such as SUMIF, AVERAGEIF, COUNTIF , etc…Earlier Functions should be used in this assignment as well.
20
Use of Scenario Manager
20
Use of Grouping
10
Accuracy of the calculations and format of the spreadsheets
15
Use of Linking Worksheets/Workbooks Features
10
Use of Pivot Tables & Charts
15
Creativity & Originality & Other Misc. Features
10
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 Victory Realty is 9% commission on the actual selling price for the first $200,000, and a further 2.2% for the remaining selling price if the selling price is above $400,000. A flat 6% is charged for selling price $400,000 & below. The realtor in turns gets 40% of the total commission paid to Victory Realty.
Victory 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 Victory 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.0% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house at 92% or more of the asking price.
A extra 0.75% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house sold at 88% or more of the asking price.
A extra 0.5% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house sold at less than 88% of the asking price
No extra bonus will be paid for houses sold at less than 82% of the asking price.
Victory 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 Victory Holdings Group of Companies to allow him/her to identify the key profit earned at Victory 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 1st spreadsheet a spreadsheet-based decision support model that allows the CEO of Victory Holdings Group of Companies to understand how the economic climate will affect the revenue earned at Victory Realty to compare different 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 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 2nd worksheet, also plan a 5-year forecast (2023 to 20267) of the Group’s corporate taxable income based on the following assumptions:
Forecast of Increase/Decrease Percentages:
Restaurant
5.50%
Motor
7.65%
Realty
12.20%
Entertainment
-7.15%
Computers
-3.50%
Directors’ Fees
5.15%
Organise your spreadsheet (if possible) into grouping & outlines.
This value should come from worksheet 1 times 2.5
2022
Victory Group Income
Net Profit from Victory Restaurant
$395,000
Net Profit from Victory Motors
$298,800
Net Profit from Victory Realty
X value
Net Profit from Victory Entertainment
$259,400
2.5 % of total group income
Net Profit from Victory Computers
$195,000
Other Operating Expenses
Annual Directors’ Fees
$275,000
Misc. Dividends to Shareholders
Y value
Total Corporate Taxable Income = Group Income – Other Operating Expenses
On the 3rd 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 65% of final transacted price with 35% being down payment. The loan interest rate is prime rate plus 0.95%,. Period of loan is usually 15 years. All price used are net of GST and PST.
Calculate the following:
Purchase Price of a house
Monthly mortgage payment & Total house payments 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:
Purchase Price
$925,000.00
House Loan
$601,250.00
Monthly Payment
$4,507.87
Down Payment
$323,750.00
Prime Rate
3.25%
Total Payment
$1,135,167.31
Plus rate
0.95%
Period (years)
15
Month
Beginning Principal
Interest Paid
Principal Paid
Ending Balance
Sep-22
$601,250.00
$2,104.38
$2,403.50
$598,846.50
Oct-22
$598,846.50
$2,095.96
$2,411.91
$596,434.59
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 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 Victory.xls and the 3rd spreadsheet in another workbook named Dream.xls and link them. Use linking formula across workbooks/worksheets wherever possible.