I believe this is a very simple project
I currently have a range of data that needs to be analysed on a 6 monthly basis and then graphed. Which we then use to create 9 reports of various geographical precincts. At present the files are satisfactory and I have done the best with my limited excel skills but I am hoping there is someone available to clean the spreads and possibly make them easier to use/more efficient.
What we are analysing is a range of industrial property information. We currently have 3 excel files 1) MASTER SALES 2) MASTER LEASES AND 3)MASTER LAND. These files include a list of either improved sales, leases or land sales broken down into 3 month periods. THIS NEEDS TO BE CHANGED TO 6 MONTH PERIODS JAN – JUNE AND JULY – DEC) This information needs to be manually entered into the spreadsheet as it generally comes from various sources with varying levels of information available. From here these master files automatically compute what geographical micro corridor each entered suburb corresponds with. (i have done this using an iferrorvlookup formula). (see column F). From here, we need to sort this information by micro region and then copy the number of sales/all relevent info for each of the 9 micro regions (ATC, SOUTH, OUTER SOUTH, NORTH, OUTER NORTH) ETC into their individual excel files.
2) each individual excel file for each of the 9 micro regions has 11 tabs. 4 of these tabs are the most important. Sales evidence, leasing evidence, supply and land. (for now ignore supply). I currently will only provide you with 1 micro region (ATC) to work on.
the sales, leasing and land sales info is pasted into these respective tabs. The following tab to each evidence is a CH tab which is a graphical representation of the data once analysed.
3) each of these 4 tabs need to be analysed to chart differing information. This is my primary concern! At present we are using various tables either to the side or bottom of the data and then charting this info. I find this process messy with the ability to be inaccurate as I am using count, sum, average for majority of my analysis.
PLEASE NOTE: AT PRESENT ALL SALES INFO IS BROKEN DOWN INTO QUARTERLY PERIODS THIS NEEDS TO BE CHANGED TO REFLECT 6 MONTHLY PERIODS FROM JAN – JUNE AND JULY – DEC
I will first explain how the SALES EVIDENCE tab needs to be analysed.
Sales evidence needs to be broken into the number of sales by particular price brackets. I.e. how many sales occurred that were under $1 million, between 1 -5 million etc etc. Also, total volume ($ amt ) of sales is also analysed along with the average yield for the respective period.
Located on columns B – I and Rows 163 to 194 is a clear example of how we need this sales information analysed. The following tab CH – sales evidence also indicates what information we then use to graph.
Leasing evidence needs to be represented as seen in columns Z – AA rows 5 – 8. (basically the average rent broken down into special configurations). The colours in the spread represent each size category which are currently manually computed. I would like to see this automated. This table is then graphed in the following tab.
Supply – This data is not broken down into 6 month periods and new data is pasted into the tab each update. The info needs to be broken down as seen in columns B – E and rows 44 – 60.
Land – similar to sales. Needs to be broken down as seen in columns d – e and rows 56 – 58. (average lot size).
(see notes tab for additional info)
WHAT WE ARE TRYING TO CREATE:
A neat, tidy, efficient spreadsheet where new 6 monthly data can be passed from the master to the individual excel file. The more automated the analysis of sales info, leasing, supply and land data THE BETTER!!
It would also be especially beneficial if the data could be presented on a historical basis in a table form of some sort. This is so we can compare (for example: average rents from jan to jun 2010, jul to dec 2010, jan – june 2011 and so on).