Financial planning spreadsheet

[Rand]

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?]

[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

[Rand]

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

[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.

[DrTimPerkins]

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".

[Rand]

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

[DrTimPerkins]

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

[Rand]

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.

[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.

[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.