Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Help needed on Excel spreadsheet design
Old 05-07-2008, 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 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
__________________

__________________
peteyperson is offline   Reply With Quote
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 Early-Retirement.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!

Old 05-07-2008, 10:39 PM   #2
Recycles dryer sheets
Average Joe's Avatar
 
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 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 is offline   Reply With Quote
Old 05-07-2008, 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 View Post
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 is offline   Reply With Quote
Old 05-08-2008, 12:10 AM   #4
Recycles dryer sheets
Average Joe's Avatar
 
Join Date: Oct 2006
Posts: 93
Quote:
Originally Posted by peteyperson View Post
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.
__________________
Average Joe is offline   Reply With Quote
Old 05-08-2008, 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 10-20 categories?

Thanks for your help so far,
Petey

Quote:
Originally Posted by Average Joe View Post
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 is offline   Reply With Quote
Old 05-08-2008, 08:56 AM   #6
Recycles dryer sheets
Average Joe's Avatar
 
Join Date: Oct 2006
Posts: 93
Quote:
Originally Posted by peteyperson View Post
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.
__________________

__________________
Average Joe is offline   Reply With Quote
Reply


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

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to design a portfolio with 10% p.a. return? shorthair FIRE and Money 26 03-10-2008 05:08 PM
Excel stock quote add-in hlep needed Gonzo Other topics 4 09-01-2006 11:45 AM
ER not by design.... Delawaredave FIRE and Money 28 08-03-2006 07:32 PM

 

 
All times are GMT -6. The time now is 05:17 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.