+ Reply to Thread
Results 1 to 6 of 6

Help! Need variable daily sales targets to total monthly sales goal

  1. #1
    Registered User
    Join Date
    07-31-2018
    Location
    Georgetown, KY
    MS-Off Ver
    2013
    Posts
    3

    Question Help! Need variable daily sales targets to total monthly sales goal

    Hello!

    I've been across most of the internet to find an answer to my quandary. I'm hoping you fine folks can help me out!

    I've created a retail sales goal tracker that sets daily sales targets based on historical sales percentages. My formula in the 'daily target' column takes the monthly $ target, divides that number by the number of sales days and then determines each sales day's target $ depending on what day of the week it is and what that weekday's historical average percent of weekly sales is (i.e. 15.04% on Mondays, 14.63% Tuesday, etc.) My problem is that the daily sales target $ don't add up to the full monthly target because of the weekday composition within a month. So M-Sa percentages total 100%, but a month that ends on a Tuesday is coming up short by 3 days. How can I alter my formula so that it accounts for the uneven weekday composition within a month and the sum of the individual sales targets equals the monthly sales target?

    For reference, here's the formula as it is now: =SUM((($C$4/25)*6)*(IF(WEEKDAY(E6)=2,$C$19,IF(WEEKDAY(E6)=3,$C$20,IF(WEEKDAY(E6)=4,$C$21,IF(WEEKDAY(E6)=5,$C$22,IF(WEEKDAY(E6)=6,$C$23,IF(WEEKDAY(E6)=7, $C$24,0))))))))

    $C$4 is the monthly sales target and $C$19:$C$24 are each day's percentages to the weekly goal.

    Many thanks!

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Help! Need variable daily sales targets to total monthly sales goal

    I'd recommend uploading sample workbook with desensitized data. Clearly indicating final result you wish to obtain.

    To upload workbook, use "Go Advanced" button found at bottom right of Quick Reply/Edit menu. Find "Manage Attachments" hyperlink and click on it.
    This will launch new window/tab to manage uploads.

    What I normally do is first calculate avg actual by day for previous month (excluding exception such as Sales campaign).
    Then calculate target for each day.

    Something like attached.
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    07-31-2018
    Location
    Georgetown, KY
    MS-Off Ver
    2013
    Posts
    3

    Re: Help! Need variable daily sales targets to total monthly sales goal

    Example is attached. See F5:F34 for formula.
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Help! Need variable daily sales targets to total monthly sales goal

    Use below formula then.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-31-2018
    Location
    Georgetown, KY
    MS-Off Ver
    2013
    Posts
    3

    Re: Help! Need variable daily sales targets to total monthly sales goal

    That's perfect, thank you!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Help! Need variable daily sales targets to total monthly sales goal

    You are 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. Replies: 6
    Last Post: 10-19-2013, 04:53 PM
  2. Replies: 2
    Last Post: 10-17-2013, 11:52 AM
  3. Replies: 6
    Last Post: 05-05-2013, 07:17 PM
  4. Replies: 4
    Last Post: 03-27-2013, 05:56 PM
  5. summarize monthly sales from daily sales
    By top1 in forum Excel General
    Replies: 4
    Last Post: 01-05-2010, 11:59 PM
  6. copy week total in weekly sales worksheet to appropriate week in monthly sales
    By Sandy2976 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-15-2009, 01:04 PM
  7. Excel pie chart - sales goal and current sales
    By Excelstar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2008, 11:31 AM

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