No, @anique.yael. Really doing this is going to be super difficult before the Magic Budget works acceptably. Plus: no grant agreement…
Got it and great
So another feedback is that the sheets seem to be editable to the extent that they can become external. I did it so heavily that I could actually send a Budget and finance plan to the potential client. It didnt make sense to do other new ones with other structures. I either made a mess or hacked it (by hiding columns in the currency they dont care about, or by adding in-kind revenues etc…). I think it worked… or?
It would be great if the cashflow could be more friendly styled - like Invoices in one color and Expenses in another one, with a legend. Also I cant export it with full margins for the dates when project starts or ends, but maybe thats excel knowledge missing for me 
1 Like
Looks good 
The spreadsheet is meant to be hacked … it provides too many features for most scenarios, so hiding stuff will be the most usual hack.
Also I see you uncovered a bug in the cash flow projection graph … lines are never supposed to be skewed, only vertical or horizontal, because cash movements happen instantaneously or not at all. This has to do with the sorting of items in the intermediary tables of the chart when multiple transactions happen on one day (which I didn’t test yet).
I don’t understand what this is referring to. What kind of margins? Padding around the content of a cell? (In that case, a screenshot would help.)
Well in the image of the cashflow above, and in the original excel file too, I cant read anything that is on the or close to the x axis, the early beginning of the project. Actually I dont even see the begnning of the project on the axis Same goes for the end of the project.
1 Like
Everyone here: I think the Magic Budget Template is now ready to use. To use it, start with the documentation – everything is, or should be, in there. Starting from now, this is the new default how to do a budget (and we need to stick to it to enable company-wide cash flow controlling). Let me know in this thread what’s missing or does not work.
(Still no progress on the Magic Spreadsheet, but that’s the final step for me and @anu now: aggregating the project budgets into the company-wide cash flow plan.)
3 Likes
Yay! Great news, thanks!
1 Like
Proposal how the Magic Spreadsheet’s cashflow graph would look like. This is the best I can get out of Google Sheets, beyond that we’d need an own application that reads the project budget spreadsheets and then can do “whatever” with them.
Notes:
-
This is a stacked chart. Which explains why the green line represents both the total and Project 3.
-
All data is just an example here. Bank reserves would be 50k EUR in reality, for example.
-
The project specific lines could be removed, but I’ve left them in so far.
What’s confusing about them is that they cross each other – normally not seen in a stacked chart. It indicates negative cash balance of a project. And that’s ok for a project because it is only relative to the current day (which will be always the diagram’s start date). When considering the whole project runtime, the project would still stay cashflow positive.
1 Like
Noted @matthias, and many thanks to you and @anu for getting this off the ground. I’ve reviewed the Creating and Managing a Budget wiki and suspect when I actually do my first budget in this form I may have some questions.
For now a few points to note:
- My budgets for Research Network Horizon2020 projects have had their own format up until now. As we move into another round of Horizon 2020 proposals, I’ll start using the budget template.
- From what I understand it’s been decided we wait until Grant Agreements are signed (at least by the Consortium Coordinator) for me to use the budget template for POPREBEL and NGI Forward. That being said, I’m slowly beginning to prepare them so I can acquaint.
- As per the Design rationale, it seems it’s time to discuss how this can be adapted to align with the Horizon2020 financial reporting templates I provided you with for ease of reporting to the Commission as we talked about. Or have I missed something?
- Just to confirm I understand: the magic budget is for projects and the magic spreadsheet aggregates all project data together for company wide cashflow and accounting?
- On first entry, it’d be good if there’s more space for team members in the Overview for those that have bigger teams. For now I’m just reformatting it myself.
Cashflow spreadsheets (from around the web)
Yes, that’s one of the next steps now.
Exactly. The Magic Spreadsheet only does cashflow projections and company-wide project monitoring though … all accounting is in FreeAgent, but I’m sure you are aware of that.
Oh, just add lines in between the existing team member lines.
Great. Let me know when you’re ready and we can talk through it. For example, in addition to the reporting templates, there are some internal record keeping obligations we need to make sure are covered - see here but I can also explain.
Yes. Back on 13 September I updated you in the Research Network riot room with a link to where they are saved and saying that in time, let me know when you are ready to talk through aligning our internal tools with theirs. Perhaps the time has come 
1 Like
Update again, mostly for @alberto and @nadia: Magic Spreadsheet v1 is ready to use.
Documentation and a few tweaks will follow tomorrow, but you can have a look already. All main mechanisms work. For now, there are two example projects based on our Budget Template spreadsheet, providing some bad (but fictitious) cashflow outlook.
The graph now only shows the company’s total cashflow now, no longer the project-specific values. This is partially because project lines in the chart were just confusing, and partially for technical reasons. (You can always explore project cashflow by selecting parts of a project’s column in sheet CashflowData2 and then looking into the footer to see the sum, representing the project’s cash on hand value up to its last selected revenue item.)
Apart from that, I discovered that spreadsheets are a bad technology for everything except very simple, standard calculations and graphs. They’re basically two-dimensional spaghetti code … or four-dimensional one, when also counting the “multiple sheets” and “multiple files” options. The budget template is ok, but at some point we might want to reimplement the Magic Spreadsheet functionality in Python. But that’s 2-3 years into the future when @anu will be an advanced software developer who can handle this 
1 Like
Documentation for the Magic Spreadsheet is now available in the Company Manual, section “2.10. Monitoring the company’s cash flow”. Happy cashflow monitoring!
Of course this means we first need to create budget spreadsheets for all our currently active projects, and then also (but less urgently) for the currently negotiated / not yet started projects. Project managers, please do so! @hugi @noemi @johncoate @nadia @anique.yael
We’ll also need budget spreadsheets for the various core / internal projects to capture all costs. The office rent (in Brussels and soon Stockholm) is the major cost here. Maybe for @alberto to test drive the new budget spreadsheet?
3 Likes
I hear and obey. But not this week, I am totally swamped!
thank you <3
@matthias, just flagging that I have not forgotten, but I am struggling with several issues that cannot wait. Have faith, I’ll get around to it.
1 Like
I created a budget spreadsheet for the Rijeka workshop but have not linked it yet.
“Core” project now budgeted for. It covers from December 2018 to April 2019. Find it here. In agreement with @matthias, I also made minor changes to the documentation.
1 Like