Help needed on Excel spreadsheet design

[peteyperson]

Hey everyone! Here's what I want to do...

Lets imagine there are three buckets to full with water. One needs 50 litres, one needs 20 litres and one needs 10 litres to fill. I have a number for how much water I have available.

What I want to have is a category from my budget on a single line, with the daily, annual & 25x cost to fund it, and then show what percentage is accomplished. I can do this in a fixed order top-to-bottom but if I want a static list in order (Food, Shelter, Transport, et al.) but want to change th e order the buckets are filled, how can this be accomplished in Excel or other application? I want to either direct the water flowing to certain buckets, then default to top-to-bottom, or specify the order (1-10) down the ten categories.

e.g.,

Funds Available $150,000

Priority | Category | Yearly | X25 | Percent Complete

3# Food & Drink $3,000 $75,000 | 83 %
1# Accommodation $3,000 $75,000 | 100 %
4# Transport $3,000 $75,000 | 0 %
2# Communication $500 $12,500 | 100 %

I think I worked this out once a few year ago, but cannot remember how I did it!

Thanks for any help,
Petey

 

[Average Joe]

I make no guarantee and accept no responsibility for how you use this but I think have a quick, but inelegant, way to do this in excel quick and simple, without very complex formulas:

I’ve left out your requirement for a daily amount column but it’s simple enough to add one and input a formula to divide the yearly amount by 365 or vice versa if the daily is your base – and then re-letter the other columns as appropriate….

Column A “Priority” – for each cell below the header input the numeric priority of the category that you will enter in column B - 1, 2, 3, 4, ect

Column B “Category” – for each cell below the header input the category name – housing, food, transportation, communication, fun, etc

Column C “Yearly Requirement” – for each cell below the header input the yearly dollar requirement for the category of its row – if you want a daily column add one and just input

Column D “25X Requirement” – for each cell below the header input the formula Cy*25 – where “y” equals the row of that particular cell

Column E “Funds Available” – Put the header one row above all the other headers and put the number for your total available funds in the header row – for each cell below that input the formula Ey-Dz - where “y” equals the number of the row above the cell and “z” equals the number of the row the cell itself is in

Column F “no name needed, this column is just for a logic function and some calculation” leave the header blank or call it “calc1” or something – in the cells below the header input the function: =IF(Ey>0,Dy,Dy+Ey) – where “y” equals the number of the row that the cell is in

Column G: “Amount complete” for each cell below the header input the function: =IF(Fy>0,Fy,0) - where “y” equals the number of the row the cell is in

Column H: “Percent Complete” for each cell below the header input the formula: =Gy/Dy -where “y” equals the number of the row the cell is in

When you want reorder the priorities, simply insert new priority numbers in the priority column, select only the rows below the header row and from the menu, select sort and make sure you sort by either the appropriate column or by the header “priority” which should appear when you select sort from the menu.

Like I said – I make no guarantees and accept no responsibility and recognize that this method is not terribly elegant – but it does what I understood you to be asking for – and without a lot complex formulas

 

[peteyperson]

Hey Avg.Joe

Thanks for your reply.

I believe your info lays out a spreadsheet where one would resort the priotities, but then the categories would shuffle around. What I wanted to do was keep the budget items with the most vital first, moving down to the trivial at the bottom. However, whether paying off a debt or funding FIRE, it is often nice to be able to pay off/fill the smaller buckets first. Hence why I want to keep the "Food, Shelter, Transport.." order in place, but be able to reprioritise how the available funds are distributed.

i.e., you might fill the smallest category worth $100 at the bottom of the list, then the #2 priority is the 'Communications' category that is third on the list, with the remainder of the available funds. The model "allocate to a line, move the balance to the line below it, use what is left or rince repeat" works unless you want to keep the category position fixed but reorder the sequence the available funds are distributed.

Petey

I make no guarantee and accept no responsibility for how you use this but I think have a quick, but inelegant, way to do this in excel quick and simple, without very complex formulas:

I’ve left out your requirement for a daily amount column but it’s simple enough to add one and input a formula to divide the yearly amount by 365 or vice versa if the daily is your base – and then re-letter the other columns as appropriate….

