Projecting Resources And Expenses In Excel 


Projecting Resources And Expenses In Excel  Excel 
View Answers 
Hi all,
*
I'm a new university student and*new to Excel 2007!**I'd like to create a worksheet for projecting expenses and resources for four years of college.
*
I have*expense data for the first year:
*
room & board**** $7,225.00
tuition & books*** $9,775.00
clothes****************** $850.00
food************ ****** $1,250.00
misc************* ****** $675.00
*
1. Based on the above data, I'd like to calculate*yearly*expenses*for the next 3 years by factoring in a*7.5% annual increase in each expense category.
*
How would I go about accomplishing this in Excel? What formulas should I be using, if any?* I'm not even sure if I should setup the expenses or years as*columns or rows, so I've created redundant tables just in case.*
*
2. Given the resources (I hope to have)*below, how would I show each funding source*as a percentage of total*college expenses over four years?
savings********** 7.5%
parents********** 25%
job*************** 12.5%
loans************* 40%
scholarships** 15%
I'd appreciate any help or*suggestions that can be provided!
*
Steve
*
I'm a new university student and*new to Excel 2007!**I'd like to create a worksheet for projecting expenses and resources for four years of college.
*
I have*expense data for the first year:
*
room & board**** $7,225.00
tuition & books*** $9,775.00
clothes****************** $850.00
food************ ****** $1,250.00
misc************* ****** $675.00
*
1. Based on the above data, I'd like to calculate*yearly*expenses*for the next 3 years by factoring in a*7.5% annual increase in each expense category.
*
How would I go about accomplishing this in Excel? What formulas should I be using, if any?* I'm not even sure if I should setup the expenses or years as*columns or rows, so I've created redundant tables just in case.*
*
2. Given the resources (I hope to have)*below, how would I show each funding source*as a percentage of total*college expenses over four years?
savings********** 7.5%
parents********** 25%
job*************** 12.5%
loans************* 40%
scholarships** 15%
I'd appreciate any help or*suggestions that can be provided!
*
Steve
Similar Excel Tutorials
How to Use the IF Function in Excel
The IF statement is a simple yet powerful tool. Today we will go through how the IF function works and some useful ...
The IF statement is a simple yet powerful tool. Today we will go through how the IF function works and some useful ...
Helpful Excel Macros
Create a Line Chart with a Macro in Excel
 Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
 Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Get Values from a Chart
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field  AutoFilter
 This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t
 This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t
Format Cells as a Percentage in Excel Number Formatting
 This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
 This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Similar Topics
