Case 10.2 Abruzzi Olive Oil Company (LO1) The Abruzzi Olive Oil Company is a small producer of premium olive oil. Cheryl Sounders, the owner of Abruzzi, is currently developing a budget spreadsheet to explore the impact of various sales goals on production. In 2020, the company had monthly sales as follows: Month Sales (gallons) January 9,200 February 9,000 March 9,400 April 8,600 May 8,000 June 8,500 July 8,200 August 7,500 September 8,900 October 9,300 November 9,200 December 9,600At a planning meeting in November 2020, Jay Peters, the marketing manager for Abruzzi, told Cheryl that he expected monthly sales to increase by 5 to 15 percent in the coming year. But in late December 2020, Jay rushed into Cheryl's ofce with some good news. \"Cheryl, I just had a meeting with Consolidated Restaurants, and they're considering an order for 1,250 gallons each month for all of 2021.\" \"Gosh,\" Cheryl replied, \"that's an exciting bit of news, but I'm concerned about whether we have the capacity to accept such a large order. I'll prepare budgets assuming we don't get the Consolidated business but we increase monthly sales by 5, 10, or 15 percent. Then I'll assume the Consolidated order comes through, and on top of that we have monthly sales increases of 5, 10, and 15 percent. This should give us a good idea of whether we'll bump up against capacity.\" Jay thought that this sounded ne, but he wondered whether Cheryl had the time to do this much work. Cheryl indicated that the analysis was relatively easy since she was preparing the budget on a spreadsheet and each analysis would require only a simple change. Required a. Using a spreadsheet, prepare the six monthly budget schedules that Cheryl suggested (i.e., monthly budgets with and without the Consolidated business assuming other sales increases of 5, 10, and 15 percent). As a general rule, Cheryl likes to have ending inventory equal to 12 percent of next month's sales. Assume that the company ended 2020 with an inventory of 1,500 gallons of olive oil. In order to calculate ending inventory at the end of December 2021, assume that sales in January 2022 will be the same as December 2021 sales. b. Suppose that capacity is 12,000 gallons. Is the company likely to encounter a capacity problem? u g. c. Abruzzi sells its oil for $25 per gallon. The variable cost per gallon is $10. What will be the annual impact on prot of obtaining the Consolidated business (assuming that there is no capacity problem)? Case Study 10 - 2 Template 2 Abruzzi Olive Oil Company 3 Sales Budget without consolidation: 5 6 2021 2021 2021 7 2020 5% increase 10% increase 15% increase 8 January 9,200 9 February 10 March 11 April 12 May 13 June 14 July 15 August 16 September 17 October 18 November 19 December 11,040 20 21 22 Sales Budget with consolidation:22 Sales Budget with consolidation: 23 24 2021 2021 2021 25 5% increase 10% increase 15% increase 26 January 10,910 27 February 28 March 29 April 30 May 31 June 32 July 33 August 34 September 35 October 36 November 37 December 12,290 38 39 40 Production Budget:40 Production Budget: 41 42 5% Sales EI BI Production 43 January 9,660 44 February 45 March 46 April 47 May 48 June 49 July 50 August 51 September 52 October 53 November 54 December 10,080 55 56 10% Sales EI BI Production84 Budget - Extra Sales to Consolidated: 85 86 5% Sales EI BI Production 87 January 10,910 88 February 89 March 90 April 91 May 92 June 93 July 94 August 95 September 96 October 97 November 98 December 11,330 99 100 10% Sales El BI Production100 10% Sales EI BI Production 101 January 11,370 102 February 103 March 104 April 105 May 106 June 107 July 108 August 109 September 110 October 111 November 112 December 11,810 113 114 15% Sales EI BI Production