A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

calculating % of a monthly budget on a daily basis



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2010, 05:29 PM posted to microsoft.public.excel.worksheet.functions
dz
external usenet poster
 
Posts: 69
Default calculating % of a monthly budget on a daily basis

I have a monthly budget figure, and I'm trying to use a formula to show what
% of budget I'm at on a daily basis. For instance, my budget for the month is
that I'll make 800 items, and halfway through the month if my actuals show
I've made 400 items, I want the % of budget column to show 100%, not 50%.
How do I do this? Thanks in advance for your help.
  #2  
Old March 16th, 2010, 06:16 PM posted to microsoft.public.excel.worksheet.functions
Russell Dawson[_2_]
external usenet poster
 
Posts: 173
Default calculating % of a monthly budget on a daily basis

This may give you what you need. 8 columns A - H headings as follows

month today days in month month target daily
target
Month start date =TODAY() =B2-A2 800 =D2/30


target to date current workstate Progress percent
=E2*C2 400 =G2/F2%

It's not very sophisticated but will do the job. I'm assuming a 30 day
month but you can change that in the daily target calc. Post back if it
doesn't make sense.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"DZ" wrote:

I have a monthly budget figure, and I'm trying to use a formula to show what
% of budget I'm at on a daily basis. For instance, my budget for the month is
that I'll make 800 items, and halfway through the month if my actuals show
I've made 400 items, I want the % of budget column to show 100%, not 50%.
How do I do this? Thanks in advance for your help.

  #3  
Old March 16th, 2010, 06:26 PM posted to microsoft.public.excel.worksheet.functions
Russell Dawson[_2_]
external usenet poster
 
Posts: 173
Default calculating % of a monthly budget on a daily basis

http://wikisend.com/download/502524/DZ.xlsx

Try this

Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Russell Dawson" wrote:

This may give you what you need. 8 columns A - H headings as follows

month today days in month month target daily
target
Month start date =TODAY() =B2-A2 800 =D2/30


target to date current workstate Progress percent
=E2*C2 400 =G2/F2%

It's not very sophisticated but will do the job. I'm assuming a 30 day
month but you can change that in the daily target calc. Post back if it
doesn't make sense.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"DZ" wrote:

I have a monthly budget figure, and I'm trying to use a formula to show what
% of budget I'm at on a daily basis. For instance, my budget for the month is
that I'll make 800 items, and halfway through the month if my actuals show
I've made 400 items, I want the % of budget column to show 100%, not 50%.
How do I do this? Thanks in advance for your help.

  #4  
Old March 16th, 2010, 06:26 PM posted to microsoft.public.excel.worksheet.functions
rzink
external usenet poster
 
Posts: 23
Default calculating % of a monthly budget on a daily basis

DZ,

The answer depends on how you count the number of days in a month. Is this
based on actual days of the month or only workdays? Do you want to count
days based on a 365 day calendar or a 360 day year (30 days in each month)?

To get a simple estimate, you could multiply the current day of the month by
1/30 and mutiply that by your monthly goal. This gives you your Month to
Date (MTD) goal. Then Divide your current status by the MTD Goal.

If Today = 15 (ie. 3/15/10)
Goal = 800
Then =(15*(1/30)*800) = 400 (MTD Goal)
If current status = 500, then you are at 125% of goal MTD

Hope this helps.
rzink

"DZ" wrote:

I have a monthly budget figure, and I'm trying to use a formula to show what
% of budget I'm at on a daily basis. For instance, my budget for the month is
that I'll make 800 items, and halfway through the month if my actuals show
I've made 400 items, I want the % of budget column to show 100%, not 50%.
How do I do this? Thanks in advance for your help.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.