Spreadsheets

Your skills are growing! You a learning how to create a spreadsheet model - after you design it, it will do most of the work for you!

We are going to develop a smart spreadsheet model for a property sales company. To be successful in this exercise we will need to be comfortable working with the following spreadsheet-related tips and tricks:

  • opening a csv file in a spreadsheet application and making all of the data visible.
  • entering and removing data from the spreadsheet.
  • using copy-down and knowing when to fix cell references using $.
  • using a variety of different spreadsheet functions such as sum, count, max, sumif, countif, vlookup.
  • creating graphs and charts.

We will also look at how to create a spreadsheet that make its own decision about which value to display in a cell. We will investigate the if function.

Download the two data files. One is a live data sheet and the other contains location lookup information.

locations

property sales
Open the two data files in a spreadsheet application. To help you manage the problem, it may help to copy and paste the location data into a new sheet in the property sales workbook. Use a lookup function to display the correct location in the Location column of the property sales sheet. Use the data in the Area Code column as the lookup value and the location data as the lookup table. Copy down your formula for every row in the main spreadsheet.
In the Price/sq ft column, create a formula to calculate the price per square foot of the property.
In the Sales Commission column, create a formula that will calculate the sales commission. Use the following rule:
  • If the property has an area of less than or equal to 500sq ft, the Sales commission is 5% of the price.
  • else the Sales commission is 7% of the price.
Note: you may have to do some research to figure out how the IF function works with spreadsheet applications.
Copy down your formula for every row in the spreadsheet.
Complete the Cost to Customer column. This will be the Price added to the Sales Commission. Copy down your formula for every row in the spreadsheet.
Calculate the total cost of all properties for sale in the cell indicated.
The local government is considering buying properties in the following 3 areas to help cope with the rising number of immigrants fleeing oppression from neighboring states.
  1. Kowloon Bay
  2. Tin Shui Wai
  3. Diamond Hill

Analyse the spreadsheet and produce a bar chart with an appropriate title clearly showing the number of beds currently available in the 3 areas listed. You will write a letter to members of local government to let them know the result of your data analysis. The names and addresses of the recipient can be found here:

Make sure each letter is formatted as a business letter. They must include:

  • the recipient's address
  • your address
  • today's date
  • the letter explaining the results of your analysis
  • a screenshot (or other) of your chart
  • a word in your letter which hyperlinks to this website - https://www.hongkonghomes.com/en

Contact information for the officials can be found here.

City Officials

Make sure you save your spreadsheet and a file containing each individual letter that is generated.