Column A “Priority” – for each cell below the header input the numeric priority of the category that you will enter in column B - 1, 2, 3, 4, ect

Column B “Category” – for each cell below the header input the category name – housing, food, transportation, communication, fun, etc

Column C “Yearly Requirement” – for each cell below the header input the yearly dollar requirement for the category of its row – if you want a daily column add one and just input

Column D “25X Requirement” – for each cell below the header input the formula Cy*25 – where “y” equals the row of that particular cell

Column E “Funds Available” – Put the header one row above all the other headers and put the number for your total available funds in the header row – for each cell below that input the formula Ey-Dz - where “y” equals the number of the row above the cell and “z” equals the number of the row the cell itself is in

Column F “no name needed, this column is just for a logic function and some calculation” leave the header blank or call it “calc1” or something – in the cells below the header input the function: =IF(Ey>0,Dy,Dy+Ey) – where “y” equals the number of the row that the cell is in

Column G: “Amount complete” for each cell below the header input the function: =IF(Fy>0,Fy,0) - where “y” equals the number of the row the cell is in

Column H: “Percent Complete” for each cell below the header input the formula: =Gy/Dy -where “y” equals the number of the row the cell is in

When you want reorder the priorities, simply insert new priority numbers in the priority column, select only the rows below the header row and from the menu, select sort and make sure you sort by either the appropriate column or by the header “priority” which should appear when you select sort from the menu.

Like I said – I make no guarantees and accept no responsibility and recognize that this method is not terribly elegant – but it does what I understood you to be asking for – and without a lot complex formulas

 

[Average Joe]

Hey Avg.Joe

Thanks for your reply.

I believe your info lays out a spreadsheet where one would resort the priotities, but then the categories would shuffle around. What I wanted to do was keep the budget items with the most vital first, moving down to the trivial at the bottom.

OK, I think I get it. A clumsy solution would be:
Add a column to the above described sheet with letters in alphabetical order matching the order you want the categories to be listed. Then reprioritize with your “what if I paid that first” priorities, and sort by the priority column. Then copy the “amount complete” and “% complete” columns (copy just the data – not the formulas) and paste those numbers into the next two columns and resort the whole sheet by the alphabetic column that will put your categories back in their original order.
It’s clumsy, but it works.

 

[peteyperson]

That's cool, Joe. I've managed to implement both posts. Simple sort when I highlight the rows to sort. I think this is how I did this several years ago, vaguelly recall something like that!

I would also like to convert the 'percentage completed' into a visual bar chart. Do I have to add a separate bar chart for each category line? How can I neatly do this, dynamically, for perhaps 10-20 categories?

Thanks for your help so far,
Petey

OK, I think I get it. A clumsy solution would be:
Add a column to the above described sheet with letters in alphabetical order matching the order you want the categories to be listed. Then reprioritize with your “what if I paid that first” priorities, and sort by the priority column. Then copy the “amount complete” and “% complete” columns (copy just the data – not the formulas) and paste those numbers into the next two columns and resort the whole sheet by the alphabetic column that will put your categories back in their original order.
It’s clumsy, but it works.

 

[Average Joe]

I would also like to convert the 'percentage completed' into a visual bar chart. Do I have to add a separate bar chart for each category line? How can I neatly do this, dynamically, for perhaps 10-20 categories?
Petey

You can input a chart and select the cells in the “% complete” column as your data range – or do a comparative chart and use the % in one column and compare it to a dummy column in which all the values are “1” – but I think its confusing to look at widely varying amounts expressed as visual percentages.

IMO, a more informative graphic would be a 3-D column chart comparing “amount required” to “amount complete” - that way you maintain a visual aspect ratio that emphasizes that you are dealing with vastly different quantities – but you can still “see” the percentage complete for each quantity.

Insert – chart – column - then from column chart menu that comes up, select 3-D column – next – now for data range, select the cells with your category names, then hold control while selecting the “amount required” cells then hold control while selecting the “amount complete” cells – next – next –finish --- that should give you pairs of columns in which one is the amount required and the other amount complete, graphically showing you both how much of each is completed and how big an amount that is compared to the amounts in the other categories you have.