
Help needed on Excel spreadsheet design
05072008, 10:05 PM

#1

Recycles dryer sheets
Join Date: Sep 2004
Posts: 108

Help needed on Excel spreadsheet design
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 toptobottom 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 toptobottom, or specify the order (110) 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
__________________




Join the #1 Early Retirement and Financial Independence Forum Today  It's Totally Free!
Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that EarlyRetirement.org is a great community to join. Best of all it's totally FREE!
You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

05072008, 10:39 PM

#2

Recycles dryer sheets
Join Date: Oct 2006
Posts: 93

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 reletter 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 EyDz  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
__________________



05072008, 11:10 PM

#3

Recycles dryer sheets
Join Date: Sep 2004
Posts: 108

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
Quote:
Originally Posted by 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 reletter 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 EyDz  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




05082008, 12:10 AM

#4

Recycles dryer sheets
Join Date: Oct 2006
Posts: 93

Quote:
Originally Posted by 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.

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.



05082008, 04:02 AM

#5

Recycles dryer sheets
Join Date: Sep 2004
Posts: 108

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 1020 categories?
Thanks for your help so far,
Petey
Quote:
Originally Posted by Average Joe
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.




05082008, 08:56 AM

#6

Recycles dryer sheets
Join Date: Oct 2006
Posts: 93

Quote:
Originally Posted by peteyperson
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 1020 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 3D 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 3D 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.
__________________




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)


Thread Tools 
Search this Thread 


Display Modes 
Linear Mode

Posting Rules

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off




» Recent Threads













» Quick Links


