Magic Spreadsheet and Budget Template Development

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.

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

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:

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

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! :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?


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

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.

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

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?

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

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.

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.

Here is the sheet I just made. Seems mostly ok except I can’t get the Edgeryders Project Profit to come out the the (extremely close to) 20% the numbers indicate. I tried modifying the formula (=Cost!$H$26 / $B$14) but that didn’t work.

And I am having trouble with Item 4: Fill down column B so that its =IMPORTRANGE($A…, “ProjectData!A4:G4”) formulas cover the rows you added to the table.

I do not see that formula on the sheet so I can fill it down. I expected to see it in the column B header. Am I supposed to add it in?

“Grant access. You will now see a #REF! in the “Project Name” column. Mouse over it and click on the “Authorize” button that appears. This permanently allows the Magic Spreadsheet to access the placeholder spreadsheet (as long as you have access to the placeholder spreadsheet).”

That did not happen for me. Is this #REF! supposed to show up in the Overview?

I am sorry I am having such a difficult time with this. I have spent a lot of time trying to make sense out of it and keep coming up short. For a guy who handled big budgets in a variety of businesses, it embarrasses me to have such trouble with this…

With a budget of 7000 EUR and a profit of 1080 EUR, you might have calculated the profit as “20% of sum of costs”, as in: 1080 EUR / (7000 EUR - 1080 EUR) = 18.2%. Then it’s very close to 20%. However, I think that what we mean by a 20% profit margin is “20% of the budget”. That’s how the 15.4% result is calculated. No worries, we had that confusion before. At least for the future the spreadsheet has now the “canonical” calculation …

Ah sorry, that referred to the Magic Spreadsheet file, not your Budget Template file. I made that clear in the documentation now. I also did it myself in the Magic Spreadsheet already, so no need to care for this anymore.

That only happens after adding the IMPORTRANGE(…) formula. I did it as well already.

Also I found various other small issues and fixed them in your spreadsheet and (for everyone else) in the budget template. I also removed the secondary currency from your spreadsheet (since the project is in EUR) and moved the spreadsheet into its project folder (in case you search for it). So Everything about your budget spreadsheet is fine now :slight_smile:

You are a kind man.