Financial planning spreadsheet

Printable View

11-27-2014, 12:59 PM
Rand

Financial planning spreadsheet

Hi Folks,

First post for me. I have gleaned a lot from the posts here and am feeling like I am drinking from a fire hose. It's great. Thanks to all who are so freely sharing their knowledge and expertise here.

Very brief backstory: I and the family are considering a move back to Vermont, where we have aging family and are looking for something healthy and economically productive to do, which brings us to considering sugaring and syrup as a possibility. I am in the process of running numbers (a caveat, it may all turn out to be a thought exercise in the end). I am not a finance guy, but can work my way around a spreadsheet pretty well. I am developing a set of linked spreadsheets in excel to help with business plan development, "what if" analysis, financial forecasting, etc. Eventually I hope to have a tool that will allow testing of assumptions on production revenue and costs, can incorporate land and capital investments, will allow scaling up over time (e.g., add 750 taps in year 2, bring on RO in year 3), and can calculate interest and principal payments, etc. Finally, I am hoping that some of the summary sheets will be suitable for bringing into a traditional business plan (e.g., pro forma Profit&loss, cash flow, balance sheet). Most of this is a ways off as I am learning as I go. I have been relying to some degree on the existing resources for calculating sugaring financials from UVM and Ohio State

My plan is to share this stuff with this community to get feedback, ground truthing of assumptions, and ideas.

In the spirit of sharing, I have attached an excel workbook that let's you grow out an operation over 5 years. I think it is pretty self explanatory from the text box at the top. The yellow cells are those for user input. Non-colored cells are automatically calculated.

In brief, the workbook lets you add new taps, sell a certain percentage as sap, and decide on how the remainder that is made into syrup gets divided into bulk, wholesale, retail, and other (candy etc) and what that might generate in gross revenue. As a tool, this should let you pretty quickly test a variety of scenarios, for example:
- what does a grow out to 3,000 taps vs 5000 taps look like?
- how does aggressive retail sales compare to bulk only sales?
- different growth scenarios (e.g., year 1: sap only sales, year 2-3: half sap and initial syrup production, Years 4-5: full syrup production and growth in retail sales)

The sky is the limit on questions. Of course, this will have much greater utility when the cost and capital components are added.

Any feedback is of course welcome, especially on the assumptions. Just remember, I am new to this, so be easy on me.

Thanks,
Rand

p.s., the first feedback I need is how to upload a file. I keep getting "invalid file" message. [Edit: Seems like I can upload a pdf but not an excel file?? Should I put it on google docs and provide a link?]
12-16-2014, 11:35 AM
Rand

I have made good progress on the spreadsheet I discussed in the first post and have shared it via PM with a couple of folks here. I think it is now a pretty helpful tool, as far as running different investment scenarios.

It works like this: Enter data in three input worksheets: production&revenue, annual expense, and capital investment. From there, the income statement, cash flow, and balance sheets are populated as well as the summary tab. The capital tab allows you to enter capital investments, timing (which year purchased), establish a depreciation schedule, put in financing terms (if a loan is used). From there, payments, annual interest and principal, depreciation, cash required etc. are calculated and summarized.

I still don't know the best way to easily share, though am happy to send a copy to you if you are interested in looking at it. Any suggestions on how to provide simpler access are appreciated also.

Rand
12-17-2014, 02:45 PM
buckeye gold

Did you consider using a database instead of a spreadsheet? I think it would be much more efficient to generate the data in the forms you want. Build tables and spreadsheets within for the types data you would want to use. Then query the tables and generate a report on any parameter within the data stored. You can build an input form for repeated queries and still use formulas within the database. It might be cumbersome to do the initial build, but then you can add data to the tables for years. It's the best way to build a long term data retrieval system and once it's operational a lot less time consuming. I'd be amazed if Dr Tim didn't already have maple data bases operational. Perhaps he could give you tips on best data tables to build etc.
12-17-2014, 03:05 PM
DrTimPerkins

Quote:

Originally Posted by buckeye gold

I'd be amazed if Dr Tim didn't already have maple data bases operational. Perhaps he could give you tips on best data tables to build etc.

You might want to check out the info at http://www.uvm.edu/extension/maple/?...lications.html Scroll down to the line that reads "Maple Sugaring Worksheets".
12-17-2014, 03:07 PM
Rand

Good suggestion, buckeye gold. No, I did not look at a database v. Spreadsheet. I guess you tend to use the tools you know best. I am ok on excel, much less so on database development. Not sure where I would start. I would be happy to have someone help with a migration to database tool. Dr. T would probably be a good place to start.
12-17-2014, 03:09 PM
Rand

Quote:

Originally Posted by DrTimPerkins

You might want to check out the info at http://www.uvm.edu/extension/maple/?...lications.html Scroll down to the line that reads "Maple Sugaring Worksheets".

Good reference. I did use these worksheets as well as some similar from Ohio State as starting points.

Dr. T: any suggestions on how to best share the excel spreadsheet here? It does not seem to be allowable to attach to a post. Thx.

Rand
12-17-2014, 07:25 PM
DrTimPerkins

Quote:

Originally Posted by Rand

Dr. T: any suggestions on how to best share the excel spreadsheet here?

Send it to me via email and I'll see if I can post it for you. Apparently I have "moderator" status so I can post to restricted "notice" type threads. Happy to give it a try. Email is Timothy.Perkins@uvm.edu
12-17-2014, 11:03 PM
Rand

