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:
And some options which, against our expectations, can not provide a suitable solution:
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.
Spreadsheet! It’s a good way to start building capacity, and it fits in nicely with what we are doing already. Thanks for doing the research, @anu and @matthias.
I also think the spreadsheet is the easiest way to get our own minds about what we want it to do, while learning about the economics behind it (for newbies like me:)).
There are two sheets:
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.
Fixed.
Some questions as I slowly move into participating… Mind you, i’m not an excel expert so trying to read and understand the formulas in terms of how we will be working with the sheets, in practice:
Correct me if I’m wrong:
The information you have in the beginning will be the one you input in Project Overview, Budget and Revenues (=predicted money? from what I understand).
In Budget, all columns are fixed from the beginning, exceot the “Spent” columns which are getting filled only as you go on with the project and fill out the Expenses sheet, right?
Translating the capture below, it means that only Noemi is tasked with community management, but Alberto is managing? As I go invoice, the Spent gets to an equal with the budget.
How would you explain this formula?
=IF(
NOT(ISBLANK(B:B)),
QUERY(Expenses!$C$4:$D,"SELECT D WHERE C = '"&B:B&"'"),
SUM(INDIRECT("K" & ROW() + 1))
)
Finally, can you confirm that the Cash predictor is only based on Overview/ Budget / Revenues information and should be generated beginning of a project?
Is it the info in Chart Data type sheets that ultimately should go in the predictor in Magic Spreadsheet?
Thanks for all this work!
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.
Thanks Anu (and @matthias for answering above).
Trying to get back into it these days, hope I’m not holding you up…
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?
Hi @Hugi. Yes, it is ready to use. I think noemi tested it as well. Sure @alberto I like the idea you presented. Let me know, we all can get connected in group chat on riot.
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?
Yes
@matthias I have planned to do the magic spreadsheet for POPREBEL and NGI Forward so that the board can get a clearer idea of cash flows.
@alberto mentioned that it may in fact not be ready.
Can you please let me know
Thanks!
Not finished to fit Alberto’s requirements.
V2 is finished for people who are more patient with spreadsheets, can make some own adaptations where needed, and would ask @anu for instructions where necessary.
V3 will be finished somewhere in early / mid October.
I have also tried it for RebelVillage:
https://docs.google.com/spreadsheets/d/1DSNfVgpPNGrKplm7BjMBFU_mGSGYIdXRXDfx_I2rKp8/edit#gid=2147433014
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).
Noted.
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.