Magic Spreadsheet and Budget Template Development

[nadia]

thank you <3

[alberto]

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

[johncoate]

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

[alberto]

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

[johncoate]

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.

[matthias]

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

[johncoate]

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?

[johncoate]

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

[matthias]

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.

[johncoate]

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.

[johncoate]

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

[matthias]

I can't get the Edgeryders Project Profit to come out the the (extremely close to) 20% the numbers indicate.

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 …

And I am having trouble with … "Fill down column B so that its =IMPORTRANGE(…)"

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.

"Grant access. You will now see a #REF! in the "Project Name" column. Mouse over it …

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

[johncoate]

You are a kind man.

[johncoate]

The 20% in this case comes from the 5K (which makes 1K the correct number for ER's cut), not the 7K which includes the travel allowance. So yeah that would not be an accurate number. Not that I know how to adjust the sheet to allow for that.

[matthias]

I think we just leave it as it is … having a consistent way to determine and compare profit among projects seems good to have, and it's a small project anyway.

[johncoate]

True.

Shouldn't the current Magic Spreadsheet say v3 instead of v1?

[matthias]

It's an independent piece of software (as in: spreadsheet file) from the Budget Template spreadsheet, so it has its own versioning. I see it can be confusing though. If you want you can adapt it, but then take care to also adapt it in all the existing documentation and discussions.

[johncoate]

Where, in FreeAgent or otherwise, do I look to see if the money from Rijeka has been transferred to us? And I am not sure about the process of authorizing payments after we do get the money. I have received money from others who authorized it, but I have not handled that end of things before.

[alberto]

Banking => LHV

By default, you should be seeing the transactions of the current month. Use the drop-down menus to look into previous months. You can then check whether a payment that looks right has come in.

Alternatively,

Work => Invoicing (https://edgeryders.freeagent.com/invoices)

Both methods tell me that invoice 018 (I renamed it: before it was called Invoice Invoice 018, for some reason. The reference should just be the number, in this case 18) has not yet been paid. However, remember that it might have been paid since the last time FA was synced with the bank (Monday last week).

Again, happy to give you guys a crash course on FreeAgent …

[matthias]

I am not sure about the process of authorizing payments after we do get the money.

It follows our usual invoicing process. In short, the person submitting an invoice to Edgeryders assigns a Dynalist task to authorize it to the project manager. The project manager authorizes it (in FreeAgent), and then the payment manager pays it.

(And, although we never dealt with that case explicitly yet, it is no longer an issue if the project manager authorizes their own invoice. The "two directors rule" for payment authorization is a matter of the past by now.)

See here for the full description of the process:

Invoice payment process