Tutorial of Project-X (working title)

Project-X enables you to make calculations (like a spreadsheet) on soundly managed data (like a database), in a presentable form (like a word processor). In this tutorial we demonstrate how.

A Notebook

Let's start with the basics: you can write. Headers and text are entered in blocks.

Clip 1: A Notebook

The combination of these features make this a (very) basic word processor. But we *are* a serious spreadsheet and database. It might not look like it, but really, we'll show you.

A (fairly) real example

We will demonstrate Project-X by introducing Gallifrey Tech Solutions. The company has a team of software engineers who work on fixed price projects. Gallifrey uses Project-X software to quickly create financial forecast reports.

But first an identity crisis: is Project-X a spreadsheet or database? Maybe we are both.

At any rate you'll need data to play with. Like a database, we'll place it in a named table. Let's add a table with employees. And for those of you watching the video below and who are not up-to-date with the latest managerial-speak: FTE is short for 'Full Time Equivalent'. We won't hold it against you if you choose to immediately forget this.

Clip 2: Creating a table

The first row contains the table name. When you add a cell we boringly call it 'A' (like a spreadsheet), but when you click you can rename it. The second row contains the data format. Later in the tutorial we'll show you how you can add spreadsheet formulas there as well. And the third row shows the actual data.

So now we can:

1. create tables with formatted data and calculations.
2. interspersed with explanatory text (no more copy-pasting tables to your favourite Word processor)

This way of working is called 'notebook-style' and it is really popular among data scientists. We think spreadsheet creators can make good use of it too.

Adding rows

We added the columns, so where are the rows? Well, just see what happens when you add one.

Clip 3: Adding Rows

Saw that? That's another database feature we added to a spreadsheet. We give you permanent data identifiers to which you can refer in your calculations! Since nobody is happy with a number: you can create your own patterns. And yes, EMP_01 definitely gives your users much more insight than simply 1 :)

Before we continue, we'll quickly add another table with projects for the employees to work on. And a minimum hour norm that Gallifrey uses to make its financial forecasts.

Clip 4: Creating some other stuff Gallifrey needs

Identification and variance

If you take a good look at the Employee and Project tables you see that each cell in a column is *identified* by the ID row. We call those the variances. Did you notice that we said variances in plural? Let's see what we mean by that.

What if you wanted to say something about both a person and a project? You want to assign, for example, your employees to projects. Easy. You simply drag the ID column of Employee and Project to the 'plus' mark to create a new table.

Clip 5: Creating Assignments

By the way, you would see that the IDs in this table change automatically when you alter them in the Employee or Project tables. That's another of the database bits we put in the system. Anyhow, back to variance. The Project Hours data don't have to be a standalone column. You can also append it to the existing Project table:

Clip 6: Dragging Columns

Variances is one of the really nifty things we added to Project-X. We know that Project Hours (and Assignments in general) is identified by both Project.ID and Employee.ID. So when you drag the column to the Project table we can automatically create a variance in that table for Employee. And because we really are not convinced that EMP_01 makes people happier than a number (such as 1) we give you the option to show a field that people can better deal with. In this case Gallifrey chooses to show the first name of the employee. That's much better!

Dragging and dropping columns is really fantastic. Gone are the days when you were futzing around with multiple sheets and endless VLOOKUP or INDEX functions! That's a big HURRAAAY in our book. And no more copying of data into multiple tables (we don't know anyone capable of keeping consistent track of that!). And you for sure don't have to hide multiple merged rows in a single (text) cell anymore, like this horrendous example:

Calculations and variance

Now, finally, yes: we will show you some calculations (and more dragging and dropping) to build the report for Gallifrey. Let's say we want to show how many hours each employee is expected to work on projects per year:

Clip 7: Formulas and Calculations

