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

1. ## 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. ## 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.

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.

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

Example is attached. See F5:F34 for formula.

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

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

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

That's perfect, thank you!

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

You are welcome

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

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