Prev: Looking for a Function? similar to LEFT
Next: auto populate unused rows from workbook to workbook
From: DZ on 16 Mar 2010 12:29
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.
From: Russell Dawson on 16 Mar 2010 13:16
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.
From: Russell Dawson on 16 Mar 2010 13:26
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.
From: rzink on 16 Mar 2010 13:26
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.
 | 
Pages: 1
Prev: Looking for a Function? similar to LEFT
Next: auto populate unused rows from workbook to workbook