Deliverable 2 - Managing Budget Variances
vargasperez
MBV-Scenario1.xlsxBudget
| Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Avg per Month | ||
| Sales Plan (All Midwest Division Locations) | January | February | March | April | May | June | July | August | September | October | November | December | Total Year | |
| Days of Sales (Excluding 2 holidays) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 29 | 30 | 363 | |
| # of Locations in Division | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | |
| Bakery Items - Units per Day per Location | 200 | 200 | 200 | 180 | 180 | 180 | 160 | 160 | 180 | 180 | 200 | 200 | 185 | |
| Bakery Items - Average Sales Price | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | |
| Sandwich Items - Unit per Day per Location | 150 | 150 | 150 | 130 | 130 | 130 | 100 | 100 | 130 | 130 | 180 | 180 | 138 | |
| Sandwich Items - Average Sales Price | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | |
| Beverage Items - Units per Day per Location | 600 | 600 | 500 | 450 | 450 | 450 | 400 | 400 | 500 | 500 | 600 | 600 | 504 | |
| Beverage Items - Average Sales Price | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | |
| Average Units Sold per Customer Visit | 1.90 | 1.90 | 1.70 | 1.52 | 1.52 | 1.52 | 1.32 | 1.32 | 1.62 | 1.62 | 1.96 | 1.96 | 1.66 | |
| Average Sales (Gross Revenue) per Visit | $9.18 | $9.18 | $7.98 | $7.15 | $7.15 | $7.15 | $6.25 | $6.25 | $7.75 | $7.75 | $9.37 | $9.37 | $7.87 | |
| Returns and Allowances - % of Sales | 1% | 1% | 1% | 1% | 1% | 1% | 1% | 1% | 1% | 1% | 1% | 1% | 1% | |
| Average Monthly Visits per Customer | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | |
| Average Monthly Visits per Loyalty Customer | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | |
| # of Customers in Loyalty Program | 2000 | 2000 | 2000 | 2100 | 2100 | 2100 | 2210 | 2210 | 2210 | 2320 | 2320 | 2320 | 2158 | |
| Loyalty Rewards - % of Discount | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | |
| Advertising - Social Media Ads # per Month | 20 | 20 | 20 | 20 | 15 | 15 | 15 | 15 | 20 | 20 | 20 | 20 | 18 | |
| Advertising - Social Media Ads Cost Per Ad | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $2,000.00 | $2,000.00 | $1,166.67 | |
| Advertising - Social Media Ads Volume Incr % | 3% | 3% | 3% | 3% | 3% | 3% | 3% | 3% | 3% | 3% | 3% | 3% | 3% | |
| Advertising - Mailer Ads # per Month | 10 | 10 | 10 | 10 | 5 | 5 | 5 | 5 | 10 | 10 | 10 | 10 | 8 | |
| Advertising - Mailer Ads Cost Per Ad | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $4,000.00 | $4,000.00 | $3,166.67 | |
| Advertising - Mailer Ads Volume Incr % | 6% | 6% | 6% | 6% | 6% | 6% | 6% | 6% | 6% | 6% | 6% | 6% | 6% | |
| Midwest Sales Team Income Statement | ||||||||||||||
| Financial Statements in U.S. Dollars | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | |
| Revenue | January | February | March | April | May | June | July | August | September | October | November | December | Total Year | |
| Gross Sales | 2,844,250 | 2,569,000 | 2,472,250 | 2,143,500 | 2,214,950 | 2,143,500 | 1,937,500 | 1,937,500 | 2,323,500 | 2,400,950 | 2,717,300 | 2,811,000 | 28,515,200 | |
| Less: Returns and Allowances | 28,443 | 25,690 | 24,723 | 21,435 | 22,150 | 21,435 | 19,375 | 19,375 | 23,235 | 24,010 | 27,173 | 28,110 | 285,152 | |
| Less: Customer Loyalty Discounts | 5,505 | 5,505 | 4,785 | 4,501 | 4,501 | 4,501 | 4,144 | 4,144 | 5,135 | 5,391 | 6,522 | 6,522 | 61,155 | |
| Net Sales | 2,810,303 | 2,537,805 | 2,442,743 | 2,117,564 | 2,188,299 | 2,117,564 | 1,913,981 | 1,913,981 | 2,295,130 | 2,371,550 | 2,683,605 | 2,776,368 | 28,168,893 | |
| Expenses | January | February | March | April | May | June | July | August | September | October | November | December | Total Year | |
| Selling Expense - Advertising | 50,000 | 50,000 | 50,000 | 50,000 | 30,000 | 30,000 | 30,000 | 30,000 | 50,000 | 50,000 | 80,000 | 80,000 | 580,000 | |
| Selling Expense - Marketing Firm | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 300,000 | |
| General Expense - Sales Team | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 1,440,000 | |
| Administrative Expense - Sales Team | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 360,000 | |
| Total Expenses | 225,000 | 225,000 | 225,000 | 225,000 | 205,000 | 205,000 | 205,000 | 205,000 | 225,000 | 225,000 | 255,000 | 255,000 | 2,680,000 | |
| Net Income (Loss) - Sales Div Only | 2,585,303 | 2,312,805 | 2,217,743 | 1,892,564 | 1,983,299 | 1,912,564 | 1,708,981 | 1,708,981 | 2,070,130 | 2,146,550 | 2,428,605 | 2,521,368 | 25,488,893 | |
Q1 YTD w Variances
| Actual | Actual | Actual | Avg for Quarter | Variance | Variance | Variance | Positive values are favorable | |||
| Sales Plan (All Midwest Division Locations) | January | February | March | Quarter 1 | January | February | March | Negative values are unfavorable | ||
| Days of Sales (Excluding 2 holidays) | 31 | 28 | 31 | 90 | 0 | 0 | 0 | |||
| # of Locations in Division | 20 | 20 | 20 | 20 | 0 | 0 | 0 | |||
| Bakery Items - Units per Day per Location | 181 | 202 | 190 | 191 | (19) | 2 | (10) | |||
| Bakery Items - Average Sales Price | $2.45 | $2.40 | $2.60 | $2.48 | ($0.05) | ($0.10) | $0.10 | |||
| Sandwich Items - Unit per Day per Location | 172 | 149 | 155 | 159 | 22 | (1) | 5 | |||
| Sandwich Items - Average Sales Price | $3.20 | $3.22 | $3.25 | $3.22 | ($0.05) | ($0.03) | $0.00 | |||
| Beverage Items - Units per Day per Location | 550 | 580 | 495 | 542 | (50) | (20) | (5) | |||
| Beverage Items - Average Sales Price | $5.90 | $5.90 | $5.90 | $5.90 | ($0.10) | ($0.10) | ($0.10) | A decrease in the average sales price across all items in a category can indicate customers are purchasing smaller sizes and/or less expensive menu items | ||
| Average Units Sold per Customer Visit | 1.81 | 1.86 | 1.68 | 1.78 | (0.09) | (0.04) | (0.02) | |||
| Average Sales (Gross Revenue) per Visit | $8.48 | $8.77 | $7.84 | $8.36 | ($0.70) | ($0.40) | ($0.14) | |||
| Returns and Allowances - % of Sales | 1.5% | 1.4% | 1.4% | 1.4% | -0.5% | -0.4% | -0.4% | The sales team receives information on returns and allowances because of the impact on sales revenue | ||
| Average Monthly Visits per Customer | 2.0 | 2.0 | 2.0 | 2.0 | 0.0 | 0.0 | 0.0 | |||
| Average Monthly Visits per Loyalty Customer | 6.2 | 6.3 | 6.3 | 6.3 | 0.2 | 0.3 | 0.3 | Historically, when customers convert to loyalty rewards, they increase their average # of visits in a month | ||
| # of Customers in Loyalty Program | 1990 | 2005 | 2010 | 2002 | (10) | 5 | 10 | Advertising to loyalty customers is done via email with no advertising fee expense -- providing an email is a requirement for loyalty rewards enrollment | ||
| Loyalty Rewards - % of Discount | 5.0% | 5.0% | 5.0% | 5.0% | 0.0% | 0.0% | 0.0% | |||
| Advertising - Social Media Ads # per Month | 17 | 20 | 18 | 18 | (3) | 0 | (2) | Because the company has many competitors for quick breakfast foods, regular advertising is used for brand recognition and to maintain market share | ||
| Advertising - Social Media Ads Cost Per Ad | $1,050.00 | $1,050.00 | $1,050.00 | $1,050.00 | ($50.00) | ($50.00) | ($50.00) | |||
| Advertising - Social Media Ads Volume Incr % | 2.5% | 2.8% | 2.8% | 2.7% | -0.5% | -0.2% | -0.2% | |||
| Advertising - Mailer Ads # per Month | 9 | 9 | 9 | 9 | (1) | (1) | (1) | Mailers directly target customers who live in a mile radius of shop locations which leads to their higher volume increase potential | ||
| Advertising - Mailer Ads Cost Per Ad | $2,950.00 | $2,950.00 | $2,950.00 | $2,950.00 | $50.00 | $50.00 | $50.00 | |||
| Advertising - Mailer Ads Volume Incr % | 5.5% | 5.8% | 6.0% | 5.8% | -0.5% | -0.2% | 0.0% | |||
| Midwest Sales Team Income Statement | ||||||||||
| Financial Statements in U.S. Dollars | Actual | Actual | Actual | Actual | Variance | Variance | Variance | |||
| Revenue | January | February | March | Quarter 1 | January | February | March | |||
| Gross Sales | 2,628,087 | 2,456,485 | 2,429,315 | 7,513,887 | (216,163) | (112,515) | (42,935) | |||
| Less: Returns and Allowances | 39,421 | 34,391 | 34,010 | 107,823 | (10,979) | (8,701) | (9,288) | |||
| Less: Customer Loyalty Discounts | 5,230 | 5,541 | 4,962 | 15,732 | 275 | (36) | (177) | |||
| Net Sales | 2,583,436 | 2,416,553 | 2,390,343 | 7,390,332 | (205,459) | (103,779) | (33,470) | |||
| -5% | ||||||||||
| Expenses | January | February | March | Quarter 1 | January | February | March | |||
| Selling Expense - Advertising | 44,400 | 47,550 | 45,450 | 137,400 | 5,600 | 2,450 | 4,550 | |||
| Selling Expense - Marketing Firm | 30,000 | 20,000 | 25,000 | 75,000 | (5,000) | 5,000 | 0 | |||
| General Expense - Sales Team | 120,000 | 120,000 | 120,000 | 360,000 | 0 | 0 | 0 | |||
| Administrative Expense - Sales Team | 30,000 | 30,000 | 30,000 | 90,000 | 0 | 0 | 0 | |||
| Total Expenses | 224,400 | 217,550 | 220,450 | 662,400 | 600 | 7,450 | 4,550 | |||
| Net Income (Loss) - Sales Div Only | 2,359,036 | 2,199,003 | 2,169,893 | 6,727,932 | (206,059) | (111,229) | (38,020) | |||
Revised Forecast
| Use this template to create a revised forecast, if desired, as supporting detail for your recommendations | Actual | Actual | Actual | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Avg per Month | ||||
| Sales Plan (All Midwest Division Locations) | January | February | March | April | May | June | July | August | September | October | November | December | Total Year | ||||
| Days of Sales (Excluding 2 holidays) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 29 | 30 | 363 | ||||
| # of Locations in Division | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | ||||
| Bakery Items - Units per Day per Location | 181 | 202 | 190 | 180 | 180 | 180 | 160 | 160 | 180 | 180 | 200 | 200 | 183 | ||||
| Bakery Items - Average Sales Price | $2.45 | $2.40 | $2.60 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | $2.50 | ||||
| Sandwich Items - Unit per Day per Location | 172 | 149 | 155 | 130 | 130 | 130 | 100 | 100 | 130 | 130 | 180 | 180 | 141 | ||||
| Sandwich Items - Average Sales Price | $3.20 | $3.22 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.25 | $3.24 | ||||
| Beverage Items - Units per Day per Location | 550 | 580 | 495 | 450 | 450 | 450 | 400 | 400 | 500 | 500 | 600 | 600 | 498 | ||||
| Beverage Items - Average Sales Price | $5.90 | $5.90 | $5.90 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $6.00 | $5.98 | ||||
| Average Units Sold per Customer Visit | 1.81 | 1.86 | 1.68 | 1.52 | 1.52 | 1.52 | 1.32 | 1.32 | 1.62 | 1.62 | 1.96 | 1.96 | 1.64 | ||||
| Average Sales (Gross Revenue) per Visit | $8.48 | $8.77 | $7.84 | $7.15 | $7.15 | $7.15 | $6.25 | $6.25 | $7.75 | $7.75 | $9.37 | $9.37 | $7.77 | ||||
| Returns and Allowances - % of Sales | 1.5% | 1.4% | 1.4% | 1.0% | 1.0% | 1.0% | 1.0% | 1.0% | 1.0% | 1.0% | 1.0% | 1.0% | 1.1% | ||||
| Average Monthly Visits per Customer | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | ||||
| Average Monthly Visits per Loyalty Customer | 6.2 | 6.3 | 6.3 | 6.0 | 6.0 | 6.0 | 6.0 | 6.0 | 6.0 | 6.0 | 6.0 | 6.0 | 6.1 | ||||
| # of Customers in Loyalty Program | 1990 | 2005 | 2010 | 2100 | 2100 | 2100 | 2210 | 2210 | 2210 | 2320 | 2320 | 2320 | 2158 | ||||
| Loyalty Rewards - % of Discount | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | 5% | ||||
| Advertising - Social Media Ads # per Month | 17 | 20 | 18 | 20 | 15 | 15 | 15 | 15 | 20 | 20 | 20 | 20 | 18 | ||||
| Advertising - Social Media Ads Cost Per Ad | $1,050.00 | $1,050.00 | $1,050.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $1,000.00 | $2,000.00 | $2,000.00 | $1,179.17 | ||||
| Advertising - Social Media Ads Volume Incr % | 2.5% | 2.8% | 2.8% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 3.0% | 2.9% | ||||
| Advertising - Mailer Ads # per Month | 9 | 9 | 9 | 10 | 5 | 5 | 5 | 5 | 10 | 10 | 10 | 10 | 8 | ||||
| Advertising - Mailer Ads Cost Per Ad | $2,950.00 | $2,950.00 | $2,950.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $3,000.00 | $4,000.00 | $4,000.00 | $3,154.17 | ||||
| Advertising - Mailer Ads Volume Incr % | 5.5% | 5.8% | 6.0% | 6.0% | 6.0% | 6.0% | 6.0% | 6.0% | 6.0% | 6.0% | 6.0% | 6.0% | 5.9% | ||||
| Midwest Sales Team Income Statement | |||||||||||||||||
| Financial Statements in U.S. Dollars | Actual | Actual | Actual | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Var. to Budget | Var. % | ||
| Revenue | January | February | March | April | May | June | July | August | September | October | November | December | Total Year | Total Year | Total Year | ||
| Gross Sales | 2,628,087 | 2,456,485 | 2,429,315 | 2,143,500 | 2,214,950 | 2,143,500 | 1,937,500 | 1,937,500 | 2,323,500 | 2,400,950 | 2,717,300 | 2,811,000 | 28,143,587 | (371,613) | -1.3% | ||
| Less: Returns and Allowances | 39,421 | 34,391 | 34,010 | 21,435 | 22,150 | 21,435 | 19,375 | 19,375 | 23,235 | 24,010 | 27,173 | 28,110 | 314,120 | (28,968) | -10.2% | ||
| Less: Customer Loyalty Discounts | 5,230 | 5,541 | 4,962 | 4,501 | 4,501 | 4,501 | 4,144 | 4,144 | 5,135 | 5,391 | 6,522 | 6,522 | 61,093 | 63 | 0.1% | ||
| Net Sales | 2,583,436 | 2,416,553 | 2,390,343 | 2,117,564 | 2,188,299 | 2,117,564 | 1,913,981 | 1,913,981 | 2,295,130 | 2,371,550 | 2,683,605 | 2,776,368 | 27,768,375 | (342,708) | -1.2% | ||
| Expenses | January | February | March | April | May | June | July | August | September | October | November | December | Total Year | Total Year | Total Year | ||
| Selling Expense - Advertising | 44,400 | 47,550 | 45,450 | 50,000 | 30,000 | 30,000 | 30,000 | 30,000 | 50,000 | 50,000 | 80,000 | 80,000 | 567,400 | 12,600 | 2.2% | ||
| Selling Expense - Marketing Firm | 30,000 | 20,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 300,000 | 0 | 0.0% | ||
| General Expense - Sales Team | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 | 1,440,000 | 0 | 0.0% | ||
| Administrative Expense - Sales Team | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 30,000 | 360,000 | 0 | 0.0% | ||
| Total Expenses | 224,400 | 217,550 | 220,450 | 225,000 | 205,000 | 205,000 | 205,000 | 205,000 | 225,000 | 225,000 | 255,000 | 255,000 | 2,667,400 | 12,600 | 0.5% | ||
| Net Income (Loss) - Sales Div Only | 2,359,036 | 2,199,003 | 2,169,893 | 1,892,564 | 1,983,299 | 1,912,564 | 1,708,981 | 1,708,981 | 2,070,130 | 2,146,550 | 2,428,605 | 2,521,368 | 25,100,975 | (355,308) | -1.4% | ||
