Lesson Four

Using Excel to keep your business under control

Some of you who have a personal assistant or someone who already does your bookkeeping for an off line business may wish to skip this lesson.

But for anyone that would like to learn some simple methodologies for keeping your accounts and records in perfect order in your new business I think you will get quite a lot from this lesson.

The Guru was aware that in my day job I have access to a personal assistant but even with that being the case his advice was that I should really brush up my knowledge on excel as it would make my year end tax simple and give me a thorough daily control sheet for each area of my new business.

The advice has proven invaluable and at least a couple of times a week I pick up anomalies in areas like -

(1) A missed commission that an affiliate program has not paid me;

(2) An overcharge that an auto responder or hosting company has tried to put through;

(3) A good or bad trend becomes evident and allows me to take appropriate action;

Soaring pay per click charges is a recent example that comes to mind.

So basically in this lesson I will assume you do not use excel in your day job and I will provide some very basic tuition as well as showing you visual examples of the kind of records that I keep.

Keyboard Shortcuts

Getting around excel with a minimum of fuss is one of the main battles so if you understand your shortcut keys, you can achieve quite a bit.

And please think about something else, this journey you are embarking on is hopefully a long one and you are not aiming to become a short term "quick buck" artist.

o even if your on line career starts slowly and you only have a few sales and expenses to log in the early days, setting up the correct structures in any business will hold you in great stead for the future.

The following shortcuts will enable you to set up your data and records in the style you like -

Ctrl+ home always moves you to cell A1

Ctrl+ b makes data in the selected cells bold.

Ctrl+ u underlines data

Ctrl+ I italicizes data

Ctrl+ a selects your entire spreadsheet

Ctrl+ z undoes your last action

Ctrl+ y repeats the last action

Ctrl+ Space bar selects your entire column

Shift + Space bar selects the entire row

Shift + F5 displays the Find dialogue box

Shift + F2 edit a comment

Shift + Tab confirms a cell entry and moves one cell to the left

Alt    + F4 closes the excel program

Alt    + F8 run a pre-recorded macro

Alt    + Return start a new line in a cell

I will explain some simple little spreadsheets that I use to keep my business completely under control.

(1) Type a title such as Sales Ledger on to your spreadsheet in cell

A1 and then add the column heading Date in one row then in the amount  column across in row E as you can see I have also added columns paid by, method etc.

I have also highlighted the far row in Yellow for any notes that I need to be conscious of.

(2) When you click on the Auto Sum button, excel inserts a SUM function in the selected cell. Between the brackets, excel expects to see some information telling it which cells contain the values you want to be added together. Click and drag from cell C4 to C8 and then press return.

(3) Excel now adds up the values in your sales column and displays the result in cell C2 .If you select cell C2 and look in the Formula Bar, you will see that C2 actually contains the Auto Sum function '=SUM(C4:C8)'. We have placed the totals above the data so you can add more entries to the bottom of the list easily without having to insert extra rows.

Creating a Company Ledger account in excel is simple if you follow the steps below.

 Step 1 Create an Expenses Ledger in [6 easy steps]

(1) Click in cell A1 and type 'expenses ledger'.  Format the text as bold and change the font size to 14pt using the tools on the formatting tool bar. In row 4 types the headings date, inv, code, item, amount, totals, method and notes.

Make all headings bold.

(2) Format the amount column as a currency with the excel currency symbol by clicking on the top of the amount column and then highlighting the entire row.

The open the format cells dialog box by clicking on the format menu and then under the numbers tab, choose currency from the category list. Select the dollar symbol from the list under symbols displayed and then click on ok.

(3) You can now add some data- type a date in column A and a description such as phone or hosting across in the item column.

(4) In column C we can allocate a single letter code to each category in this example I will use P for phone, F for fax, S for server.

In the code column type a reference number for each item you anticipate having for this example I will use 1, 2, 3 to keep things simple whereas in my real ledger I use the 101,102,103 style number like in traditional bookkeeping.

(5) The paid column should contain an abbreviation that represents how the payment was made or the full word if there is the room in the column.

This will enable you to check your accounts against your check book stubs and your bank account and credit card statements. Finally you can add some informative notes to the 'notes' column.

(6) To total the amount column, click on cell E3 to select it. Then click on the Auto sum button, select cells E5 to E50 and press return. This totals your column.

The simple example below shows how easy it is to create an ordered system to refer to as your business grows.

Create a Sales Ledger Below

A sales ledger keeps track of all the sales that you make and although there is no real need for an item code we will still use the column Project and assign a project code so that we can split the income by project on a summary sheet.

1. Type the title Sales Ledger in cell A1 and then enter the columns date, inv, code, project, amount in row 4.

2. Format the title. Type the Column headings and the cells in the same way as your expenses ledger.

 3. Add Auto Sum to your amounts column in exactly the same way you did in your expenses ledger.

Step 3 - Add an attractive chart with chart Wizard so you can simply enter your data and look at your stats at a glance.

1. Simply select the data you want to be included in your chart including the headings but excluding the last total cell. Click on the chart wizard button on the standard tool bar then simply click on the Finish button. Excel will insert a simple bar chart on top of your data showing how your expenses vary from month to month.

You can move your chart around easily by clicking anywhere on the white area around it and dragging it across your spreadsheet.

In Lesson 12 we will show you how to use screen capture tools to keep an on line diary of this to look back on as your business grows.

In closing out this section my advice would be to start with a simple spreadsheet of sales and expenses and to also invest in a book on Microsoft’s Excel or another program of your choosing.

Quite often in business people will give you a hint but only your discipline and conscious effort to learn will enable you to take a simple idea and expand upon it.

The Guru showed me his excel records log but I then went out and purchased a hard copy book by Microsoft that gives me these and about 100 other tips on creating spreadsheets for my business.