10 Jul Managing Budget Variances Assignment Content Competency Analyze operational budget drivers and variances. Student Success Criteria View the grading rubric for this
Deliverable 2 – Managing Budget Variances
Assignment Content
- Competency
Analyze operational budget drivers and variances.Student Success Criteria
View the grading rubric for this deliverable by selecting the “This item is graded with a rubric” link, which is located in the Details & Information pane.Scenario
You are a sales manager responsible for the midwestern U.S. division of a grab and go style, limited-item restaurant chain that specializes in breakfast items, such as donuts and other bakery items, egg-based sandwiches, coffee, etc. Through the first three months of the fiscal year, the net sales revenue for the midwestern U.S. division has been 5% below budget combined across all items. Your supervisor, the head of sales for the entire U.S., has requested that you investigate the situation and make recommendations to prevent further revenue declines and increase sales revenue enough in the upcoming months to get back to the budgeted amount for the total year.The business analyst supporting your sales team has prepared a set of budgets for review. The data includes the original master budget for the year and the sales figures for the completed quarter with variance amounts. The analyst has also provided the sales plan with mix information (i.e. price and quantity sales budgets along with variances for the quarter). The file also includes promotional metrics showing historical sales volume increases resulting from advertising and loyalty rewards.
Instructions
Write a memo summarizing your analysis of the variances to budget.- Describe at least two issues identified in your analysis;
- Discuss at least two recommendations to prevent further declines in sales revenue and recover the unfavorable revenue variance from the first quarter;
- Include a justification for your recommendations versus other possible options and describe the potential risks; and
- Include supporting detail such as tables, calculations, or a revised forecast copied from Excel as appendices with the memo.
Budget
| 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% | ||
,
Criterion 1
A – 4 – Mastery
Fully described more than two issues identified in the analysis and, thoroughly outlines a recommended course of action to increase sales revenue back to budgeted levels.
Criterion 2
A – 4 – Mastery
Accurately predicted the expected financial impacts to revenue and/or expense from the recommended course of action with supporting detail.
Criterion 3
A – 4 – Mastery
Provided a comprehensive justification for the solution versus other possible options.
Criterion 4
A – 4 – Mastery
Included an extensive properly formatted memo with supporting detail such as tables, calculations, or sample revised budgets copied from Excel as appendices.
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteEdu. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
Do you need help with this question?
Get assignment help from WriteEdu.com Paper Writing Website and forget about your problems.
WriteEdu provides custom & cheap essay writing 100% original, plagiarism free essays, assignments & dissertations.
With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Chat with us today! We are always waiting to answer all your questions.