Magic Spreadsheet and Budget Template Development


#27

Yay! Great news, thanks!


#28

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.


#29

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)


#30

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.


#31

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.


#32

Did you? Nothing here, and I don’t remember seeing them so far.


#33

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 :blush:


#34

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 :wink:


#35

Documentation for the Magic Spreadsheet is now available in the Company Manual, section “2.10. Monitoring the company’s cash flow”. Happy cashflow monitoring! :blush:

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?


#36

I hear and obey. But not this week, I am totally swamped!


#37

thank you <3


#38

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


#39

I created a budget spreadsheet for the Rijeka workshop but have not linked it yet.


#40

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


#41

I’m ready to link the Rijeka project to the magic spreadsheet, but I am not sure where it is. The versions I found aren’t active.


#42

Does that mean you already have filled the budget template for the Rijeka project? If so, and if you were using the up-to-date version “Budget Template v3”, you can link it to the Magic Spreadsheet using the instructions “4.1. Creating placeholder spreadsheets” in manual topic “Creating and managing a budget”.

If you want to start with a blank budget template, there is no need to link it as you’d start with a pre-linked file. Again, the instructions for that are in “Creating and managing a budget”.


#43

I’m not sure if the sheet i created is v1 or v3, but i put the link to where you linked up above to see if it works as is. I took a spreadsheet Noemi had made and redid it.

In the “creating and managing a budget” portion of the company manual as pre your link, the link in the manual goes to version 1. If we are using v3, then is that link wrong or is it really v3 but just says v1?


#44

Maybe it would be worthwhile to put old versions of these templates into a folder…it gets confusing as it is.


#45

That’s a link to version 1 of the Magic Spreadsheet – which is the latest and up to date one.

What I meant is, have you used the Budget Template v3 to create the Rijeka budget. It appears you used the v1 (as there are still ChartData1, ChartData2 etc. sheets inside; I created v3 only recently). So the best is to copy & paste the data from your sheet to a new one derived from Budget Template v3, according to the process in the manual topic.


#46

I am remaking the Rijeka Budget in what I believe now is the correct template - I am trying to follow the instructions verbatim to see how it works.