+ Reply to Thread
Results 1 to 14 of 14

Calculate Average Weekly Income for Business

  1. #1
    Registered User
    Join Date
    03-03-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Talking Calculate Average Weekly Income for Business

    I have created a table for a business to monitor all income and expenses within a financial year of that business. This table contains raw data for example, date, income/expense, wholesale amount, retail amount etc. So when the business makes a sale for example I enter in the date of the sale, type of sale, wholesale and retail amounts etc, and i do this for every sale I make.

    What i need to do is figure out a way to calculate the average monthly and weekly income to date so as the financial year continues and i make more sales i will continue to enter more data into this table and it will automatically adjust to the new weekly or monthly average income. This way i always have an actual and live average for every cent the business has made as the year progresses and i can use this to budget for the business. I would also like to know what the income is for the current week or month. that way i can say for example on average the business makes $4000 a week and in this week it has made say $5000.

    I thought this would be simple to do on excel but i am struggling to figure out how to do it?

    Thanks for your help

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculate Average Weekly Income for Business

    Hi,

    This is achievable using dynamic named ranges and some Excel functions. Could you please upload your workbook (desensitised if necessary please) so that I can take a look at the format of your workbook and assist you in getting your formulas and named ranges setup.

    Thanks

  3. #3
    Registered User
    Join Date
    03-03-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Talking Re: Calculate Average Weekly Income for Business

    Thanks for your help here is the spreadsheet Business Income & Expenses 13 - 14.xlsm.

    The 'income figures' sheet is something i was playing with to try to figure out this problem. the main sheet is the first sheet 'income and expenses'

    I was just thinking if i can use a formula that will tell me the number or weeks or months within the date column on table1 I could just divide this by the total income. It may be a simple way of doing it.

    no idea how to do it for each current week or month


    Thanks again

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculate Average Weekly Income for Business

    You're welcome!

    Given that you are using Tables, you shouldn't need the named ranges after all. Test the formulae on the "ajryan88" tab in the attached workbook, and let me know if they work as you would expect

  5. #5
    Registered User
    Join Date
    03-03-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Talking Re: Calculate Average Weekly Income for Business

    Thanks for your help

    This looks like a way to do it, but I dont think its calculating the figures correctly. Ive inputted some figures to test it and it looks like they are coming up wrong.

    What do you think?

    Business Income & Expenses 13 - 14.xlsm


    Thanks again.

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculate Average Weekly Income for Business

    My apologies

    Average monthly income: corrected - was dividing by one month less than it should have been.
    Average weekly income: corrected - was missing a pair of parenthesis on the numerator.
    Current month income: this calculation has been based on the month of the current day. So it is only summing up 1/3 - 3/3 (= $150). Can you please explain to me how you got $200 and how you would like to calculate this value if I have interpreted this incorrectly.
    Current week income: this is calculated based on the week of the current day (starting on the most recent Monday). I have adjusted this formula to now ensure that the date is no greater than today's date, but the result is $50, not $150. As for the current month income, can you please explain this one to me in a little more detail.

    I can see 4 interpretations of the current month and current week income calculations:
    1. Current income for the current calendar month/week (starting on the 1st (Month)/Monday (week)), based on today's date
    2. Total income for the previous 30 (Month)/7 (Week) days, based on today's date
    3. Current income for the current calendar month/week (starting on the 1st (Month)/Monday (week)), based on the last date in the Date column
    4. Total income for the previous 30 (Month)/7 (Week) days, based on the last date in the Date column


  7. #7
    Registered User
    Join Date
    03-03-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Talking Re: Calculate Average Weekly Income for Business

    No thats fine, you've already done much more than I could have on my own


    Sorry my mistake, I added from 2/3 to 4/4 not 3/3 so monthly income is $150. Now that ive had lunch and came back my brain is working a little better! the way I calculated the weekly income was starting from the Sunday so from the 2/3 to 3/3 which is $100 but it probably doesnt matter much if it starts monday or sunday.

    As for the current month income which method do you suggest? I think option 1 would be the best as it would always be current.

    Thanks alot for doing this, I hope it hasnt taken up much of your time.

  8. #8
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculate Average Weekly Income for Business

    I also would suggest option 1.

    If you would like it to calculate from Sunday instead, just change
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in the Current Week Income formula.

    You're very welcome I wouldn't contribute here if I was concerned about how much of my time it was taking up. It is a pleasure to be able to help and give back to the forum that has helped me get to where I am over the last 2 years.

    If this solves your initial problem, please don't forget to mark this thread as solved, and please click on the * next to my post to say thanks.

    Let me know if there's something else that you need before this thread is solved.

  9. #9
    Registered User
    Join Date
    03-03-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Calculate Average Weekly Income for Business

    Thanks I might leave it for now but ill keep that in mind if i ever want to change it to sunday. sorry to be a pain but i also just remembered. the income/expenses and amounts column contain data relating to both income and expenses so ive had to use SUMIFS i.e. =SUMIFS(Table1[Amount],Table1[Income/Expense/Credit/Debit],"Income",Table1[Type],"Bags") or =SUMIFS(Table1[Amount],Table1[Income/Expense/Credit/Debit],"Expense",Table1[Type],"Stock") in my summary sheet. The way the formulas you've put together are now will it add all expenses too by mistake?

    Thats awsomes ive only been using excel properly for the past 6 months or so for all our bookkeeping but its such an awesome program. The maths it can do is so awesome i reckon soon you'll be able to create your own universe on it :P

  10. #10
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculate Average Weekly Income for Business

    That's right, sorry I didn't realise that.

    Here is the corrected version. Your suggested formulas are very close.

  11. #11
    Registered User
    Join Date
    03-03-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Talking Re: Calculate Average Weekly Income for Business

    No i should have mentioned that earlier

    Ive attached the inputted formulas you gave me to the income and expenses tables in the "summary" sheet, and had a play with some figures. Im not sure if the Average Weekly Profit/Expenses is working correctly, ive changed ((MAX(Table1[Date])-MIN(Table1[Date]))/7),0) to a 3 (through trial and error) and initially i thought this solved the issue but i dont think it has. it seems to be dividing by the wrong number of weeks.

    Im assuming the average weekly income or expense should be roughly 1/4 the average monthly. Let me know what you think


    Thanks
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculate Average Weekly Income for Business

    That 7 is correct, as the numerator is the number of days covered by your Income/Expenses table, so the outcome is the number of weeks. The reason the figure isn't 1/4 of the monthly is because of 2 reasons:
    1. There are 2 months in the sheet (March, April), so the $5000 total income is an average of $2500
    2. There are almost 5 weeks in the sheet, so the $5000 total income works out around $1029 average
    The reason that it looks weird is because we are only partway through the second month, and the monthly calculation is treating that as 2 months rather than 1.1 months for example.

    I hope this makes sense...?

  13. #13
    Registered User
    Join Date
    03-03-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Talking Re: Calculate Average Weekly Income for Business

    I thought that may be the issue but I just wanted to check with you first.

    Thanks alot for that you've helped alot I would never have been able to figure that out on my own

  14. #14
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Calculate Average Weekly Income for Business

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculating weekly income tax on gross income using marginal (bracket) tax rates
    By Puni in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-22-2013, 06:35 PM
  2. [SOLVED] Calculate average business days to complete task
    By jent in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-10-2012, 04:32 PM
  3. $25,000.00 WEEKLY INCOME.MAGAZINE FRANCHISE.WORK AT HOME
    By ISRAEL FAGBEMI in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-17-2006, 02:10 AM
  4. [SOLVED] $25,000.00 WEEKLY INCOME.MAGAZINE FRANCHISE.WORK AT HOME
    By ISRAEL FAGBEMI in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-17-2006, 02:10 AM
  5. Replies: 0
    Last Post: 09-16-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1