Hi all,
I'm a new university student and new to Excel 2007! I'd like to create a worksheet for projecting expenses and resources for four years of college.
I have expense data for the first year:
room & board $7,225.00
tuition & books $9,775.00
clothes $850.00
food $1,250.00
misc $675.00
1. Based on the above data, I'd like to calculate yearly expenses for the next 3 years by factoring in a 7.5% annual increase in each expense category.
How would I go about accomplishing this in Excel? What formulas should I be using, if any? I'm not even sure if I should setup the expenses or years as columns or rows, so I've created redundant tables just in case.
2. Given the resources (I hope to have) below, how would I show each funding source as a percentage of total college expenses over four years?
savings 7.5%
parents 25%
job 12.5%
loans 40%
scholarships 15%
I'd appreciate any help or suggestions that can be provided!
Steve
I'm a new university student and new to Excel 2007! I'd like to create a worksheet for projecting expenses and resources for four years of college.
I have expense data for the first year:
room & board $7,225.00
tuition & books $9,775.00
clothes $850.00
food $1,250.00
misc $675.00
1. Based on the above data, I'd like to calculate yearly expenses for the next 3 years by factoring in a 7.5% annual increase in each expense category.
How would I go about accomplishing this in Excel? What formulas should I be using, if any? I'm not even sure if I should setup the expenses or years as columns or rows, so I've created redundant tables just in case.
2. Given the resources (I hope to have) below, how would I show each funding source as a percentage of total college expenses over four years?
savings 7.5%
parents 25%
job 12.5%
loans 40%
scholarships 15%
I'd appreciate any help or suggestions that can be provided!
Steve
Hi,
I am a using Excel 2013 and I am having trouble understanding why a formula I am working with isn't working.
The problem: So I am creating an Excel Worksheet that gets income and expense data from a MySQL database, imports it into tables and then formulas determine expenses and income for a given year. I have linked up MySQL and that's all working fine but my problem is with the expenses calculation.
The formula requires data from two sheets: Annual Expenses 2013: contains the calculations based on the Expenses sheet. It creates a table based on the categories made ([Expense Name]) in the MySQL database and then calculates from the Expenses sheet what expenses were made (expenses_totalsum) in that category given they were made in the a year specified in a cell (annual_expense_year). Expenses: contains the data imported from the MySQL database. The fields I am working with in this sheet are the category the expense is filed in (expenses_category_name) and the year the expense was made (expense_year).
So the formula I used in the first place was: =SUMIF(expenses_category_name,[Expense Name],expenses_totalsum). This would search through the category name in the Expenses sheet, match it with the Expense Name in the Annual Expenses 2013 sheet and return the SUM of said values that matched those criteria from the expenses_totalsum column in the Expenses sheet.
This all worked fine but now I need to introduce the year factor. So I need to achieve the same thing except any value it finds must also search the Expenses sheet for the year the expense was made and match it to the year set in the Annual Expenses 2013 Sheet.
This is the formula I have tried amongst other things but I can see where I have gone wrong: =SUMIFS(expenses_totalsum,expenses_category_name,[Expense Name],expenses_year,annual_expense_year) this just returns a value of 0.
Just a little extra info: the annual_expense_year value is a cell with the year in it within the Annual Expenses 2013 sheet. The expense_year is a column of data within the Expenses sheet (each expense is assigned a date and this column just displays the year of that date).
I can upload the worksheet if anyone want's it I just don't know if the data will also move with the sheet given the MySQL is on a localhost (for now).
Thanks for any help and I hope I have explain it well. I tried my best but it's a little complex.
My son is trying to track his expenses in Excel 2003. I am trying to help him so he doesn't have to copy and paste manually his expenses from the expense sheet into the checking worksheet. If the bank column has I in it then copy that expense to the Intrust expense. If the bank column has B in it then copy that expense to the Bank of America expense.
The expenses will be a running expense meaning that he will not be deleting the values once moved to the appropriate bank.
The test file is attached.
The expenses will be a running expense meaning that he will not be deleting the values once moved to the appropriate bank.
The test file is attached.
I am trying figure out the Years category, can anybody help me out?
Revenue $185,000,000
Expenses $300,000,000
Revenue Growth 0.2
Expense Growth 0.1
Years
Expected Revenue $185,000,000
Expected Expenses $300,000,000
Total Profit $(115,000,000)
Revenue $185,000,000
Expenses $300,000,000
Revenue Growth 0.2
Expense Growth 0.1
Years
Expected Revenue $185,000,000
Expected Expenses $300,000,000
Total Profit $(115,000,000)
Hi,
In table 1, i have the data entry for expenses as follows:
Sheet2
* B C D E F G H I 2 Table 1 * * * * * * * 3 Date Expenses Category Amount * * * * 4 11/06/2010 Biscuits Food 2.7 * * * * 5 * Commuter Commuter 7.6 * * * * 6 * Lunch Food 3.8 * * * * 7 * Lunch Food 3.5 * * * * 8 13/06/2010 Movie Entertainment 12 * * * * 9 * Breakfast Food 6.6 * * * * 10 * Nestum Food 9 * * * * 11 * Petrol Petrol 75 * * * * 12 14/06/2010 Commuter Commuter 7.6 * * * * 13 * Temple Temple 405 * * * * 14 * Biscuits Food 7 * * * * 15 * Insurance Insurance 62.5 * * * * 16 * Petrol Petrol 80 * * * * 17 * * * * * * * * 18 Table 2 * * * * * * * 19 Expenses Average Per Day * * * * * * 20 Temple 405 * * * * * * 21 Petrol 77.5 * * * * * * 22 Insurance 63 * * * * * * 23 Food 21 * * * * * * 24 Commuter 8 * * * * * * 25 Entertainment 12 * * * * * * 26 * * * * * * * * 27 * * * * * * * * 28 Average expenses is the total expense divided by the number of days in which the expense incurred. * * * * * * * 29 Example: Total amount for Petrol is $155. Total days in which the expense has incurred is 2 days. * * * * * * * 30 Therefore average is $155/2 = $77.5 * * * * * * *
Excel tables to the web >> Excel Jeanie HTML 4
in table 2, I need a formula to calculate the average expenses. The average expenses is calculated by dividing the total expenses by the number of days the expense has incurred.
Example: Total amount for Petrol is $155. Total days in which the expense has incurred is 2 days which is on 13/6/2010 and 14/06/2010,
Therefore average is $155/2 = $77.5
Is there a formula that I could use ? Thanks in advance.
In table 1, i have the data entry for expenses as follows:
Sheet2
* B C D E F G H I 2 Table 1 * * * * * * * 3 Date Expenses Category Amount * * * * 4 11/06/2010 Biscuits Food 2.7 * * * * 5 * Commuter Commuter 7.6 * * * * 6 * Lunch Food 3.8 * * * * 7 * Lunch Food 3.5 * * * * 8 13/06/2010 Movie Entertainment 12 * * * * 9 * Breakfast Food 6.6 * * * * 10 * Nestum Food 9 * * * * 11 * Petrol Petrol 75 * * * * 12 14/06/2010 Commuter Commuter 7.6 * * * * 13 * Temple Temple 405 * * * * 14 * Biscuits Food 7 * * * * 15 * Insurance Insurance 62.5 * * * * 16 * Petrol Petrol 80 * * * * 17 * * * * * * * * 18 Table 2 * * * * * * * 19 Expenses Average Per Day * * * * * * 20 Temple 405 * * * * * * 21 Petrol 77.5 * * * * * * 22 Insurance 63 * * * * * * 23 Food 21 * * * * * * 24 Commuter 8 * * * * * * 25 Entertainment 12 * * * * * * 26 * * * * * * * * 27 * * * * * * * * 28 Average expenses is the total expense divided by the number of days in which the expense incurred. * * * * * * * 29 Example: Total amount for Petrol is $155. Total days in which the expense has incurred is 2 days. * * * * * * * 30 Therefore average is $155/2 = $77.5 * * * * * * *
Excel tables to the web >> Excel Jeanie HTML 4
in table 2, I need a formula to calculate the average expenses. The average expenses is calculated by dividing the total expenses by the number of days the expense has incurred.
Example: Total amount for Petrol is $155. Total days in which the expense has incurred is 2 days which is on 13/6/2010 and 14/06/2010,
Therefore average is $155/2 = $77.5
Is there a formula that I could use ? Thanks in advance.
I am looking for a formula that will take a list of expenses and for an entire year and total each months expenses. I'm sure this is a simple formula but I am not too familiar with them.
I have two columns of data... (A & B for instance) Column A = Expense Amount Column B = Expense Date
I need to create another section that will have my expense catigories from left to right and months from top to bottom. Expenses for January 2008, February 2008, March 2008, etc. Hope this makes sense. Any help would be appriciated. Thank You!
I have two columns of data... (A & B for instance) Column A = Expense Amount Column B = Expense Date
I need to create another section that will have my expense catigories from left to right and months from top to bottom. Expenses for January 2008, February 2008, March 2008, etc. Hope this makes sense. Any help would be appriciated. Thank You!
I am looking for a formula that will take a list of expenses for an entire year and total each months expenses. I'm sure this is a simple formula but I am not too familiar with them.
I have two columns of data... (A & B for instance) Column A = Expense Amount Column B = Expense Date
I need to create another section that will have my expense catigories from left to right and months from top to bottom. Expenses for January 2008, February 2008, March 2008, etc. Hope this makes sense. Any help would be appriciated. Thank You!
I have two columns of data... (A & B for instance) Column A = Expense Amount Column B = Expense Date
I need to create another section that will have my expense catigories from left to right and months from top to bottom. Expenses for January 2008, February 2008, March 2008, etc. Hope this makes sense. Any help would be appriciated. Thank You!
1
Enter your name in cell B36 of the "5 Year Forecast" worksheet.
2
Complete the "5 Year Forecast" worksheet.
3
Use the cells in the Assumptions area to drive the calculations.
4
The user should be able to change any of the assumptions and the calculations should reflect the changed values.
5
"Year 1" should be replaced with the "first year of forecast" value from the assumptions area (i.e. 2010).
6
Annual Increases start in the second year.
7
Remember: Revenue  Expenses = Income (a.k.a. Earnings) (Expenses include fixed and variable expenses)
8
Remember: Variable Expenses are based upon the number of units sold!
9 Use the information below to create 3 Scenarios: Consensus, Optimistic and Pessimistic AND
create a Scenario Summary on a new worksheet showing the Earnings Before Taxes (EBT) for each year.
Income Revenues Jars Sold  Projected 100,000
Price per jar $5.00
Gross Revenue
Expenses Fixed Costs Production facility lease $50,000
Fixed Labor expense $40,000
Marketing expense $70,000
Administration expense $30,000
Total Fixed Costs
Variable Costs
Mfg cost per jar $2.00
Variable labor cost per jar $0.25
Total Variable Costs
Earnings Before Taxes
Assumptions Initial/First Year Values Annual Increase Maximum
First year sales (# of jars) 100,000
3.0%
Selling price per jar $5.00
5.0% $5.75
Production facility lease $50,000
8.0%
Fixed labor expense $40,000
3.0%
Marketing expense $70,000
10.0% $90,000
Administration expense $30,000
5.0%
Mfg cost per Jar $2.00
5.0%
Variable labor cost per jar $0.25
4.0%
First year of forecast 2010
Having so much trouble. Need to do it for 5 years
9 Use the information below to create 3 Scenarios: Consensus, Optimistic and Pessimistic AND
create a Scenario Summary on a new worksheet showing the Earnings Before Taxes (EBT) for each year.
Income Revenues Jars Sold  Projected 100,000
Price per jar $5.00
Gross Revenue
Expenses Fixed Costs Production facility lease $50,000
Fixed Labor expense $40,000
Marketing expense $70,000
Administration expense $30,000
Total Fixed Costs
Variable Costs
Mfg cost per jar $2.00
Variable labor cost per jar $0.25
Total Variable Costs
Earnings Before Taxes
Assumptions Initial/First Year Values Annual Increase Maximum
First year sales (# of jars) 100,000
3.0%
Selling price per jar $5.00
5.0% $5.75
Production facility lease $50,000
8.0%
Fixed labor expense $40,000
3.0%
Marketing expense $70,000
10.0% $90,000
Administration expense $30,000
5.0%
Mfg cost per Jar $2.00
5.0%
Variable labor cost per jar $0.25
4.0%
First year of forecast 2010
Having so much trouble. Need to do it for 5 years
I have 2 columns. Column A is Annual Expenses and Column B is Monthly Expenses. Each row is an expense category, e.g. Insurance, Taxes, Utilities, etc.
I want to allow someone to enter either an annual expense or a monthly expense. If he enters an Annual Expense (column A) then the Monthly Expense will be automatically calculated. If he enters a Monthly Expense (column B) then the Annual Expense will be automatically calculated.
What formulas do I use?
I want to allow someone to enter either an annual expense or a monthly expense. If he enters an Annual Expense (column A) then the Monthly Expense will be automatically calculated. If he enters a Monthly Expense (column B) then the Annual Expense will be automatically calculated.
What formulas do I use?
In the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?
=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100))
I would Like to SUM all expenses After the posted date including that date.
Thanx
Jim O
=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100))
I would Like to SUM all expenses After the posted date including that date.
Thanx
Jim O
Hi guys! First post and I'm pretty much a complete noob at Excel. I've set up a simple spreadsheet to keep track of my food expenses every month. The first column is for the date, the second is for the daily total expenses and then the next three columns are where I add the data which is then calculated into the daily total column.
I also have a total at the bottom for the entire month. Now what I want to do is I want to also have underneath the grand total, a cell which keeps track of my average daily expenses. Basically I want to divide the total expenses by the number of days which I've entered data. Now normally this would be fine but because I've applied the formula to all the cells in the expense column, it automatically lists every day as "0" rather than leaving it blank. So when it does the average calculation it's dividing my total by 30 days rather than by only the 7 days I have data for.
I suppose I could remove the formula =SUM(C1:E1) but that would mean that I would have to add up my expenses that day by myself and I'd rather let the program do it for me.
Can anyone help me out here?
I also have a total at the bottom for the entire month. Now what I want to do is I want to also have underneath the grand total, a cell which keeps track of my average daily expenses. Basically I want to divide the total expenses by the number of days which I've entered data. Now normally this would be fine but because I've applied the formula to all the cells in the expense column, it automatically lists every day as "0" rather than leaving it blank. So when it does the average calculation it's dividing my total by 30 days rather than by only the 7 days I have data for.
I suppose I could remove the formula =SUM(C1:E1) but that would mean that I would have to add up my expenses that day by myself and I'd rather let the program do it for me.
Can anyone help me out here?
I have a worksheet "Expenses" with columns Month, Category, Expense, Amount. There are four categories and, obviously, 12 months.
In the workbook is another worksheet, "Totals" which lists the months across the top, and the categories down the left. Then I have array formulas in each cell, that look like this:
=SUM(IF(Expenses!$B$2:$B$990="July",IF(Expenses!$C$2:$C$990="Acquisition",Expenses!$E$2:$E$990,0),0) )
This works fine, until people start moving cells around in the Expenses sheet. Say they sort everything they've put in by month, and then they want to move July up to the top because it's the start of our fiscal year. So say they have six expenses for July and they cut and insert them in at B2. Suddenly the formula now says:
=SUM(IF(Expenses!$B$8:$B$990="July",IF(Expenses!$C$8:$C$990="Acquisition",Expenses!$E$8:$E$990,0),0) )
I thought by using absolute cell references instead of relative ones, I could avoid this problem, but that's apparently not the case. I tried changing the cell references in the formulas to relative ones but it still happens then too.
Anyone have any thoughts? I'd really appreciate it.
Edited to add: this only appears to be the case if they cut and paste into B2  in other words, at the top of the sheet (below the header row). It doesn't seem to happen if they cut/paste cells in other parts of the worksheet. I'm not sure what this means for the issue.
In the workbook is another worksheet, "Totals" which lists the months across the top, and the categories down the left. Then I have array formulas in each cell, that look like this:
=SUM(IF(Expenses!$B$2:$B$990="July",IF(Expenses!$C$2:$C$990="Acquisition",Expenses!$E$2:$E$990,0),0) )
This works fine, until people start moving cells around in the Expenses sheet. Say they sort everything they've put in by month, and then they want to move July up to the top because it's the start of our fiscal year. So say they have six expenses for July and they cut and insert them in at B2. Suddenly the formula now says:
=SUM(IF(Expenses!$B$8:$B$990="July",IF(Expenses!$C$8:$C$990="Acquisition",Expenses!$E$8:$E$990,0),0) )
I thought by using absolute cell references instead of relative ones, I could avoid this problem, but that's apparently not the case. I tried changing the cell references in the formulas to relative ones but it still happens then too.
Anyone have any thoughts? I'd really appreciate it.
Edited to add: this only appears to be the case if they cut and paste into B2  in other words, at the top of the sheet (below the header row). It doesn't seem to happen if they cut/paste cells in other parts of the worksheet. I'm not sure what this means for the issue.
Hi,
I am creating an excel sheet for maintaining my daily expenses and balance for
each month and yearly for learning purpose.
so in the excel sheet i have taken the 31 or 30 days of each month as coloums
and my expenses as each row.
I am maintaining this format for one month for one sheet and so on.
I would like to report the expenses spent on particular items at the end of the month. Which I am able to do.
For example for the date 01/01/2009 i had the following expenses.
bus > $25
Food(lunch) > $100
Pen > $10
books > $15
vegetables > $20
These are individual expenses. and these individual expenses come under seperate categories. For examples
bus comes under Travelling
pen & books comes under Stationary
food and vegetables come under Food
now I want to generate report for Travelling , stationary, food for the whole month.
can any body please suggest how can i acheive this in Excel.
Your help is highly appreciated.
Thanks,
Aparicitudu.
I am creating an excel sheet for maintaining my daily expenses and balance for
each month and yearly for learning purpose.
so in the excel sheet i have taken the 31 or 30 days of each month as coloums
and my expenses as each row.
I am maintaining this format for one month for one sheet and so on.
I would like to report the expenses spent on particular items at the end of the month. Which I am able to do.
For example for the date 01/01/2009 i had the following expenses.
bus > $25
Food(lunch) > $100
Pen > $10
books > $15
vegetables > $20
These are individual expenses. and these individual expenses come under seperate categories. For examples
bus comes under Travelling
pen & books comes under Stationary
food and vegetables come under Food
now I want to generate report for Travelling , stationary, food for the whole month.
can any body please suggest how can i acheive this in Excel.
Your help is highly appreciated.
Thanks,
Aparicitudu.
I am making a personal expenses sheet to calculate my monthly expenses. i have each expense on my CC marked according to a validation list I have (for example Gas, Grocery, Shopping, etc)
How do i combine the Sum and Vlookup formulas to return the total amount spent on Gas for instance. This will be used for each of my type of expenses.
if its not possible, I can probably just write a macro
Thanks
How do i combine the Sum and Vlookup formulas to return the total amount spent on Gas for instance. This will be used for each of my type of expenses.
if its not possible, I can probably just write a macro
Thanks
I've created a spreadsheet showing expenses by month for many different expense catogories. I used the "sum" function, horizotally, to add the expenses to see the total for the year. I now want to create a new worksheet so I can see the monthy expenses for next year (2007) next to each month in 2006. When I changed the formula to just add the months for 2006 (I can't use the "sum" function because I need the expenses in every other column); I get a "#value" error in some of the rows. Any idea what the problem is? The link with the spreadsheet showing the error is:
http://cjoint.com/?lzvgh3pvpo
http://cjoint.com/?lzvgh3pvpo
I'm trying to create a basic set up to keep track of expenses.
If you check the image, I know how to automatically keep the total expenses, as you can see in E1  I used =SUM(B2:B9000)
I'd now like to be able to automatically calculate how much i'm spending per day. With the format you see i've created, what do I put in E3 to divide the total expenses by the number of days.
Also note that i'd like to spend two lines on one date, e.g. one line Feb  5 as taxi. The next line which is also Feb5, steak and wine for $42.
Another side question, i'd like to pull up the value of a cell from different tabs (e.g. pull up E1 in my expenses tab to another cell in another tab) how would i do this?
Thanks so much for your help!!
http://img.photobucket.com/albums/v220/fut/expenses.jpg
If you check the image, I know how to automatically keep the total expenses, as you can see in E1  I used =SUM(B2:B9000)
I'd now like to be able to automatically calculate how much i'm spending per day. With the format you see i've created, what do I put in E3 to divide the total expenses by the number of days.
Also note that i'd like to spend two lines on one date, e.g. one line Feb  5 as taxi. The next line which is also Feb5, steak and wine for $42.
Another side question, i'd like to pull up the value of a cell from different tabs (e.g. pull up E1 in my expenses tab to another cell in another tab) how would i do this?
Thanks so much for your help!!
http://img.photobucket.com/albums/v220/fut/expenses.jpg
Is there any specific template Excel which is aimed at personal expenses
calculation for students? I would like to see a template made keeping in mind
their expenses (monthly or fortnightly). My idea or rather wish is it should
be designed in a manner where say every day or probably every alternate day
or every 2 to 3 days one can add the expenses made includig petty expenses
such as a coffee. At the end of the month the person can collate the whole
data included in the worksheet and then see the expenses made in that month
against the earnings and which would facilitate a quick and easy analysis of
where the money is misspent or rather spent excessively. It would be a boon
for many students like me because i have experience in the past year of being
an international student and interacting with many students like me that most
of us are always in short of money the major reason being lack of control
over expenses and lack of tool which can ease the tracking of expenses
monthly and fortnightly in some cases as the pay comes fortnightly in most
part time jobs.
calculation for students? I would like to see a template made keeping in mind
their expenses (monthly or fortnightly). My idea or rather wish is it should
be designed in a manner where say every day or probably every alternate day
or every 2 to 3 days one can add the expenses made includig petty expenses
such as a coffee. At the end of the month the person can collate the whole
data included in the worksheet and then see the expenses made in that month
against the earnings and which would facilitate a quick and easy analysis of
where the money is misspent or rather spent excessively. It would be a boon
for many students like me because i have experience in the past year of being
an international student and interacting with many students like me that most
of us are always in short of money the major reason being lack of control
over expenses and lack of tool which can ease the tracking of expenses
monthly and fortnightly in some cases as the pay comes fortnightly in most
part time jobs.
I have a worksheet titled Expense Table. This expense table has in the leftmost column "expense ID" which is unique to each row. Then from left to right it has Product ID, Date, Amount.
For each product I have another worksheet that does certain calculations based on the expenses for that particular product. What I have been doing in the past is doing an autofilter on Product ID in the expense table and then copying and pasting those records into the cells that I have designated for them in the corresponding Product worksheet. They need to stay in this same format with the same headers.
The expense table gets updated and occasionally I will copy in an updated master list of expenses. When I do this I then have to autofilter and copy the expenses into the Product worksheets again. Each product worksheet has the product ID on it that corresponds to the product IDs in the expense table. I would like to somehow do a lookup based on the IDs and have it automatically copy in the Expense ID, Product ID, Date, Amount from the expense table. In each Product worksheet I have calculations and other information at the top and even next to the cells that I copy in. So in this case I would need the information to copy in as it is in the expense table and in cells
A21,B21,C21,D21
A22,B22,C22,D22
etc
There could be no expenses or there could be 100 and I just need them to go down as far as need be.
I have a fair amount of knowledge with Excel but this is past my level of expertise and this seemed like the best place to come for an answer. I hope all of that makes sense. Thanks in advance.
For each product I have another worksheet that does certain calculations based on the expenses for that particular product. What I have been doing in the past is doing an autofilter on Product ID in the expense table and then copying and pasting those records into the cells that I have designated for them in the corresponding Product worksheet. They need to stay in this same format with the same headers.
The expense table gets updated and occasionally I will copy in an updated master list of expenses. When I do this I then have to autofilter and copy the expenses into the Product worksheets again. Each product worksheet has the product ID on it that corresponds to the product IDs in the expense table. I would like to somehow do a lookup based on the IDs and have it automatically copy in the Expense ID, Product ID, Date, Amount from the expense table. In each Product worksheet I have calculations and other information at the top and even next to the cells that I copy in. So in this case I would need the information to copy in as it is in the expense table and in cells
A21,B21,C21,D21
A22,B22,C22,D22
etc
There could be no expenses or there could be 100 and I just need them to go down as far as need be.
I have a fair amount of knowledge with Excel but this is past my level of expertise and this seemed like the best place to come for an answer. I hope all of that makes sense. Thanks in advance.
Hey all,
First time posting (and yes, I searched, but couldn't find what I was looking for) and figured this was the right place to come for help. I'll do my best to explain my issue.
I have a workbook with 13 worksheets. One is my entire 2009 budget, the following 12 are one for each month.
On the annual budget, I have it set to show: Available cash, additional income, Projected expenses, actual expenses, planned future expenses, total actual expenses, and then savings, if any. I have used a formula that pulls the actual expenses from each month, and then using conditional formatting and IF statements, it shows me Under or Over.
My problem is this: I want it to mimic my checking account, since that's what I'll be using to pay for everything. The problem is, when I transfer money from checking to savings, say, $500, that will be counted as part of my expenses, when in fact it is not. I want the ending sum to include all categories except transfers.
I have each month set up as follows:
Date  Date Posted  Description  Category  Debit  Credit  Balance
The category column uses data validation with a list (such as ATM, Transportation, Wages, Fees, Food, Transfers, etc...)
Is there a way to create a formula to say that if "category" is "transfer" it is not included in the ending balance? I've tried using IF and SUMIF, and just can't figure it out .
I apologize for typing so much, but I know that details help to solve a problem.
Thanks in advance!
First time posting (and yes, I searched, but couldn't find what I was looking for) and figured this was the right place to come for help. I'll do my best to explain my issue.
I have a workbook with 13 worksheets. One is my entire 2009 budget, the following 12 are one for each month.
On the annual budget, I have it set to show: Available cash, additional income, Projected expenses, actual expenses, planned future expenses, total actual expenses, and then savings, if any. I have used a formula that pulls the actual expenses from each month, and then using conditional formatting and IF statements, it shows me Under or Over.
My problem is this: I want it to mimic my checking account, since that's what I'll be using to pay for everything. The problem is, when I transfer money from checking to savings, say, $500, that will be counted as part of my expenses, when in fact it is not. I want the ending sum to include all categories except transfers.
I have each month set up as follows:
Date  Date Posted  Description  Category  Debit  Credit  Balance
The category column uses data validation with a list (such as ATM, Transportation, Wages, Fees, Food, Transfers, etc...)
Is there a way to create a formula to say that if "category" is "transfer" it is not included in the ending balance? I've tried using IF and SUMIF, and just can't figure it out .
I apologize for typing so much, but I know that details help to solve a problem.
Thanks in advance!
I have 2 columns on an expense sheet, 1 for company expenses and 1 for billable expenses then a column with amounts. I want to sum all the values for the company expenses and billable expenses seperately but each item has a different value (ex: parking NYC). How can i do that with sumif or sumifs to just sum for any text value?
Hi everybody,
If anybody can help me on this...
I am doing a follow up expenses with differentes categories.
On each category I have a macro to add a line everytime I have a new expense.
The file is presented like this:
ColumnB: row 26 for example: Title: "Category 1: Getting help for excel"
and then under all the expenses with 1 macro to add a line when necesary.
on this column on each line I have left =B$26, then folowing =B$27....to be able to count all expenses under the same value on the right category.
My goal: I would also like to use Autofilter to analize some category of expenses. So in this example choosing all expense linked with title"Category 1: getting Help for excel".
My Probleme: If I choose this category I have only main title and not all expenses lines as in column B it is equal to main title line but not appear in text.
My first try: I have first add in my macro to do in a way that in column B these =$B26...appear in text so it works but then...if I decide to change main title expenses lines do not change.
My question: would there be a way through the macro to add expense line and that in Column B cells appear in text but that it is still equal to =B$26... for example and change when title change...
or other solution...
Soorry for being long and thanks for any help on this,
Mika
If anybody can help me on this...
I am doing a follow up expenses with differentes categories.
On each category I have a macro to add a line everytime I have a new expense.
The file is presented like this:
ColumnB: row 26 for example: Title: "Category 1: Getting help for excel"
and then under all the expenses with 1 macro to add a line when necesary.
on this column on each line I have left =B$26, then folowing =B$27....to be able to count all expenses under the same value on the right category.
My goal: I would also like to use Autofilter to analize some category of expenses. So in this example choosing all expense linked with title"Category 1: getting Help for excel".
My Probleme: If I choose this category I have only main title and not all expenses lines as in column B it is equal to main title line but not appear in text.
My first try: I have first add in my macro to do in a way that in column B these =$B26...appear in text so it works but then...if I decide to change main title expenses lines do not change.
My question: would there be a way through the macro to add expense line and that in Column B cells appear in text but that it is still equal to =B$26... for example and change when title change...
or other solution...
Soorry for being long and thanks for any help on this,
Mika
I have this troublesome formula I've been working on.
According to what I've read so far in this forum, this will be cakewalk for you guys to find out of (or at least so I hope.. :)
I don't know much about Excel, and what I do know is just logic sense, and a bit of common math.
My problem is in a sheet I use for my personal economy. (I'm a neatfreak, and a nerd...)
The logic of my sheet is that I seperate the different type of monthly expenses, and sum them individually. The way I do this is that the A Column identyfies an expense (where 1 = rent, 2 = food, 3 = gas etc) while the C column is the expense itself, Example:
A B C
On account 500
1 rent 200
2 food 20
3 gas 10
2 food 10
Sum expenses 240
On account 260
I then use this formula to discern them:
{=ABS(SUM(IF(A$4:A$30=X;C$4:C$30;0)))}
where X is substituted for the number in the A column.
1 would give 200
2 would give 30
3 would give 10
This works swimmingly. The problem comes he
A B C
On account 500
1 rent 200
2 food 20
3 gas 10
2 food 10
4 to saving 100
Sum expenses 340
On account 160
As you see, what is going to my saving account is registered as an expence, which isn't right... (I want the sum expenses to still be 240) So I've tried to not have the "type 4" to be registered as an expense (but it still needs to be in there, to balance the account) So I made this formula for "Sum expenses":
{=SUM(C4:C30)+(IF(A$4:A$30=4;ABS(C$4:C$34);0))}
What this is supposed to do is to take the sum of the expenses, and add whatever is marked with 4 (that is 340+100=240). This doesn't work. I've tried changing it around a bit, and for some reason, the logical test in the IF part never gets "true", even though it "should" . If I simplify:
{=SUM(C4:C30)+(IF(A$4:A$30=4;5;7))}
The value of the cell will always end with a 7, never a 5, which it should if one of the A columns contained a 4.. (assuming, of course that the last digit of the original sum is 0, as in the examples...)
I probably fail to see some logic Excel finds obvious..
Any clue?
PS: I can upload a clean copy of the sheet, if the problem isn't understandable.. I am however using a nonenglish Excel, do the formulas translate to other versions?
According to what I've read so far in this forum, this will be cakewalk for you guys to find out of (or at least so I hope.. :)
I don't know much about Excel, and what I do know is just logic sense, and a bit of common math.
My problem is in a sheet I use for my personal economy. (I'm a neatfreak, and a nerd...)
The logic of my sheet is that I seperate the different type of monthly expenses, and sum them individually. The way I do this is that the A Column identyfies an expense (where 1 = rent, 2 = food, 3 = gas etc) while the C column is the expense itself, Example:
A B C
On account 500
1 rent 200
2 food 20
3 gas 10
2 food 10
Sum expenses 240
On account 260
I then use this formula to discern them:
{=ABS(SUM(IF(A$4:A$30=X;C$4:C$30;0)))}
where X is substituted for the number in the A column.
1 would give 200
2 would give 30
3 would give 10
This works swimmingly. The problem comes he
A B C
On account 500
1 rent 200
2 food 20
3 gas 10
2 food 10
4 to saving 100
Sum expenses 340
On account 160
As you see, what is going to my saving account is registered as an expence, which isn't right... (I want the sum expenses to still be 240) So I've tried to not have the "type 4" to be registered as an expense (but it still needs to be in there, to balance the account) So I made this formula for "Sum expenses":
{=SUM(C4:C30)+(IF(A$4:A$30=4;ABS(C$4:C$34);0))}
What this is supposed to do is to take the sum of the expenses, and add whatever is marked with 4 (that is 340+100=240). This doesn't work. I've tried changing it around a bit, and for some reason, the logical test in the IF part never gets "true", even though it "should" . If I simplify:
{=SUM(C4:C30)+(IF(A$4:A$30=4;5;7))}
The value of the cell will always end with a 7, never a 5, which it should if one of the A columns contained a 4.. (assuming, of course that the last digit of the original sum is 0, as in the examples...)
I probably fail to see some logic Excel finds obvious..
Any clue?
PS: I can upload a clean copy of the sheet, if the problem isn't understandable.. I am however using a nonenglish Excel, do the formulas translate to other versions?
Hi,
I have the following expenses in table 1.
Sheet2
* B C D E F G H 1 Table 1 * * * * Table 2 * 2 Date Expenses Amount * * Expenses Frequency 3 02Jul Books $5.00 * * Petrol 3 4 03Jul Petrol $75.00 * * * * 5 04Jul Movie $10.00 * * * * 6 04Jul Groceries $25.00 * * * * 7 03Jul Insurance $65.00 * * * * 8 02Jul Drinks $20.00 * * * * 9 05Jul Petrol $80.00 * * * * 10 06Jul Books $5.00 * * * * 11 08Jul Petrol $75.00 * * * * 12 01Jul Movie $10.00 * * * * 13 06Jul Groceries $25.00 * * * * 14 04Jul Insurance $65.00 * * * * 15 10Jul Drinks $20.00 * * * * 16 12Jul Petrol $80.00 * * * *
Excel tables to the web >> Excel Jeanie HTML 4
In Table 2, I would like to know how many days in average until the next expenses incurres.
Example Petrol was incurred on July 3 2010. Then on July 5 2010, the same expense incurred again. This means from the first date of the expense to the next date of the expense, there was a difference of 2 days.Then again the expense incurred on 8 July 2010. This is a difference of 3 days from the last expense on 5 July 2010.Then the expense for petrol incurred again on 12 July 2010. This is another 4 days difference from the last expense on 8 July 2010. By summing the number of days ( 2+3+4) we get 10 days and the average would be (9/3) 3.00
Please note that the data entry of expenses does not necessary entered in a chronological order. The dates can be in a random order as in column B.
Is there a way to achieve this via a formula for the desired result in cell H3 ?
Thanks in advance.
I have the following expenses in table 1.
Sheet2
* B C D E F G H 1 Table 1 * * * * Table 2 * 2 Date Expenses Amount * * Expenses Frequency 3 02Jul Books $5.00 * * Petrol 3 4 03Jul Petrol $75.00 * * * * 5 04Jul Movie $10.00 * * * * 6 04Jul Groceries $25.00 * * * * 7 03Jul Insurance $65.00 * * * * 8 02Jul Drinks $20.00 * * * * 9 05Jul Petrol $80.00 * * * * 10 06Jul Books $5.00 * * * * 11 08Jul Petrol $75.00 * * * * 12 01Jul Movie $10.00 * * * * 13 06Jul Groceries $25.00 * * * * 14 04Jul Insurance $65.00 * * * * 15 10Jul Drinks $20.00 * * * * 16 12Jul Petrol $80.00 * * * *
Excel tables to the web >> Excel Jeanie HTML 4
In Table 2, I would like to know how many days in average until the next expenses incurres.
Example Petrol was incurred on July 3 2010. Then on July 5 2010, the same expense incurred again. This means from the first date of the expense to the next date of the expense, there was a difference of 2 days.Then again the expense incurred on 8 July 2010. This is a difference of 3 days from the last expense on 5 July 2010.Then the expense for petrol incurred again on 12 July 2010. This is another 4 days difference from the last expense on 8 July 2010. By summing the number of days ( 2+3+4) we get 10 days and the average would be (9/3) 3.00
Please note that the data entry of expenses does not necessary entered in a chronological order. The dates can be in a random order as in column B.
Is there a way to achieve this via a formula for the desired result in cell H3 ?
Thanks in advance.
Hi
i am looking for a macro to generate all possible combinations of N elements in C columns
for example I have 3 columns
CoL 1 Account
Col 2 Month
Col 3 Year
Independant of number of elements in each column I want to get all possible combinations of 3 elements ( not 2 or 1 element only )
example
Base is
ACCOUNT MONTH YEAR Sales jan 2007 Expenses feb 2008 Material mar 2009
apr
may
jun
jul
aug
number of combination is 3 x 8 X 3 in that case 72 lines to generate but keeping data in columns ( not concatenate field)
result is 72 lines in 3 columns
ACCOUNT MONTH YEAR Sales jan 2007 Sales feb 2007 Sales mar 2007 Sales apr 2007 Sales may 2007 Sales jun 2007 Sales jul 2007 Sales aug 2007 Expenses jan 2007 Expenses feb 2007 Expenses mar 2007 Expenses apr 2007 Expenses may 2007 Expenses jun 2007 Expenses jul 2007 Expenses aug 2007 Material jan 2007 Material feb 2007 Material mar 2007 Material apr 2007 Material may 2007 Material jun 2007 Material jul 2007 Material aug 2007 Sales jan 2008 Sales feb 2008 Sales mar 2008 Sales apr 2008 Sales may 2008 Sales jun 2008 Sales jul 2008 Sales aug 2008 Expenses jan 2008 Expenses feb 2008 Expenses mar 2008 Expenses apr 2008 Expenses may 2008 Expenses jun 2008 Expenses jul 2008 Expenses aug 2008 Material jan 2008 Material feb 2008 Material mar 2008 Material apr 2008 Material may 2008 Material jun 2008 Material jul 2008 Material aug 2008 Sales jan 2009 Sales feb 2009 Sales mar 2009 Sales apr 2009 Sales may 2009 Sales jun 2009 Sales jul 2009 Sales aug 2009 Expenses jan 2009 Expenses feb 2009 Expenses mar 2009 Expenses apr 2009 Expenses may 2009 Expenses jun 2009 Expenses jul 2009 Expenses aug 2009 Material jan 2009 Material feb 2009 Material mar 2009 Material apr 2009 Material may 2009 Material jun 2009 Material jul 2009 Material aug 2009
Thanks in Advance for helping
i am looking for a macro to generate all possible combinations of N elements in C columns
for example I have 3 columns
CoL 1 Account
Col 2 Month
Col 3 Year
Independant of number of elements in each column I want to get all possible combinations of 3 elements ( not 2 or 1 element only )
example
Base is
ACCOUNT MONTH YEAR Sales jan 2007 Expenses feb 2008 Material mar 2009
apr
may
jun
jul
aug
number of combination is 3 x 8 X 3 in that case 72 lines to generate but keeping data in columns ( not concatenate field)
result is 72 lines in 3 columns
ACCOUNT MONTH YEAR Sales jan 2007 Sales feb 2007 Sales mar 2007 Sales apr 2007 Sales may 2007 Sales jun 2007 Sales jul 2007 Sales aug 2007 Expenses jan 2007 Expenses feb 2007 Expenses mar 2007 Expenses apr 2007 Expenses may 2007 Expenses jun 2007 Expenses jul 2007 Expenses aug 2007 Material jan 2007 Material feb 2007 Material mar 2007 Material apr 2007 Material may 2007 Material jun 2007 Material jul 2007 Material aug 2007 Sales jan 2008 Sales feb 2008 Sales mar 2008 Sales apr 2008 Sales may 2008 Sales jun 2008 Sales jul 2008 Sales aug 2008 Expenses jan 2008 Expenses feb 2008 Expenses mar 2008 Expenses apr 2008 Expenses may 2008 Expenses jun 2008 Expenses jul 2008 Expenses aug 2008 Material jan 2008 Material feb 2008 Material mar 2008 Material apr 2008 Material may 2008 Material jun 2008 Material jul 2008 Material aug 2008 Sales jan 2009 Sales feb 2009 Sales mar 2009 Sales apr 2009 Sales may 2009 Sales jun 2009 Sales jul 2009 Sales aug 2009 Expenses jan 2009 Expenses feb 2009 Expenses mar 2009 Expenses apr 2009 Expenses may 2009 Expenses jun 2009 Expenses jul 2009 Expenses aug 2009 Material jan 2009 Material feb 2009 Material mar 2009 Material apr 2009 Material may 2009 Material jun 2009 Material jul 2009 Material aug 2009
Thanks in Advance for helping
Hi everyone,
So here is what I am trying to do:
I have four tables  Tables Q1Exp, Table Q2Exp, Table Q3Exp and Table Q4Exp. They have expense category data stored in them by quarter. A brief ex below:
Jan Feb Mar Total Raw material G&A
The above is an excel table as expense categories can increase. Generally the no. of months in a quarter remain unchanged but in my original table even the columns can vary. The user will input the actual expenses per month per quarter in the four tables described above (each of them are pretty large so I have made them on different sheets. However they are named so that shouldn't be an issue in my humble opinion  I could be wrong as I am extremely new to Excel and VBA for Excel) I then have a summary table that tracks, for the purpose of our example, the yearly expenses. I want the actual expenses to be automatically populated in this table for our four editable tables. This table looks like the following:
Q1 Q2 Q3 Q4 Raw materials G&A Total
My logic says that my VBA code should look up the column name of the active cell (doable) and concatenate it with 'Exp' to get the table name (doable). Now I want to look up the row name (doable) and fetch the data from the totals column of same row of the table name. This is where I am stuck. Is there a way, in VBA for Excel, for a variable to be used as a Table name?
I will be extremely grateful for any help and am ready to answer any queries.
Thank you in advance for your time and your help.
Regards,
Alpana SAVARNA