Belinda and Steve Property Portfolio Analysis - Report Example

Belinda and Steve Property Portfolio Report Student Name Student number Date Table of Contents Introduction 3 Discussion and Analysis 5 Rental income 5 Expenses 6 Cash Flow 8 References 10 Summary and recommendation 11 Executive Summary This is a report that is intended to explain the information gathered on the Belinda and Steve property portfolio. Steve and Belinda own three different properties and they have chosen the use of an Excel workbook to store and compute there data. The there are 3 worksheets that use a specific template to compute data for the three properties respectively and a summary worksheet that give a summary of the information obtained from the property worksheets. A discussion on the rental income, the expenditure and monthly cash flow has been done on this report and the statistics used have been gathered from the worksheets. Comparative figures and charts have been included to help understand the economic value of the properties. The report has been divided into sections to create a logical flow of information while explaining the worksheet information. At the end, there are recommendations that may help Belinda and Steve to make decisions that will make them increase their profits and cash flow. Introduction This report has been prepared to help understand the financial situation on the Belinda and Steve Jones’s properties investments. It provides information concerning the income obtained and expenses incurred by on the property investments during the year 2009. The report is based on the data collected for the different properties in different categories. Income is obtained from the rent collected from the property on a monthly basis. On the other hand, there are expenses incurred to maintain the properties and attract more renters. To ensure the properties are well kept there is a routine maintenance which requires labor and materials which translate to cost. Advertising is done incase a property is empty and Cable TV is provided to help attract customer. The rest of the expenses are incurred on property rates, insurance, water rates and appliances in the rental units. A management fee is also charged on all the properties which is 8 percent of the rent charged every month the property is occupied. There are three different properties on Belinda and Steve Jones’s portfolio. One of the properties contains 3 rental units with different rental income. Expenses for these properties are all different as well as the income. For easier management of these properties, Belinda and Steve have opted to use an Excel workbook which will automatically compute raw data fed into it and provide a summary if the information. There are different worksheets for the properties and a summary worksheet where information regarding all the properties is summed up and acted upon. Pie charts have been used to give a graphic illustration of the total figures collected over the year 2009. The template contains formulas which automatically compute the data and generate the pie charts. A discussion on the information produced by these worksheets will be discussed in the latter section as the financial situation of the Belinda and Steve Jones’s property portfolio is analyzed. Discussion and Analysis In the spreadsheet created, the in-built functions AVERAGE, MIN and MAX have been used to perform calculations. According to Office.com (2012), the AVERAGE function returns the arithmetic mean of the number of arguments passed to it. In the investment portfolio, the owners need to know the average amount of money being paid on expenses for a given property and for all the properties when it is due for payment. The MAX function returns the maximum value in a set of numbers. This gives the maximum amount of money for each expense category that is paid for a given property. The MIN function returns the minimum values in a given set of number. The last two functions enable the owners to know the greatest and least expense in a given category. The three functions can be used to project the costs that may be incurred over the coming years. Rental income The annual income for the all the properties is equal to the total rental income for the different properties. Figure 1 below shows the rental flow for the three properties for the year 2009. The total annual rental income for the Parakeet Units property was 29070.00 while the total annual income for 6 Bellbird Circuit property was 15750.00 and finally the 25 Swansneck Crescent property gained 12090.00 income. In total the sum for the rental income gained from all the 3 properties was 56910.00. Figure 1: Rental income in the year 2009 According to the figures and graphical representation given on Figure 1, it will be right to say that the Parakeet units brought in the highest income. The rental income from these units was 29070.00 in total which is more than half the share of the total income received. The pie chart illustrates the share of the Parakeet Units rental income in dark red color. It is visible that the portion is slightly over 50% of the whole share of rental income. Expenses There were expenses incurred for property upkeep which they fell under different categories. The price for each expense varied from one property to another although an average amount could be obtained. Figure 2 below is a snippet of the excel sheet that shows the different expenses incurred and the average amount of money for each whenever a payment is done. The average expense for Cable TV and advertising is 20.00 and 75.00 respectively. Property rates, water rates and Insurance average at 450.00, 127.86 and 195.71 respectively. Routine maintenance, material used for maintenance, appliances and the management fee stand at 160.39, 95.42, 1323.00 and 193.80 respectively. Figure 2: Expenses summary for all properties The largest expense was incurred on insurance paid for all the properties. The total amount for this expense summed up to 9175.00 as shown on Figure 2 above as given in the work sheet. Figure 3 below shows a graphical representation of the expenses for all properties in each category in form of a pie chart. The insurance cost is seen to have taken the majority share of the pie chart illustrating the total annual expenses. Figure 3: Total annual expenses chart The annual expenses by individual properties are best illustrated by the table below. The table gives the total amount for each category of expense as obtained from the summary worksheet. Table 1: Property expenses in each category.  Expenses 6 BellBird Circuit Parakeet Units 25 Swansneck Crescent Material 472.50 396.00 389.88 Advertising 20.00 160.00 0.00 Cable 900.00 900.00 675.00 Water Rates 220.00 711.67 440.00 Appliances   7803.00   Property Rates 1300.00 1783.33 2900.00 Insurance 1500.00 4800.00 2875.00 Routine Maintenance & Labour Charges 780.00 2100.00 1125.00 Management fees (8% of total rent) 1260.00 2350.60 1128.40 Cash Flow Cash flow is measure of a company's financial health. Equals cash receipts minus cash payments over a given period of time (What is cash flow? definition and meaning 2012). Monthly cash flow is given by getting the difference between the monthly incomes less the monthly expenses. The annual cash flow in this case may be obtained by summing up the monthly cash flow amounts. For the property investments by Belinda and Steve, the annual cash flow for the three properties was different since different income and expenditure amounts were involved. The figure below shows the difference between the annual cash flow for the three properties. According to the figures obtained and the chart shown below, the Parakeet units had the highest cash flow with the amount being 11141.80. On the other hand, the 25 Swansneck Crescent property had the least cash flow of 8080.20. The total cash flow amount for the three properties summed up to 28519.50 Figure 4: Annual Cash flow for year 2009 References AVERAGE function - Excel - Office.com . 2012. AVERAGE function - Excel - Office.com . [ONLINE] Available at: http://office.microsoft.com/en-us/excel-help/average-function-HP010062482.aspx. [Accessed 11 December 2012]. What is cash flow? definition and meaning. 2012. What is cash flow? definition and meaning. [ONLINE] Available at: http://www.investorwords.com/768/cash_flow.html. [Accessed 11 December 2012]. Summary and recommendation Data on Belinda and Steve property portfolio for the year 2009 was collected and fed into a worksheet template tailored to compute the data and summarize it. This data is the amounts of money for rental income gained and upkeep expenses incurred during the course of the year. The owners of the properties need a worksheet where they will enter data for each month and at the end of the year get a summarized report of the income, expenditure and cash flow. The summarized report should however be able to provide up to date information when any of the data from the different properties is added during the course of the year. Some charts have been included to provide a graphical illustration of the information on the summarized worksheet. According to the results obtained from the year 2009 summary information, the best performing property is the Parakeet units while the worst performing is the 25 Swansneck Crescent property. According to figure 4 graphical illustration, the Parakeet units have the highest cash flow while the latter has the least cash flow. Considering that during the whole year the property on 25 Swansneck Crescent property was occupied and it still has the least cash flow that means it is the worst performing in monetary terms. Steve and Belinda should raise the rent for the 25 Swansneck Crescent property so that it may increase the cash flow from this property. This move is however dependent on the value of the property and whether it will be fare to increase the rent on it otherwise it may chase away the renters. Read More