Quote:

Originally Posted by DrTimPerkins

Send it to me via email and I'll see if I can post it for you. Apparently I have "moderator" status so I can post to restricted "notice" type threads. Happy to give it a try. Email is Timothy.Perkins@uvm.edu

Thx. I sent am email to you a bit ago. Appreciate it.
12-18-2014, 07:23 AM
Michael Greer

Keep in mind that all the financial planning, and all the machinery and gear won't change the weather, which will be the largest factor in a season's success or failure.Aim for a target somewhere in the middle of what may be possible, and be prepared to work longer and harder if it's a bumper year.
12-18-2014, 08:58 AM
spud

Quote:

Originally Posted by Michael Greer

Keep in mind that all the financial planning, and all the machinery and gear won't change the weather, which will be the largest factor in a season's success or failure.Aim for a target somewhere in the middle of what may be possible, and be prepared to work longer and harder if it's a bumper year.

Very good advise for sure. Weather, bulk syrup pricing, price of buying land and the cost of sugaring equipment change every year. When I started out I had a certain amount of money to put towards sugaring. My land had 6000 taps on it but I was only going to tap 2000 the first year and then set up the sugar house for boiling. As we were setting up the woods a friend told me I should consider selling my sap. This thought never crossed my mind but I wanted to learn more. I was told for the amount of money I would have to spend in setting up the sugar house (rig, RO, filter press and much more) I could set up the other 4000 taps to have a total of 6000 taps. I then was told people pay 65-70% of bulk price for sap in my area. For me it was a no brainer. Why buy all the equipment for the sugar house (that will only depreciate in value) when the real money is made in the woods. I was already making profit on my investment a third of the way through my second season. After the second season we thought about setting up the sugar house to boil. At this time our neighbor was selling his 60 acres of land that bordered my land and it had 3000 taps on it. Again a no brainer here so I bought the land instead of buying equipment that only loses value each year. After having a very good third season we then set up the other 3000 taps for a total of 9000 taps. Our thought now is to never boil and to pursue buying more land. The price of sugar woods is going up and more people are getting into it. I feel buying more land now before it gets to expensive is the way to go for me. I can always sell the land later in life if I needed to. Another thing I have seen in the world is many sugar makers have put themselves into such debt that it takes the fun right out of sugaring. If every year your giving your syrup check to the maple equipment dealer then whats the point in sugaring. I am close friends with several dealers and they tell me that at the end of a sugaring season they have to pick up equipment at many sugar houses. People get in way over their heads and then cannot afford the equipment they bought or charged. Only spend what you can afford to lose when setting up a sugar woods.

Spud
12-18-2014, 10:56 AM
Rand

Quote:

Originally Posted by Michael Greer

Keep in mind that all the financial planning, and all the machinery and gear won't change the weather, which will be the largest factor in a season's success or failure.Aim for a target somewhere in the middle of what may be possible, and be prepared to work longer and harder if it's a bumper year.

Sound advice. What I hope is helpful about the spreadsheet is that it makes it easier to look at the implications (i.e., bottom line given fixed costs, etc.) of best case or worst case scenarios.

Rand
12-18-2014, 04:18 PM
Rand

1 Attachment(s)

MapleCalc spreadsheet attached (finally)

OK, I have attached the spreadsheet referenced in the beginning post. According to the sage advice of Tim Perkins, it can be attached as a zip file (but not an excel file) so it is attached in that form. Start with the first tab ("Read Me First") for general description. The tool is best suited for looking at a new operation or expansion scenario. All data is entered only in yellow cells on the following three tabs: Production&Revenue, Expenses, Capital. Everything else is calculated from there. Existing information in yellow cells is there for illustrative purposes only; please delete and add info specific to your situation.

It is a work in progress, hopefully tuned based on feedback from people like you who are more knowledgeable than I am.

I developed this to be able to quickly test assumptions and scenarios. I am offering it up here in case anyone sees utility in it. I have already learned a lot from this site. This is my way of hopefully paying things forward as I have little in practical sugaring experience to offer up at this point.

Any and all feedback, constructive criticism, questions gratefully accepted.

Rand
12-18-2014, 06:28 PM
buckeye gold

Wow that there is a monster Rand! I did not enter numbers to see how things calculated out, but she looks like a workable book to me. Since you already have as many work sheets as you have you may as well add one or two more for entry data. One sheet could do it. The columns would be your line items and rows for single transactions. With the expenses and total linking to your expense sheet. This would allow operators to add individual payouts/invoices for tracking and ease, as they can enter as the expenses come in. They could even add notes to each cell. The same is true for sales. You could do columns for each row/line item and allow people to track each day's sales or individual.

I have a spreadsheet and I want a line for each transaction with cost to profit numbers updating at each entry. Perhaps since your this deep add a sheet for production records. Mine is just an extension of my financial sheet with my columns being date, sap collected, syrup made, sap sugar, lot info, lot number, sap held over and comments. All columns total a running total and I have a line for sap to syrup conversion and bottled syrup.

I have to go I saved it to play with some more if that's ok.
12-19-2014, 10:21 AM
Rand

Good ideas Buckeye. It would turn it into more of a tracking tool than a projection tool. I suspect (hope) there are better ways to do that, perhaps thru customizing an accounting package. Your suggestion also resonates well with your earlier recommendation to use a database rather than spreadsheet. I can see how you would have entry forms for expenses and investments and the like. Maybe something for me to dabble with some future December.

Rand