Visual Display of Operating Expenses Using Excel 2010
I was teaching in the college computer lab last week when a student asked for help with his Excel worksheet. He had created a pie chart and wanted to print his worksheet and pie chart on one page. We adjusted the size of the pie chart and reduced the page margins, but it was going to print on two pages not one. I asked the student if he really needed a pie chart. He said, “Yes, how else am I going to visually show the difference in values without using some kind of chart?” I told him, instead of a chart, he could use conditional formatting using data bars. With conditional formatting using data bars you can create visual effects in your data that can help you see how the value of a cell compares with other cells.
When I got home that night I thought, how would this apply to real estate investing? I looked at some of the calculations used in income property analysis, namely operating expenses in an annual property operating data worksheet. Usually the operating expenses are displayed from the percentages of the gross operating income. Below shows an example of operating expenses and its pie chart using an Excel 2010 worksheet.
Please note that these numbers are just for demonstration purposes.
My next step was to take the operating expenses data and apply data bars using the conditional formatting feature in Excel 2010. The result was a worksheet showing data bars in the operating expenses column. The data bars make a dramatic visual of the value comparisons in the operating expenses.
Conditional formatting also allowed the student to display his data without the need for a pie chart and he was able to print his worksheet on one page.
Will this eliminate the need for charts, absolute not. Conditional formatting is another feature that can help you display value ranges. Note that this example only showed one feature of conditional formatting used in Excel 2010.
Conditional formatting is worth investigating. If you have more than one way of displaying data, it gives you more flexibility in your presentation of value ranges. In real estate investing, we know it is about the numbers. But add a dramatic representation of those numbers in your analysis to a private investor or lender could make a difference. As I always tell the students, your computer is a tool for you to use. Always look for various options in representing your data.
Comments (3)
Very cool! It's always good to learn new features in a real estate investors favorite program, Excel.
Kevin Kaczmarek, almost 14 years ago
Nice article! I'l have to check that out!
Bryan Hancock, almost 14 years ago
Excel is a very versatile tool. As an accountant I use it Excel regularly to create new spreadsheets and to help me understand financial data.
Account Closed, almost 14 years ago