Commerce & Business Administration FALL 2021 CSIS 1190 – Excel in Business MS Excel Major Assignment Instructor: Victor Choong INTRODUCTION The purpose of this MS Excel Major Assignment is to give you...

1 answer below »
















Commerce & Business Administration

FALL 2021



CSIS 1190 – Excel in Business




MS Excel Major Assignment


Instructor: Victor Choong




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





This project carries 10% 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 (just to name a few), 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



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 Happy Realty (for property priced above $400,000) is9%commission on the actual selling price for the first $200,000, and a further2.5%for the remaining selling price.A flat commission of6.15%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.5% 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.0% 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 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 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 the1st
spreadsheet (Commissions) a spreadsheet-based decision support model that allows the CEO ofHappy Holdings Group of Companiesto unde8rstand 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 1st$200,000; the2.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 a2nd
worksheet (Forecast), also plan a 5-year forecast of the Group’s corporate taxable income based on the following assumptions:











































Forecast of Increase/Decrease Percentages:




Restaurant









6.5%




Motor














9.5%




Realty














11.0%




Entertainment









-7.0%




Computers









-3.5%




Directors' Fees









5.5%




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
















































































This value should come from worksheet 1 times 2.












2020




Group Income



















Net Profit from Happy Restaurant





$395,000



Net Profit from Happy Motors





$295,800



Net Profit from Happy Realty






X value



Net Profit from Happy Entertainment



$375,000


















5%of total group income



Net Profit from Happy Computers



$295,000














Other Operating Expenses







Annual Directors' Fees





$290,900



Misc. Dividends to Shareholders






Y value



Total Corporate Taxable Income = Group Income – Other Operating Expenses



On the3rd
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 (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:
































































Purchase Price



$850,500.00






House Loan



$552,825.00




Monthly Payment



$4,020.27






Down Payment



$297,675.00




Prime Rate



3.00%






Total Payment



$1,021,323.14




Plus rate



0.75%






Period (years)



15




Month




Beginning Principal




Interest Paid




Principal Paid




Ending Balance



Mar-20



$552,825.00



$1,727.58



$2,292.69



$550,532.31



Apr-20



$550,532.31



$1,720.41



$2,299.85



$548,232.46
















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 submissionswill not be graded.






DATA (YouMUSTuse the following data for your assignments.)





Sales for the 1st6 months as follows:


























































































Address of House




Date Sold




Asking Price




Final Transacted Price




Realtor



2567 Mica Place



Jan-20



$1,229,000



$1,228,000



Eric



1288 Pinetree Way



Jan-20



$1,323,000



$1,200,000



Eric



204 #81 Elm Street



Feb-20



$1,028,000



$850,500



Eric



#101 800 Schoolhouse Ave



Mar-20



$415,000



$399,900



Sam



#501 1290 Greenway Pl



Feb-20



$995,900



$888,000



Sam



#409 999 Como Lake



Apr-20



$822,000



$850,000



Carol



#111 122 Gilmore Ave



Apr-20



$412,800



$320,000



Sam



2388 Sugarpine Ave



Jun-20



$1,922,000



$1,850,000



Carol



1634 Diamond Cres.



Jun-20



$1,029,000



$950,000



Eric



123 Holdom Ave



May-20



$999,990



$780,000



Sam



582 #14 West 13thSt



Feb-20



$410,900



$389,900



Carol







Answered Same DayNov 25, 2021

Answer To: Commerce & Business Administration FALL 2021 CSIS 1190 – Excel in Business MS Excel Major Assignment...

Subhanbasha answered on Nov 25 2021
101 Votes
Decision Making
    Address of House    Date Sold    Realtor    Property Price    Actual Selling price    Atual Selling price-first    Atual Selling price-Remaining    Happy Realty Commisi
on-Fitst    Happy Realty Commision-Remaining    Total Commission for Happy Realty    Realtor Commission    Realtor Commision - Final    Happy Realty revenue     percentage of $ earned    7%    11%    Pecentage That seller will give    Scenario Based
    2567 Mica Place    20-Jan    Eric    $ 1,229,000.00    $ 1,228,000.00    $ 200,000.00    $ 1,028,000.00    $ 110,520.00    $ 25,700.00    $ 136,220.00    $ 54,488.00    $ 72,908.00    $ 81,732.00    $ 11.09    $ 85,960.00    $ 135,080.00    7    $ 85,960.00
    1288 Pinetree Way    20-Jan    Eric    $ 1,323,000.00    $ 1,200,000.00    $ 200,000.00    $ 1,000,000.00    $ 108,000.00    $ 25,000.00    $ 133,000.00    $ 53,200.00    $ 65,200.00    $ 79,800.00    $ 11.08    $ 84,000.00    $ 132,000.00    8    $ 96,000.00
    204 #81 Elm Street    20-Feb    Eric    $ 1,028,000.00    $ 850,500.00    $ 200,000.00    $ 650,500.00    $ 76,545.00    $ 16,262.50    $ 92,807.50    $ 37,123.00    $ 41,375.50    $ 55,684.50    $ 10.91    $ 59,535.00    $ 93,555.00    9    $ 76,545.00
    XXXXXXXXXXSchoolhouse Ave    20-Mar    Sam    $ 415,000.00    $ 399,900.00    $ 200,000.00    $ 199,900.00    $ 35,991.00    $ 4,997.50    $ 40,988.50    $ 16,395.40    $ 22,393.90    $ 24,593.10    $ 10.25    $ 27,993.00    $ 43,989.00    11    $ 43,989.00
    XXXXXXXXXXGreenway Pl    20-Feb    Sam    $ 995,900.00    $ 888,000.00    $ 200,000.00    $ 688,000.00    $ 79,920.00    $ 17,200.00    $ 97,120.00    $ 38,848.00    $ 43,288.00    $ 58,272.00    $ 10.94    $ 62,160.00    $ 97,680.00    7    $ 62,160.00
    Como Lake    20-Apr    Crol    $ 822,000.00    $ 850,000.00    $ 200,000.00    $ 650,000.00    $ ...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here