Farm Financial Wellbeing Worksheet walkthrough
November 8, 2024
In this video, MSU Extension farm business management educator Florencia Colella provides a walkthrough across a sample farm financial wellbeing worksheet.
This material is based upon work that is supported by the National Institute of Food and Agriculture, U.S. Department of Agriculture, under award number 2020-38640-31522 - H008917110 through the North Central Sustainable Agriculture Research and Education program under subaward number LNC20-437. USDA is an equal opportunity employer and service provider. Any opinions, findings, conclusions, or recommendations expressed in this publication are those of the author(s) and do not necessarily reflect the view of the U.S. Department of Agriculture.
Closed Captions may be turned on by clicking on CC at the bottom right of the video.
The video transcription can be viewed, printed and downloaded at the link below.
“Enthusiasm” music by Jay Man is licensed under CC BY 2.0.
Video Transcript
Hello, and welcome. My name is Florencia Colella, and I'm a farm Business Management educator with Michigan State University Extension. In this video, I am going to walk you through our Farm financial well being worksheet. This worksheet is a Google Sheets file that you can get access to by typing this link on your browser. When you open it, you will notice that this is a view only file. This means that you wouldn't be able to type anywhere in the file. That is okay because this is a publicly accessible file and we don't want anyone's personal financial information on the Internet. What you have to do to use this file for yourself is go to file and either make a copy and you will be able to store your own version of the file in your Google Drive in whatever folder you choose. I am going to make a copy for myself. Or you could download it to Excel or as a PDF file. Or you can even print it out and fill it out by hand. Let's assume that we're going to use it in Google Sheets. I've already made a copy of it, and as you can see, I am now able to and it will stay. We really only want to enter information in the gray cells though. Wherever there's a gray cell, that means that information can be reported. You'll see that there's lots of different tabs here at the bottom. Alpha show you the reports tab. These are all of the results that you would get once you enter information. Here at the top, we have the year of analysis, and you do need to enter what year you're analyzing so that you get the reports back. Let's assume that we're going to. We're going to analyze the 2023 year. There's lots and lots of different reports that you get in this tab. The most important one, in my opinion is the Farm Finance score card. The Farm finance scorecard shows you several different financial ratios that make it super easy for you to compare your financial performance from year to year or against other top or average farms. There's databases where you can find average numbers for different regions of the country or for different types of farms, and we can talk more about that in a separate video. But the first step to be able to compare yourself is to get your own numbers. You can see that ratios are color coded, and the ratio for your farm will be colored according to whether you're falling in the vulnerable caution or strong categories. This section up here is about the cow calf operation. We can create enterprise budgets for lots of different profit centers. But if you are a cow calf operation, then this has been designed specifically for you, and you can get break even prices for the different products that you sell. There's three break even numbers from less to more conservative. This next table shows you a cow calf enterprise budget. The enterprise budget is an economic analysis. It accounts for different sources of revenue, including cash and non cash sources. For example, the Hafer calves that you retain to replace cows in your herd, you could be selling them. This cow calf budget takes into consideration those real cash revenues on the left, but also non cash revenues. It does the same for the costs. It takes into consideration real costs, but also theoretical costs. For example, feed and hay, They show us costs here, even if you are growing your own, because if you weren't, you would have to buy them, and so there's an opportunity cost to the time, money, and effort that you spend on those farming activities. Then this table comes up with a total gross economic revenue and a total cost. And then it calculates the income over variable costs and the economic profit. The economic profit accounts for both variable and fixed costs. This is typically going to be a lower profit than your accounting profit because it takes more costs into account, which are the opportunity costs. But it's a safer number. If you are making an economic profit, then you are completely safe. Another important section here, is the accrual adjusted income statement. This is also called the calculation for the true net firm income or the accrual adjusted net firm income. This takes into account not just the cash income and cash expenses, which are here. But it also takes into account inventory changes. It makes adjustments for those inventory changes. So you could be cash poor, but you could have increased your inventory. And this income statement takes that into consideration, and it gives you a true net firm income, which again, might be higher or lower than your cash income, but it's more accurate. These are all of the adjustments that it makes. At the very top, there is a net cash income value. Right here. And this is just the cash income. It does not take into consideration those inventory adjustments. And so here there's a few more cash based measures, including a cash accuracy check that tells you whether the numbers that you entered are adding up or not. And so you really want to look at this discrepancy number. Make sure that the discrepancy is low. We also have a comparative networth statement, which shows all of the assets at the top, liabilities at the bottom, separated by current intermediate and long term, and it compares this year and last year for both the cost values and the market values. Cost values are depreciated values, and market values are what you could be selling stuff for at the at the time of the balance sheet. That's a summary of the reports tab. Assuming this is a report that you think you might want to have, let's look at how you would enter your information. First, there is a household tab. The project that funded the creation of this file analyzed farm financial well being as a whole, including both household and farm. It is set up such that you can enter a bunch of household financial information and then on the reports tab in the scorecard. You get some household metrics. I'm not going to go into much detail about the information in the household tab, but let me know if you have any questions. I do want to spend a little more time on the farm and ranch tab. There's 12 sections in the farm and ranch tab. Miscellaneous overhead, homegrown feed hay, cover crops, grain, and other crops, purchase feed hay and supplements, veterinary medicine and ID, including AI, labor, livestock, land, buildings, and improvements, machinery and equipment, title vehicles, fuel oil, and utilities, and cash. Each section will always have an orange table, a green table, one or more blue tables, and one or more purple tables. Orange tables are always for outgoing dollars. They are your cash expenses. Green tables are for incoming dollars, so income or cash inflows. Blue tables are assets owned, and purple tables are liabilities. So loans, unpaid bills, those sorts of things. A couple of things to note here. First would be these solid lines. We have several types of cash expenses, including interest paid on loans, repairs, and leases. And under the solid line, we have capital purchases and improvements. Again, separated by a solid line, we have principal paid on loans. The reason there are solid lines is that the first few are operating cash expenses, but capital purchases, and principal paid are not regular operating cash expenses. In the first case, they are an investment or in the second case, it's just money that you were given and are now returning. Those are just different types of expenses and that's why they are separated. Whenever things are a different type, there is going to be a solid line throughout the file, and the same will happen in the incoming dollars table. We have cash income at the top. For example, if you were leasing out a piece of equipment, but that's different from selling a piece of equipment that you've owned for a while. And it's also different from loans received because that's not really your money, it's just being lent to you. You'll see that there's always a beginning and ending line for all assets. Because we're going to be comparing your beginning and ending net worth. We typically ask for a cost value and a market value. Cost values show how much you spent on an item when you first bought it. Market values are what you could sell an item for today in the marketplace. There's this third column all the way for estimating more or less how much of the expense or the asset is for the cow Calf operation. Another thing to note is that we had said that gray cells are those to be completed, we see some white cells here that I can't fill up fill out. Why is that? Well, that's because machinery and equipment, as well as titled vehicles and buildings and improvements are all what we know as depreciable assets. They lose value throughout their life, and so we need to account for that. You will notice that the beginning and ending cells for buildings and improvements machinery and equipment and titled vehicles are all white, as well as the lines for sales and purchases of these assets. These here, and these and these. Again, that is because for these three types of assets, you do not need to fill out this information in here, but in the depreciable tab. We have a special tab for those types of assets, because we need some detail for those types of assets to be able to make those calculations for you. So let me give you an example. I've entered a few pieces of equipment here. We have a tractor, Hb rake, Baylor, Bill rapper, Hywagon. We also have a titled vehicle and a few other pieces of equipment, cattle trailer, loader, tractor, all rake, flatbed trailer. For each of these pieces of equipment or for each of these assets, we have the year they were purchased. How much we purchased them for. And for one of these items, well, we actually have two items that we purchased in 2023 and one item that was sold in 2023. For the item that was, bought in 2023 or for the two items that were bought in 2023, we do not have beginning market values, and we don't also have a beginning cow calf percentage. But we do have their market values at the end of the year and how much we are like to what extent we're using them for the cow calf operation. And for everything else, yeah, for everything else, we have beginning and ending values. And for the asset that we sold in 2023, we only have its beginning market value and Calf percentage, but we do not have anything for the end because it just was not at the farm anymore. Now that I have entered all of this information, if I switch over back to the farming Ranch tab, you will see. I didn't enter any buildings and improvements as if I don't own any buildings and improvements. But for the machinery and equipment, it pulled from the depreciable tab, our purchases, our sales and our beginning and ending inventory. We also had a title vehicle, and so it wasn't purchased or sold in 2023, but we do have the beginning and ending values here. Both the cost values and the market values cost values are calculated automatically through the spreadsheet does that automatically with the information that you entered. Okay, let's take a look at the purple tables now. Purple tables are liabilities, money that you owe. For example, here we have some liabilities that are not loans. They are just unpaid repairs, rents, or leases. And you would enter that here again with a beginning and an ending value. This is an example of the information that we need for loans to be able to calculate your liabilities for your balance sheet. There's a space for a description and I'm actually going to enter a loan for the end of the year because there's a piece of equipment that I bought here. In 2021 of these two pieces of equipment that I bought in 2023, I actually took a loan for. I'm going to enter the loan description here at the end of the year. I'm going to enter hey equipment for the description. I'm going to enter the interest. I'm going to enter how many times I pay this loan in a year, how many years there is left. When I paid the loan for the last time and how much I have left to pay. And also for each loan, we also ask that you please fill out how much of that debt corresponds to your cow calf operation. In this case, I'm going to say it's 50%. While we're here, let's fill out some cash expenses in these sections. So for my buildings and improvements, I have some repairs that I made. Then I also have some interest that I paid on machinery and equipment loans. So repairs for my machinery and equipment, and some principle that I paid back on that loan. I also made some repairs to my vehicle. And if I keep scrolling, I'll be able to enter my expenses in gas, oil fuel, in some utilities. I also have some records of distributions that I took from the business, so I'm going to enter those here. And I'm going to enter my beginning and ending cash balance. And for everything that I enter, I'm trying to estimate more or less how much of that is ed or going to be used or corresponds to the cow calf operation. All right. So we've reached the bottom. Now we're going to scroll all the way up. So we have several miscellaneous expenses. And some sources of income. Then we can just go straight to the home grown feed hay cover crops, grain and crop section, where we also have some chemicals expenses, fertilizer and lime and seeds and plants. We did not have any miscellaneous assets or liabilities. We do have some income from the sale of hay. In this section here, we are going to do some reconciliation of our home grown hay and inventory. So we enter our beginning market value, how much we grew, how much was fed or sold or shrunk, and how much we had at the end. Let's stop for a second and ask ourselves, what's these yellow tags? These are the cells that ask for opportunity costs. For our enterprise budget, we want to calculate how much you're saving by growing your own hay or feed. We're going to estimate the value of the beginning inventory, grow, fed, sold, shrunk, and the ending inventory. If I keep scrolling, I am going to be able to enter some feed purchases. In some veterinary medicine and ID expenses. In this case, artificial insemination expenses, and you'll see here that sometimes we will ask for numbers, for example, here, how many females were inseminated. Please when you see those, please fill them out as we really need that extra information to be able to calculate things. If I keep filling these in, some custom hire that I contracted out and some custom machine work that I did. And here there's another example of an opportunity cost. This is my estimation of the value of my time and how much of my time I'm using for the cow calf operation. Whenever you have questions, we have these little question marks and you can make them bigger, and there's explanations throughout the file of how to interpret things or how to complete things. Okay. But let's keep filling this in and so we are now in the live stock section. We have some cash expenses and some cash income. We ask for number of head here throughout the Live stock section quite a bit. We also have a section for our capital animals, animals that are breeding or used for the production of other products, like milking animals, for example. And so we have our outgoing dollars, or incoming dollars. Here, we have another example of an opportunity cost because we may not buy bulls every year, but if we typically buy bulls, we fill that in. And then we have the asset section. So we the different categories, how many animals, and the market values that we own at the beginning and end of the year. We only need the detail for the end of the year here. The same for breeding, milking, and draft livestock. Let's now take a minute to talk about the livestock reconciliation tab. Again, this was designed for cow calf operations. We have this tab to make it easier for you to estimate your beginning and ending values for the different categories because animals grow and get sold or die, and so these tables will hopefully help you account for all of that. This tab is completely optional, though. The information is not pulled anywhere else, but it should help you fill out the livestock table that we were just looking at. Because we need quantities of livestock so, livestock purchased and how many you had at the beginning and at the end of the year and their market values. All right. Now, going to the land section, I've made some money on pasture rent. And we have some examples of opportunity costs that we need about land. But here I have my cost value, how much I spent on my land when I first bought it, how much I could sell it for now. Same for the end of the year. And here, the pasture value is an opportunity cost like essentially how much I could rent my pasture for. And that's the end. We've reached back buildings and improvements, which is where we had started. So now we can go to the reports tab and we can take a look at our ratios. Each ratio has its own explanation so that you can interpret them, but you can always ask us to take a look at them for you. So, that's all. Let me know if you have any questions and hope you give this file a chance. I am aware that it may seem scary at first, but we're here to answer any questions you may have. Thank you.