|
scmn | We do ours in Excel
I have the "Ledger" setup with entries for Date, Party, Description, notes, etc. Every transaction has a from Account and a To account.
My chart of accounts page then has a sumifs formula that sums all Amount entries where for the Code To and subtracts all entries for the Code From and then uses a correction factor (revenue and liabilities = -1, assets and expenses = +1)
=(SUMIFS(Ledger[Amount],Ledger[Code To],[@Account])-SUMIFS(Ledger[Amount],Ledger[Code From],[@Account]))*[@Factor]
What I end up with is the Chart of Accounts has a running balance for every account. By using a -1 factor for revenue I mark down sales from the revenue account to the checking account thus the (-)*(-) shows a positive number in the Revenue Account, and a (+)*(+) shows my checking account balances. Likewise when I loan money I take money from the loan and put it to the checking account. I want my Loan balance to show up as a positive number so I use the (-) correction factor. When I pay back the loan the money moves to the loan and works as a (+)*(-) which makes the loan amount smaller. I write a check from the checking account for repairs I get a smaller checking balance and larger repair expense.
I set my accounts up to align with checking, savings, assets, etc and my expense accounts line up with line items on Schedule F. I then used pivot tables to summarize Assets, Liabilities, Revenue, Expense, Equity, etc. My balance sheet has the Assets, Liabilities, and Equity pivot tables and the P&L has the Income and Expense pivot tables. The Category, Class, Sub Class, and Enterprise are used to group and organize the Balance Sheet.
I also have additional columns to the right where I move inventory To/From. I use locations like: Purchased, Production, Sold, Destroyed, BL (barn loft), BM (barn main level), TR (trailer), FD (field), etc to know were everything is at. It is nice knowing that there are 468 small square grass, 193 small square straw, 350 small square alfalfa, 175 5x6 cornstalk bales, etc and where they are at for inventory and making decisions to sell some feed/bedding and how much. It also keeps track of how many steers are bought and sold along with what pen they are currently in and how many are in said pen. I am working on including current weight based on rate of gain and last actual known weight. One could just as easily track grain, lumber, t-posts, or any other inventory item. For me the best part is everything is in one ledger. The inventory summary sheet shows how much and where along with production and sales date for the various crops that I track (grass, alfalfa, straw, custom baling, steers, cows, etc)
For 2020 my goal is to transition to using a google form for entry and updating the spreadsheet with the google form data so we can categorize the line item as it happens (groceries, repairs, cattle breeding, etc) vs entering at a later date and having to did thru invoices. I have a reference amount for split entries so that I can reconcile the reference amount with the bank statement when one check pays for lp, vehicle fuel, and livestock feed, etc. The inventory adds entries to the Ledger for inventory movement, but eliminates having to receive in on one ledger and expense on another and keep track of how to handle in both.
The rules of my sheet are simple: every account needs a unique number and every transaction needs a From Acct, a To Acct, and a $ Amount. When moving inventory the from and to acct are the same. I did smart number my accounts, but that is not necessary:
The first numeral is 1=Assets, 2=Liabilities, 3=Equity, 4=Revenue, 5=Costs of Goods Sold, 6=Expenses, 7=Production Data, 9=Personal Expenses
The second numeral is 0=Personal, 1=Schedule C Enterprise, 2= Schedule E Enterprise, 3=Schedule F Enterprise
The last numerals are the acct and usually increment in 100s or 10s
I try to keep the same expense categories the last 3 across enterprises when I can so 6x600 is repairs on all 3 and will also use the same last 3 for cost of goods sold and revenue so 43500 is steer revenue and 53500 is cost of steers
At the end of the day what you need is a system that fits you operation. I was not able to find one that would support 3 businesses so I worked one up on my own based on my needs. I still need to work up a method to auto update depreciation based on year in service and purchase price but have so few that I just update them manually for now.
Just for reference, in 2018 & 2019 I had close to 3500 line entries in the ledge. When I get to the point that I use the google form to track every time I give shots, move animals, fill feed bunks that could easily climb to 10,000 lines but everything will be there and will be easily queried. My goal is to get away from a system for tracking breeding, a system for tracking steer progress, a system for tracking inventory, a system for taxes. They all function on the same basics:TO-FROM so using one system all the time will be simpler in the long run IMHO. The setting it up is the difficult part but I have been improving this for 4 years now and am happy with how it works.
Edited by MiradaAcres 1/7/2020 16:17
 (Ledger (full).JPG)
 (chart of accounts (full).JPG)
Attachments
----------------
 Ledger (full).JPG (30KB - 161 downloads)
chart of accounts (full).JPG (35KB - 151 downloads)
| |
|
Accounting Software