That's how formulas can really work to keep your data persistent. We apply the same formula to the entire column. You can't lose yourself anymore in a mass of error-prone copied cells with individual calculations. Oh, and also nice: no more colleagues taking a quick short-cut and altering the formula in cell $G$176 because the project on that line of data just "works slightly different" than the others. With Project-X things are crystal clear: one formula. Most of us rarely need more. And to write formulas we support virtually the same set of functions that you know from Excel and Google Spreadsheet.

Another cool thing in Project-X is the built-in transparency. You can *always* see how a value is calculated. Simply click the cell and we give you the overview. This really helps in figuring out 'what the heck is going on' and fix a bug, or - if needed - change your perception :)

So now we have:

1. Tables and formatted data, like a database;
2. Formulas and calculations, like a spreadsheet;
3. Interspersed with explanatory text, like a word processor;
4. where you don't lose yourself in a mass of error-prone copied formulas.

Let's look at a couple more formulas to know how much those Assignments actually cost.

Clip 8: Some more formulas and calculations

We'll calculate the costs per hour for each employee and combine that with the Project and Assignments tables.

Note that you can write formulas that refer to data in other tables: ='Project Hours' * 'Employee.Actual Hour Costs'. Although we created the column 'Actual Hour Costs' somewhere else, you can easily add it to the Project table because of the variances. This means you don't _need_ to VLOOKUP the proper 'Employee.Actual Hour Costs and it would probably not even occur to you.

Oh, and of course Project-X supports aggregated functions in formulas. For the finishing touch we add a SUM(Assignments.Cost) to see the costs per project. Compare it with the 'Fixed Price' to get the Forecast that Gallifrey wanted to have.

Creating scenarios

Often we build spreadsheets to see what the consequences of different scenarios are. We want to help Gallifrey to pick the best allocation of software engineers to projects, and maintain a positive budget position. In the current scenario Cindy is deeply invested in the Frobnicatorz'r'us project. What if she were to work less and Charly a lot more? In a regular spreadsheet we either have to copy the entire sheet, or accept that we lose our ability to compare when we change the numbers. Not in Project-X! We are building a database-spreadsheet (or a spreadsheet-database if you like). One of the cool benefits is how easy it is to create multiple scenarios. You can go wild and create tons!

Clip 9: Scenarios

Gallifrey wants to create multiple assignment scenarios and see how different staffing choices on projects affect their bottom-line.

We simply add a new column to Assignments and give it the creative name 'Scenario'. As data format we pick the 'Variance' option. Each group of Project Hours is now not only identified by Project and Employee but also by Scenario. Like with every other Variance, the Project-X application asks you for an identifying pattern. We want to identify each individual scenario as 'Scenario 1', 'Scenario 2' etc. You'll find that a new column is added with the identifier and that the current assignments are now all part of 'Scenario 1'. You will also see that the column was not only created in Assignments but also automatically added to the Project table!

To create a new scenario we can select the Scenario column in Assignments and then click the icon below the table to add new rows. Project-X automatically creates all the necessary rows for Scenario 2 - both in the Assignments and the Project tables.

After entering the data for 'Scenario 2' we end up with a Project table that shows all different scenarios. Although this definitely works for just two scenarios, adding more - with different columns! - quickly turns a table into an unreadable mess. So instead, you can drag the scenario column to the top of the Project table. A rectangle will light up where you can drop it. Now we have tabs for 'Scenario 1' and 'Scenario 2' and we can click on the plus for a new Scenario.

Finally: Let's look at our Project.Costs and Project.Forecast columns, shall we? You'll notice that they received the scenario variance as well and are changing according to the selected tab.

Now what?

We are busy making Project-X a reality and are curious to hear what you think! E-mail any ideas, comments, feature requests and other remarks to gertjan.filarski@fourdays.nl.

Fourdays is a company registered in the Netherlands. If you'd like to have chat over a feel free to send an email to cup_of_tea@fourdays.nl. .

• gertjan.filarski@fourdays.nl
• jauco.noordzij@fourdays.nl
• RSS-feed