You have lots of choice when it comes to computer programs for the business minded. But for me, Excel is the best. It provides a relatively simple way to create your own visual spreadsheet — such a cost analysis spreadsheet for a specific crop — that is clean and calculated. This article will show the basics of using Excel and how you can then use this program to calculate and display costs of production for a farm.
THE IMPORTANT FUNCTIONS
Excel in its basic form is a spreadsheet with rows and columns of information inputted and edited. The rows are marked alphabetically over the top of the sheet and the columns are marked numerically down the left side. When you combine these, this identifies a cell or one unit that holds information marked by a letter and a number such as A4 or B16.
One of the strengths of Excel is that information in these cells can be changed with the click of the mouse and the program automatically and instantly changes all the other numbers to adjust for that one change. As a simple example, say you’ve got a spreadsheet showing inputs for a specific crop. If the price of fertilizer goes down, you change that cost at the top, and Excel automatically adjusts the total on the bottom. More on that later. Let’s start at the beginning.
The key differences between an Excel spreadsheet and one you create on a piece of paper is that you can easily insert and delete information in Excel without making a mess or having to start over. And Excel does the math for you.
To write inside a cell, simply click on the cell space and start typing. When you’re done, press “enter” or click on another cell. Sometimes the cell is too small for the information given. Two things can be done to relieve this. First, you can put the mouse pointer on the line at the top of the cell. The pointer will then turn into a double arrow, and you hold down the left mouse button and move the mouse to widen or narrow the cell. Or you can use the “wrap text” feature, which is found in the toolbar. The toolbar is the section of the program above the sheet that contains different icons and buttons. When this feature is enabled, the cell automatically extends downward as you type more words.
Making sense of the toolbar. There are some basic functions that, once learned, can make the Excel experience go much better. You’ll want to take some time to play around with functions and experiment. To find out what a particular button does, just hold the mouse pointer over it. A box saying what that function does then comes up.
With the basic toolbar, you can change the font and how it looks as well as how the text is used — such as text wrap — as well as line spacing features.
The next important toolbar section is the “number” box. This is the section in the middle of the toolbar that starts with the number sign icon. Agricultural spreadsheets usually use many different numbers, and with these functions you can manipulate the numbers all at once or set them to look a certain way for the given cells. I use the “$” button a lot. This button changes all current cells to a currency format. You select all the needed cells by clicking on one, or clicking and dragging the mouse across many. Then go to the toolbar and press this button with the mouse pointer. Cells are automatically changed over to currency.
The number can have decimal places added or subtracted as well as having commas added to them with the click of toolbar buttons. The “%” button turns a numbered cell into a percentage.
Inserting or deleting cells. Say you’ve got your whole spreadsheet done and then decide you want to add a new cost. Instead of redoing the whole thing, take the mouse pointer over to the side and place the point over the spot between the cells. The double arrow will come up again but this time push the right click button on your mouse. A small menu will come up with the insert and delete sections that can be clicked to add (or delete) a cell.
Create a border. I use the border function to make my spreadsheets look a little cleaner. This button, found in the “font” box, looks like a cell that is quartered with a dashed line and then one side of the cell is bolded. Sometimes you want to highlight titles or totals with a bold box. To do this, just click the cell you want to have a border. A small menu will come up with the selections for which side of the cell you want to border. Just select what you want and the cell is then bolded in the appropriate place.
Auto sum makes addition much easier. The “auto sum” button, found in the “editing” box at the far right of the toolbar, looks like an M that is rotated to the left. To use this, select all the cells you want to sum together. Then press the auto sum button and these numbers are added. The sum is put at the very bottom of the numbers.
Another great thing about Excel is the “undo” button. This button is a curved arrow at the very top left of the toolbar. If you make a mistake that has a negative consequence on the current sheet, all you do is press the undo button and it removes the change taking you back to where you were. These functions make Excel fast and easy to use.
Create multiple spreadsheets. You can have many spreadsheets going at the same time, say one for each crop. You can give each its own file name. The bottom tab has all the open spreadsheets listed, and you simply click on the tab to view that sheet. It’s like shuffling a stack of paper.
A BASIC EXAMPLE
Now we’ll look at a basic example that shows the cost of production for an operation. Follow along by looking at the sample spreadsheet on this page. We’ll build this spreadsheet using the things we learned above. I’ll also explain how to create a basic mathematic formula.
First thing is to title the sheet. Go to cell A1, click on it and then type in the name of the operation or farm. This name is usually larger than the cell so the cell needs to be wrapped or expanded. To make it stand out it, click the “B” on the toolbar. This creates bold text.
Now down the left hand side in the A column we will put in all of the titles of the information that we will be using. You can make this as extensive as possible. The more costs the better understanding you’ll can gain of the operation, but for this example we will keep it simple and not even divide the costs into variable or fixed costs.
We start again with a bolded heading called “costs.” Under this we will put seed, fertilizer, machinery and labour in their own cells. To the direct right of these headings we will enter the corresponding costs. In my example, I just put in general random numbers. To format them as a dollar figure, select them all by clicking and dragging the mouse over them and then press the dollar sign button in the toolbar.
Next, under all the costs leave a one-row space to create the total. Create the label for “total cost” under your list of costs. Then select all the costs in column B and press the auto sum button. This adds up all of the costs instantly and puts the total in the cell in line with your “total cost” label. To create a line above the total cost, follow the instructions above to create a border. Instead of choosing a border around the whole cell, just choose to have a line at the top of the cell.
Next we will put in our acreages, including total acres, seeded acres, and fallowed acres. We create these the same way as we did the costs, with the titles in the A column and the numbers in the B column. Once again we can auto sum these numbers if needed.
Now we will create a section to show the costs per seeded acre as well as cost over the total acreage. So as always create a bolded title. I called mine “cost totals.” Now we get to use the math function of Excel. First click on the desired cell you wish to put the total in. For me it was B17. Once this is selected I go to the top of the sheet to where you can type in information in the text bar (which is the long blank space at the bottom of the toolbar.) Whenever you are creating a math formula you start with an = sign. So I type in “=B9/ B12,” which says that cell B17 will show the division of cell B9 by B12.
For a short cut, just type “=” then click on cell B9 then type “/” then click on B12. You will notice when you click on a cell it is highlighted by a collared border. This can become more important as you go because you can trace formulas between sheets and these collared borders allow you to see where all your numbers came from.
My Excel automatically put this operation into currency and created a cost per seeded acre of $73.33. Now we will do the same thing for the total acreage. We just do the same thing and have “=B9/B14” creating a cost of $44. So now we have simply set up a cost per acre scenario for these 5,000 acres. The great thing about Excel is that if you now change any of these numbers, the change will be reflected in any total it affects. This is because you have set it up using numbers and formulas. As soon as B9, B14 or B12 changes, it will be shown in your costs formula.
I’ve shown you a very basic example to get you started. You can make it as complicated as you want, extending over many sheets for all sorts or costs or feasibility scenarios. All it takes is some practice and patience and you’ll be an Excel wizard in no time.
Jay Peterson farms near Frontier, Sask. He graduated from University of Saskatchewan in 2008 with a Bachelor of Science in Agribusiness.