I am sucking in this training material like a frog drinking out of a Firehose, and I guess I could be missing some material. I have rewatched some of the beginning videos to make sure I am developing a solid understand. But for now, everything is in a haze.
Anyway, I am hoping you could provide me with some guidance developing a burndown chart of cash over a period of time. For example, I have an estimated budget of cash to burn down over one year. Then as the invoices come in, I want to compare the budgeted burndown with the actual burndown.
My invoices come in with itemized (role / labor hours / rate / line total) and (materials) that were purchased.
I have been trying to figure out a way to create a tabular dataset for this, but I get stuck and need a little bump.
If I figure out a rough way to organize this data, I will post it.
Hope to hear back from you soon.
Sincerely,
Revised: I attached an Excel Workbook with baseline data that I think I will be working with. I will keep fooling with it, but wanted to at least get a rough draft uploaded.
Hi Troy,
I love your frog anaolgy 🙂
In the attached file you'll see on sheet 'Tabular' that I've consolidated your data into one table. You'll fidn your calculations in the 'Total' column aren't possible for every row so you may want to reconsider where you do this calculation.
I've also included a PivotTable and chart as an example of what you could do.
I hope that's the bump you were looking for. Please let me know if you have any questions.
Mynda
Good idea of the Actual vs Estimate Graph. Looks kind of like a glass (Estimate) with water (Actual).
To consolidate the data, did you primary do a copy from the other sheets and paste into one sheet?
Mynda: ". . . calculations in the 'Total' column aren't possible. . ."
Is this because of the format of the data in the Materials Sheet is different from the other sheets? I am not sure how else I would do this calculation. I will have to think about that some.
Thanks,
Glad you liked the chart.
Yes, the total column only makes sense for the labour expenses. You can have it in this table, but it means the auto-population of formulas when you add new data will not happen because they're not consistent the whole way down the column.
You could change the column headers for Hour to Unit and Rate to Price and then put the Materials workings in these columns too (obviously they might always be 1 unit x price). That way the formulas will be consistent the whole way down the column, which is best practice.
Hope that makes sense.
Mynda
