This topic is about the “Magic Spreadsheet”, our future all-in-one solution for budgeting, cash flow predictions, and project status information. Development is done by @anu with assistance from myself where needed.
We will discuss and decide some of the features here, and moreover, will keep you informed with updates how the development goes.
As a first notable update, mostly of interest for @alberto: we looked for existing cash flow prediction software, to not reinvent the wheel if we don’t have to. However, the results were disappointing: no open source alternative is available at all, and the commercial ones seem too expensive for our needs. (I’m personally a big fan of keeping business running costs low, and paying more for a small financial software than we pay for all of our Discourse hosting would be certainly strange.)
Here is an overview of all serious candidates we found, in case somebody wants to argue that we should use one of them instead of rolling our own:
Pulse: bit simpler than the other options, and at 49 USD/month for a 10-user plan or 29 USD/month for a single user plan still way too expensive
And some options which, against our expectations, can not provide a suitable solution:
QuickBooks Online: it does not support cash flow predictions (see); note that QuickBooks Desktop does, but we need an online solution
Easy ERP: an open source ERP solution that comes with a cash flow feature, but that is seemingly only about past cash flow; other open source ERP software might provide forecasts, but also a ton of features that we don’t want to have, so it would be worse than a simple, tidy spreadsheet
Open source personal finance tools: Some of these tools, esp. seemingly Skrooge with its “Simulations”, offer cash flow predictions. However, they are all desktop software, so not suitable for a simple collaboration collaboration process. Moreover, they are not made for business purposes, so might not have features like per-project budgeting etc. that we need.
Our chosen path is so far to develop a spreadsheet template according to our own needs. We found quite some examples to look at, and even our accounting software FreeAgent recommends this approach. So this seems to be the usual path taken by businesses which are too small for a commercial cash flow tool or ERP solution.
Magic Spreadsheet: Will aggregate the project status and cashflow of all projects and provide the company’s cashflow prediction. Not much progress so far.
Budget Template v2: This is at least half-finished already. Each project will make a copy of this template document and use (and adapt) it for its own budgeting. (Update: the new and current version is Budget Template v3)
Here is a small project made using the budget template spreadsheet. Thank you for giving it a stab! Here is some early feedback. My first impression is that it could be simplified a lot. In fact, I made some changes already.
My main idea:
In the Budget tab, get rid of columns A and I to L. If we need Subtotals (which would be nice, and seems to have been the thinking behind column A), we’ll probably need a better solution – let’s talk about it in voice.
Move the Cash journal to the Budget tab. There should be a “predicted date of payment” column (only one: earliest, main and latest are overkill). This feeds the cash flow predictor.
The predicted money in should probably be in the Overview tab. We never had more than four (active) invoices for one project, so in the Overview tab we input the financial consideration: how much do we get, when we get it.
The “Paid” checkbox should probably go. Remember, the idea is that you know how much money has been spent on a project by looking it up in Freeagent, which is the authoritative record.
The form-filling in the Overview tab seems excessive. No, I am not going to input a description of the project. This will be available on the contract, on the platform etc. I suggest we go over it and decide what is absolutely necessary and kill all the rest.
Just a bug: columns D and H in the Budget tab do not seem to work. I had to reprogram column H, otherwise the budget would not work at all. But of course I only did all this on my local copy, not your master file.
Thanks, @alberto. We have utilized your proposals as detailed below, and a new version (let’s say a first beta release) is available now (link).
I have a few “internal standards” when I develop spreadsheet templates (or templates in general), and you seem to have different ones, but that’s fine. For clarification, mine include: (1) A template should rather include too much than too little functionality, as long as it’s simple to remove what is not needed in each application case, (2) it’s almost never a good idea to mix records of different types in one table (“budget items and expenses”, “config variables and revenues”), sheets should be treated like SQL tables with a well-defined record structure, (3) data should be structured in such a way that formulas can apply to it with as few exceptions needed as possible, (4) formulas have to be understandable without much work (esp. difficult as no comments are possible), so if needed use intermediate columns and tables.
So in effect, what we did with your proposals:
Yes, subtotals based on budget item number was a bad idea, not implemented (formulas get too complex). Current simple SUM(…) mechanism has to be improved as well though, as it easily breaks when adding rows at the edges of the formula’s range and forgetting to adapt the formula. We kept the budget item number column and unit / unit cost columns though, as they can be easily removed when not needed.
We kept the Cash Journal sheet (now “Expenses”), but yes, three dates were overkill. One date now.
Predicted money in got now its own sheet. Seemed the cleanest solution.
“Paid” checkbox is still there, but not used yet. The idea is that for projects we don’t have a bank account balance from which to start a cash flow prediction, so instead everything “paid” would be summed up to make that balance. Maybe there is a better idea to the same effect.
The form filling is mostly because (as discussed with @noemi) we’re also trying to replace the project journals with these spreadsheets, aggregated together in a sheet of the company-wide Magic Spreadsheet. Replaced “description” with a field to enter a URL with a platform reference, though.
Yes – into Overview, Budget, Expenses and Revenues. All of that is predicted money, and not all expenses would have to be predicted at the start of the project. But the more the better for the cash flow prediction chart.
Right. Currently, all associated expenses from sheet “Expenses” are included in the “Spent” sums. In the future, we’ll only include those marked as “Paid” (or as “Authorized”, if we decide to remove the “Paid” column altogether from the spreadsheet).
Not really, this is just not filled as intended. The D4 cell with “Alberto” in it should be empty here. Anyway, the budget items-and-subitems / subtotals mechanism will change again, as the way it is it breaks too easily …
I fixed two mistakes in that formula compared to the version you saw, but its task stays the same: it sums up all expenses from sheet “Expenses” that refer to the budget item using the item’s name (as shown in column “Item Ref”). Ask @anu for details, she made this
Confirmed. It will use all the planned expenses and turnover as entered into the “Expenses” and “Revenues” records. It’s ok to enter them / correct them as more information comes in, but yes, whatever is available at the beginning of a project should be entered already.
Rather the info in the “Expenses” and “Revenues” sheets of all spreadsheet files of our projects. You’d mix them all together into a sheet equivalent to ChartData1, then convert that again to a sheet like ChartData2 to make it ready to plot, and then plot a cash flow prediction graph as on the “Overview” sheet. Just not for one project but all of them “overlaid”.
Hope this helps! You can also work with Anu to create the right formulas etc., she learned a lot about Google Sheets by now.
Hi @noemi. Please feel free to discuss about magic spreadsheet with me also want to know if you been using it. If no then may be we together can find out the issues and I would try my best to get any issues or difficulties fixed.
I’m trying to understand weather this is ready to use, and what I should do?
It might be helpful to write a step by step guide, or if it’s less time consuming, for @anu to maybe screencast herself explaining what to do and how to use it? I would really like to put the Particip.io project in there, but I need some pointers.
@hugi, I think it is as ready as it gets without us trying to use it and making remarks, posting bugs etc. This is already the second iteration. So go right ahead.
Maybe it is too early to write proper documentation, but maybe you, I and @anu could meet online and try to erect a couple of projects. She could then observe what mistakes we make and the questions we ask to write the documentation. What do you think, Anu?
Hello everyone, I had another go at budgeting a small project. I posted two issues, which are simply bugs: one and two. Here, I would like to discuss the logic of the prototype. I have a request, a suggestion and a question.
###Request: separate clearly the budgeting functions from the bookkeeping ones
Right now, the Magic Budget does two things (or it will, when it’s finished). One is actual budgeting. Before the project happens, the project manager tries to predict future cash flows: how much money will go out/come in, and (critically) when. This is by far the most important function of the MB: that’s why it’s called Magic Budget, and not Magic something else. The other is helping monitoring: as money gets spent and earned, the project manager can put her past cash flow into the sheet, and check how well she is doing with respect to her predictions.
I don’t think I want to use the monitoring functions at all. The main reason is that FreeAgent does this already. If people want to use them, great. But I would like these two functions to be clearly separated (different sheets) so that I (and people who want to do like me) can just focus on the budgeting part, and never look at the rest.
Notice that past cash flow data are not used to predict future cash flow. This is important, and it would be a mistake to do otherwise. All those transactions are summarized in the bank account balance. The Magic Spreadheet (aggregating all the magic budgets) starts from the actual bank account balance, adds future revenues and subtracts future costs. So, my refusal to do cost-tracking in the MB does not break the the MS.
I would like for different rows in the “Budget” sheet to accept the same item reference (column B). Furthermore, I suggest that the item references are the same as FreeAgent uses. This reduces the cognitive load of bookkeeping, because our different tools use similar logics. Look at this: why cannot I have “staff” in B5, B6, B7 e B10?
Question: how do we tell the Magic Spreadsheet to accept the data from any given Magic Budget?
Quick feedback is that it is very resistant to adding new rows into the Budget field… and any modification in it messes up with the cell formats (ie coloring), and requires people to watch out for the formulas and copy them in.
Otherwise the predicted expenses and invoicing are what we care most about…
Unfortunately the format for the speadsheet is not always the one the client sees. I made one with the format for the client and realised I have to redo it somewhere else to eliminate the unnecessary info.
Also, the client rarely sees the overhead, and so the overhead either gets included into the costs or gets renamed (i called it Project administration and accounting).
Can someone from the board (or @alberto) please advise if you need the cash flow forecasts for your next meeting? If so, then I’ll put on my best patient hat and go for it with POPREBEL and NGI Forward in their current state.