Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Projecting Resources And Expenses In Excel

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

View Answers     

Similar Excel Video Tutorials

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
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
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
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
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

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




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.


For example, I have 4 expenses, and a line that sums all expenses. One of the expenses is calcuated as 10% of the total.

Expense 1: $100
Expense 2: $ 50
Expense 3: $ 50
Expense 4: 10% of total

SUM expenses1-4.

The result should be that Expense 4 displays $22.22 and the total would be $222.22.

Thanks in advance.


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)


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.


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 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!


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



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?

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


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?


Hello Folk,

I have been picking my minds for a few days. I tried using conditional Sum.

ex.

Date Type Tax Total
02/05/2008 Gas .12 10.00
02/09/2008 Books .14 12.00
03/10/2008 Food .10 1.00
03/18/2008 Tickets .12 3.00
03/19/2008 Gas .15 4.00

i want another page to add total of each month expenses just all of feb, then march, i know i can add each one up, but an on going total . help please. i came up with

=SUM(IF(Expenses!$A$6:$A$2300>DATEVALUE("2/1/2008"),IF(Expenses!$A$6:$A$2300<"02/30/08",Expenses!$F$6:$F$2300,0),0))

but, it ends up adding everything together not just the dates btw 02/01/2008 - 02/31/2008


Hi,

I am trying to create a function that would do the following:
you have a maximum claim that you can expense per year(10; column 1), and you have your expenses (column 2). However, if your expenses are greater that the maximum expense for the year, you can carry it forward to the next year and claim in in following years.
Basically I want to set up a function that replicate the claimed column
Hope this make sense

Many thanks for your help

Maximum Expense Expense Claimed
10....................... 7 .............7
10....................... 9 .............9
10 ...................... 12............10
10 ....................... 13............10
10.......................... 2............7
10..........................11............10
10...........................8............9


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.


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 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


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


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 Feb-5, 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.



Hi everyone and a blessed day to all. Is it true I can find some Excel help here? While I have been using Excel for several years, I have never gotten to the area to really set up a proper work sheet. Now I am having to do it, I have no clue and I cant search for help as the pc I am using in in a foreign lauguage and each time I click help it reverts back to this foreign language that I do not know.

Basically, I need help setting up an expense sheet for Jan to Dec, then in the sheet there are expenses as far as the eye can see, some of the expenses are set per month, others are added to like gasoline is changing weekly, I am at this point, having to manually calculate the old gas bill and new amount then reenter in the area next to gasoline. Is there a better and easier way to do this for the particular expenses that are not fixed? About half are fixed monthly bills and half fluctuate during the month depending on useage.

Any help at all will be very appreciated.

Have a great day
Pippy


My assignment is to make a budget worksheet it should have a row to show income for each month Jan.-Dec. and properly label the row and value. Expenses should be added in a separate row and properly label the row and value. For the expenses and income, create a year's history by month.Create formulas or use the Autosum function to calculate total expense for each month. Create formulas or use the Autosum function to have Excel compute the Total Annual Income and the Total Annual Expenses and properly label each. Name the worksheet tab "Budget Details".

I need someone to check my assignment and tell me anything else that I can do to make it better. Points are awarded for the relative merits of the way the worksheets are created including appropriate layout of the worksheet for the information included and the appropriate use of the Excel features.


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.


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!


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?