When preparing its budget for 2019, the company assumed that there would be a global market for 22,000,000 boxes of sports drinks and that Mountain Springs product would have a 11.409% share of this market.

However, a recent debate on sports drinks argue that while traditional sports drinks are designed to provide athletes with the right balance of carbohydrates, fluids and electrolytes during exercise, they do more harm than good, if youre just quenching a thirst, because sports drinks contain high level of sugar. As a result, during 2019 both Mountain Spring and its competitors were adversely affected by diminishing consumer in sport drink products. The actual total market size was only 20,000,000 boxes.

The Sales director of Mountain Spring recently explained how they attempted to respond to the difficulties which it faced in 2019: First, we reduced our selling price for all products; this was a modest price reduction in comparison with those of our smaller competitors. Second, as a pilot project, we introduce natural ingredients such as natural sweetener to add value to our lemon sports drink. In doing so, we try to increase consumer confidence in the health of our product. Finally, to promote our new design lemon sports drink, we run several marketing campaigns as demonstrating the benefits of sports drinks, in particular, the health of our lemon sports drink (with all-natural ingredients) to the sport players.

Required:

1. Complete the flexible budget (fill the green area on excel template) and associated variances using the template provided.

2. Complete reconciliation of variances using the template provided (Calculate the profit variances and reconcile the actual operating profit to the planned operating profit using the template provided.

If possible, explain how you got the numbers

Mountain Spring manufactures and sells varieties of sports drink (berry, lemon and orange) in bulk to upmarket. Budgeted and actual results for 2019 are as follows: Mountain Spring Income Statement Year Ended 30 June 2019 budget actual sales revenue $ 18,680,000 19,079,500 (11,400,000 variable costs $ (11,794,000) contribution margin $ 7,280,000 7,285,500 non-variable costs $ (5,580,000) (5,680,000) net operating profit $ 1,700,000 1,605,50 Boxes $ per box $ per box $ per box 500,000 7.00 (4.50) 2.50 400,000 6.90 (4.40) 2.50 Boxes $ per box $ per box $ per box 900,000 7.00 (4.00) 3.00 1,100,000 6.90 (4.30) 2.60 Product Breakdown Berry sales volume selling price variable costs contribution margin Lemon sales volume selling price variable costs contribution margin Orange sales volume selling price variable costs contribution margin All Products Combined sales volume market share selling price variable costs contribution margin per box Boxes $ $ per box $ per box 1,110,000 8.00 (5.00) 3.00 1,105,000 7.90 (4.80) 3.10 Boxes $ per box $ per box $ per box 2,510,000 11.409% 7.44 (4.54) 2.90 2,605,000 13.025% 7.32 (4.53) 2.80 Mountain Spring manufactures Flexible Budget Year Ended 30 June 2019 variance flexible budget variance $ $ (325,000) (11,725,000) (69,000) sales revenue variable costs contribution margin non-variable costs net operating profit budget 18,680,000 (11,400,000) 7,280,000 (5,580,000) 1,700,000 actual total variance 19,079,500 399,500 (11,794,000) (394,000) 7,285,500 5,500 (5,680,000) (100,000) 1,605,500 (94,500) 335,000 2,035,000 (429,500) Boxes (100,000) $ per box $ per box $ per box 500,000 7.000 (4.500) 2.500 400,000 7.000 (4.500) 2.500 (0.100) 0.100 0.000 400,000 6.900 (4.400) 2.500 (0.100) 0.100 0.000 Boxes $ per box $ per box $ per box 900,000 7.000 (4.000) 3.000 1,100,000 6.900 (4.300) 2.600 (4.000) 3.000 (0.300) (0.400) (0.100) (0.300) (0.400) Product Breakdown Berry sales volume selling price variable costs contribution margin Lemon sales volume selling price variable costs contribution margin Orange sales volume selling price variable costs contribution margin All Products Combined sales volume market share selling price variable costs contribution margin (5,000) Boxes $ per box $ per box $ per box 1,110,000 8.000 (5.000) 3.000 1,105,000 8.000 (5.000) 3.000 (0.100) 0.200 0.100 1,105,000 7.900 (4.800) 3.100 (0.100) 0.200 0.100 Boxes 1.616% 13.025% $ per box $ per box $ per box 2,510,000 11.409% 7.442 (4.542) 2.900 2,605,000 13.025% 7.324 (4.527) 2.797 (0.118) 0.014 (0.104) 0.023 2.923 (0.126) Mountain Spring manufactures Profit Variance Analysis Year Ended 30 June 2019 $ $ $ 1,700,000 Planned net operating profit market size variance market share variance product mix variance volume variance price variance variable cost variance non-variable cost variance price/cost variance Total variance rounding error Actual net operating profit (94,500) 1,605,500