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
