Monday, March 26, 2012

Personal Business Management Spreadsheet Template


We’re in the middle of tax season here in America so it comes as no surprise that some of the most requested Excel spreadsheets this time of year are personal and business finance and accounting templates. In my everyday life I use three primary spreadsheets to help track my finances and will be writing a post about each one.

First up is what I like to call my business accounting spreadsheet. I call this a “business” but it’s pretty basic seeing as how I’m the only employee. Maybe a better name would be web site management spreadsheet or even personal project management (or project tracking) – because that’s what this is, a personal project, after all!  Also please note, this template is relatively new and is continuously evolving as I add new features to my web site and want to analyze the data in new ways.

The Concept

So what is this so called business or project? This blog uses the Blogger platform, which is free to use but requires .blogspot to be added at the end of the domain name. I’ve always wanted to create my very own web site and finally did so - how to learn to write CATIA macros. The site contains several free articles with tips and advice about VB scripting in CATIA, a 3D CAD program.

However, creating and maintaining my own web site costs money. I decided to treat my site like a business. I keep track of all expenses and revenue because the goal is to have the site pay for itself through the sale of an eBook I wrote on the same topic. If the site is not profitable over time I will abandon it. I guess you could classify this type of web site as a “niche profit site.”

Total Expenses

The first sheet I have in my template is labeled Total Expenses. This where I keep track of any products or services I have to buy to keep the web site up and running, as well as the initial start up fees. For example, I purchased the domain name www.scripting4v5.com through NameCheap at $10.87 for an entire year. In the month column I use the MONTH function to return the month of a date as a number, which will be used later on in my monthly report worksheet. I use HostGator (exceptional customer service – I speak from experience!)  to host my web site, a monthly expense.


For my CMS (content management system) I decided to go with Wordpress because it’s user friendly and free (thus not included as an expense). In addition, I purchased a new theme called Socrates due to its number of built in features which again are very easy to use. The onetime fee is added to the expense sheet.
In order for customers to purchase and download my eBook, I needed a way to protect the download link so it couldn’t be copied and shared with other users. I bought a program called WP File Lock one another onetime fee of $47.

Finally, I use an email newsletter service called Aweber to manage my email subscribers. This service is a monthly fee of $16.33. That’s it for my expenses. I know it sounds like a lot but really I only have two only monthly bills (email newsletter, and hosting) and one yearly expense (domain name).

Total Revenue

Now, let’s look at the next tab in my workbook, Total Revenue, where I list all my revenue generated from the web site. At this time I am using Google Adsense to place one banner of ads across the top of the site as well as Kontera ads within the text.



The main revenue stream is from selling VB Scripting for CATIA V5 eBook. I have a referrer column to indicate whether I sold the eBook or if it was sold through one of my affiliates. Yes, if you have a Clickbank account you can earn a 50% commission for selling my book for me!

Once again, I use the MONTH function to return the number of the month, as in cell F2, =IF(E2="","",MONTH(E2)). At the bottom of the sheet I add the totals for each of my site revenue streams, as in cell B20 I have =SUMIF(A2:A15,A20,D2:D16).


Monthly Report

Finally, on the third sheet I can look at my total expenses and revenue by month. This gives me a great snapshot of how the site is doing. I use the SUMIF formula on my Monthly worksheets where I can view total expenses, revenue, and if I have made or lost money for the month. For example, in cell B2,
=SUMIF('Total Expenses'!F2:F9,2,'Total Expenses'!C2:C9) I also use conditional formatting to highlight when I've spent more money than I’ve made in red and highlight the text in green when I have made a profit.


Summary

In review, I was able to setup my first web site at an initial cost of $209.11, shown here on my spreadsheet. My expected monthly recurring expenses are $26.28. So now what? The purpose of the site is to sell my eBook. When a sale is made I add it to my revenue column. It will take a few sales to cover my initial expenses but then I should only need to sell one eBook a month in order to pay for the site every month.

Sorry for the long article, but that was my “business” spreadsheet in full. Next, I’ll cover the Excel spreadsheet template I use to track all of my real-world and online income, then we’ll look at how I keep track of bills and other living expenses.


*Full disclosure: Some of the links in this article are for affiliates. I earn a commission if you purchase the product having following the link. I only name products that I actually use and fully endorse.

·         Tags: personal finance excel spreadsheet, monthly finance spreadsheet, Free excel project sheet