Projecting Resources And Expenses In Excel

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

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.

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

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

Hey I'm making a budgeting worksheet, and I'm beating my head on a wall trying to figure out how to perform a seemingly simple task. I've done a few hours of searching and playing around, but can't seem to get what I want. So if this has been answered similarly, I apologize.

There are two sheets, one named "Bi-Weekly Budget" and the other "Expenses". On the "Expenses" sheet, I have a table of expenses that is set-up thus:

A - Expense Name
B - Cost Per Month
D - Day of Expense

What I am trying to do, on a bi-weekly calendar I setup, is compare 2 pay dates, and see if the "Day of Expense" falls between them, and if so put the "Cost Per Month" value into the calendar. Now I can compare two days easily with the formula below, but the problem comes in when I compare between two months.

Below you can clearly tell that the 30th falls between the 2 dates, but the function wont work properly as is.

Example:
"Bi-Weekly Budget" Sheet
D1="1/29/2010"
E2="2/12/2010"

"Expense" Sheet
D2=30

=IF(Expenses!D2="","0",IF(AND(Expenses!D2<DAY('Bi-Weekly Budget'!$E$1), Expenses!D2>DAY('Bi-Weekly Budget'!$D$1)), Expenses!B2, "0"))

Any help for this n00b would be much appreciated.