P – Prepare the Data

Now that we have gotten the data it is time to move on to the next step. The data that we have right now is somewhat messy, and not quite ready for analysis.

Messy

There are several issues here that needs to be addressed, and that is what comprises the second step, P – Prepare the data.

In this particular case, the export has carried a period as a place holder, which makes Excel recognize this number as text. This is not useful at all, and we should convert this to a usable form. A basic “search and replace” will do in this case, where we highlight columns D and e and search for “.” to replace it with a blank. This will make Excel realize the error of its ways, and read the data as numbers.

Next, we should use the built in table function in Excel to make this collection of data into a table. Simply highlight a cell in the table and hit Ctrl + L and you should have a table. In my particular case there were no column names in the data set, so I am going to give each column its own identifying name to help ease the process.

Table

This now looks a lot better, but there are still a few more things we need to take care of. Since we want to make a budget of our expenses, we need to make sure we record what our expenses are and also divide them into different types of expenses. For starters, let us divide expenses into three different categories; fixed, variable and savings. Yes, savings count as an expense, because it is removed from your spending account. So let us create an additional column which we will call “Type”, and manually record the type of spending we have. To save time, let us use “F” for fixed, “V” for variable and “S” for savings. Additionally, we should use “I” to denote income, so that all rows have their designated letter.

Note:  Money transferred from your savings account to your spending account should be labeled “S” and not “I”, since this is essentially a negative saving for that month.

Table_with_type

And this basically concludes the steps needed to properly prepare the data. However, this data could be further prepared – or serve as the basis for a further in-depth analysis later – by adding another column called “category” where we could specify what the spending was on (power, mortgage, food, vacation etc.) This is particularly useful for companies, and I would highly recommend it. For our current purposes though, just adding the type will suffice. We have now prepared the data and are on the cusp of actually analyzing it.

This entry was posted in Basic analysis, Excel, GPAP. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s