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



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

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







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.


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!


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?


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.


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



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?


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 non-english Excel, do the formulas translate to other versions?


Morning,
I have a file that has a table containing various expenses cost lines about 8. The table is filtered using a drop down list of about 35 businesses all numbered using 4 digits. E.g business number 1189 . The problem with this table is that it only holds 1 week worth of expenses for each business. I want to copy these Figures to another workbook with each businesses on each separate sheets so I can track each businesses expenses week by week e.g

Business 1189

Wk 1. Wk2. Wk 3. Wk 4
Expense 1
Expense 2
Expense 3
Expense 4

Is there a code to do this. The file name for the expense is named wk1 or wk2 etc the new file is called total expenses

Thanks in advance
Alan


am trying to create an expense chart for my company. There are a total of 6 people and i want to keep a track of all expense on a monthly basis. The expenses would be given to me via email or hard copy and I have to basically fill out my expense chart and come up with total expenses for the month. This is my first project in the company and want to make it look nice. any ideas would be greatly appreciated


I am trying to create an expense chart for my company. There are a total of 6 people and i want to keep a track of all expense on a monthly basis. The expenses would be given to me via email or hard copy and I have to basically fill out my expense chart and come up with total expenses for the month. This is my first project in the company and want to make it look nice. any ideas would be greatly appreciated.


Hello all, I am new to excel and new to these forums so please be gentle! I hope I can explain my problem well enough for someone to be able to help.

Basically I want to use excel to create a budget for my day-to-day and long term expenses as I have just moved into a new flat. I move my money every month to different bank accounts, depending on whether it is a monthly payment, yearly payment, or long term savings.

In one column I have a list of expenses: rent, travelcard, council tax, gas, electricity etc
in the second column I have the amount that expense costs.
In the third I have set up a drop-down box depending on which account the money is to be moved to, i.e. "monthly payment", "yearly payment", "keep in current account", "savings account". To help you visualise, things like rent, gas, electric, phone bill go to the monthly payment account, things like car tax, tv license and car MOT go to the yearly payment account, as it;s an easy way for me to split the costs month-by-month and dig out the account card once a year to pay them.

underneath these columns I want to be able to set three or four formulae, I.e. total amount going to monthly payments account, total amount going to yearly payments account, and total amount going to long-term savings account.

I know I could just do a simple SUM formula and select the expenses individually, but I really want it to change automatically based on which account is selected from the drop down box, as you and I all know that new charges and expenses crop up every month and this would allow me the freedom to add more expenses later, or even change which account I put the money in.

Thanks for any help you can provide, I hope I haven't confused you. Also, for what it's worth I created the drop-down list by writing the list on a separate sheet then going back to my "main" sheet and creating a validation data drop-down list.


I have attempted to solve this last problem for an hour now and can't seem to find the right formula. Can anyone help? I have attached the given information in excel format and the problem reads the following:

Xmark.com is a major internet company specializing in organic food. Xmark.com is thinking of purchasing GoodGrow, another organic food internet company. GoodGrow has current revenues of $100 million,with expenses of $150 million. Current projections indicate that Good Grow's revenues are increasing at 35 percent per year and its expenses are increasing by 10 percent per year. XMark.com understands that projections can be erroneous, however; the company must determine the number of years before GoodGrow will return a profit.

Project Focus

You need to help Xmark.com determine the number of years required to break even, using annual growth rates in revenue between 20 percent and 60 percent and annual expense growth rate between 10 and 30 percent.


Hi Guys,

I'm having a bit of an issue.

I want to be able to sum expenses for the year-to-date ie. If I want the sum for the expense item to March, I want to be able to type in March and it sums up the expense up until March.

The other issue is that I want to combine a "vlookup" type entry into this formula. I want it to look for the specific expense and then sum up the expense up until any desired month.

The reason for this is that the expense is situated on different rows in the different worksheets.

Is this at all possible - I hope I've explained myself correctly...

Please help.

Thanks




I have an expense handbook that I track my expenses and incomes. In the book I use certain codes for various expenses such as fuel, breakdowns and utilities. They vary month to month, so there's no set amount of each expense I will have for any particular month; as well as what order they would be written. I write them down as they are expensed. Is there a way to enter an expense with a code independently and have it total into another cell for a total spent for that expense for that month?

I am trying to sum data within a database taking the criteria from 2 columns. For example, there is a column for a job name, expense type, month of the expenses, expense amount. What I would like to do is sum all of the expenses in the expense column from a certain month and a certain expense. My overal goal is to create a P & L statement for every month from data which is entered into 1 large spreadsheet.

Someone please help!


Hi, i am hoping someone would be able to suggest to me the best way of achieving the following task.

I work in accounts and we currently have our wages expenses split across multiple departments within the business. The problem i have is that it is only the wages that are being split by our accounting software, the facility does not exist to split other such expenses equitably.

What i have done is setup a spreadsheet where there is a sheet that contains all the employees and then a % that is applicable to each department. I then have each month as a seperate sheet, which looks up the percentage and applys the appropriate % to the expense amount (input from a report generate by the software).

There are many other expenses such as annual leave / long service leave / etc that i would like to split in much the same way. The problem i see however is that i would need a sheet within each of these workbooks for the splits and they would all have to be updated each time a new employee was added or deleted which could become a bit tedious.

Any suggestions as to the best way to go about solving this problem would be appreciated.


I am trying to calculate the percent difference between two expenses. The expenses are positive numbers unless there is a credit balance, which is negative.

Last year exp is (477)
This year exp is 7,496

if I set up the formula in a column and drop-handle down in multiple rows (7,496-(477))/(477) I get a -1671% (decrease)in expenses over last year, which is opposite of what it should be.

What formula can I use to correct the issue. I must keep the expense numbers as positives unless it is a credit balance. I am also using the ISERROR to get rid of the DIV 0! messages.

Thanks.


Hello,
Here i am with a stupid query.
I need a excel macro sheet that can do the following tasks.

1. When it opens it should ask password to access the sheet.

2. after that , a popup FORM should open and ask the details:

2a). The Forum should include : Date, Expenses A, Expenses B, Expenses C, Expenses D. after given all the information, the form should automatically updated with the total Expenses.

3. I need Each Month on Each Sheet in a work book. Can any one provide this Please.

Thanking you in advance,
Krish.


Hi Folks,

Just want to say I'm so glad I came across this forum. Been searching the internet for days trying to find a tutorial on what I'm trying to achieve. Unfortunately, I've had no success.

I've attached a MS Excel spreadsheet I've created [Filename: 032209_Expense_Report.xlsx].

There are couple of places where I'm stuck and I hope one of you can help:
(the points marked with a * aren't crucial to my requirements)

1. When I add more rows to the table I have to manually select the last cell and pull the formulas down to populate them into the new rows. I know it takes just a little effort but more automation would be great. Not a must but would appreciate some tips and tricks. *

2. To retrieve the Savings amount I have merely referenced the last cell in the Balance column. Just curious if there's a "smarter" way to achieve this. *

3. Day - Is there a way for this column to state the day based on the information from the Date column? *

4. Expense By Type
This is the part I'm really, really stuck at.

Here's an example of what I'd like to do;

The value displayed in cell C19 should be the sum of all "Transportation" expenses available in the table (From column G - Amount). I might have one Transportation expense on 22 Mar, another on 25 Mar, a third on 31 Mar and so and so forth. I'm interested in only the total "Transportation" expenses being displayed in cell C19.

Likewise, the remaining expense types will only show their totals.

I would really appreciate if somebody out there can assist me with this.