Abruzzi 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. Month January February March April May June July Sales(Gallons) 9400 9200 9000 8800 8100 8500 8800 August September October November December 8500 9000 9300 9200 9600 At a planning meeting in November 2011, jay Peters, the marketing manager for Abruzzi, told Cheryl that he expected monthly sales to increase by 5 % to 15% in the coming years. But in late December 2011, Jay rushed into Cheryl's office with some good news." Cheryl, I just had a meeting with consolidated restaurants, and they are considering an order for 1500 gallons each month for all of 2012". "Gosh", Cherylreplied," that's and exiting bit news, but I am concerned about whether we have the capacity to accept such a large order. I will prepare budgets assuming we don't get the consolidated business, but we increase monthly sales by 5,10, or 15 percent. This should give us a good idea of whether we will bump up against capacity." Jay thought that this sounded fine, but he wondered whether Cherylhad the time to do this much work. Cheryl indicated that the analysis was relatively easy since she was preparing the budget on spreadsheet and each analysis world require only a simple change. Required: a. Prepare a report which explain different types of budgets Chreyl can prepare based on the above information. Jay indicated that it is easy to use spread sheet for budgets analysis; explain what tools and techniques can be used in budget planning and control and what are their advantages and disadvantages. (P4,P5) b. Using a spreadsheet, prepare the six-monthly budget schedules that Cherylsuggested (i.e. monthly budgets with and without the consolidated business assuming other sales increases of 5,10 and 15 %). As a general rule, Cheryl likes to have ended inventory equal to 15% of next month's sales. Assume that the company ended 2011 with an inventory of 1400 gallons of olive oil. To calculate ending inventory at the end of December 2012, assume that sales in January 2013 will be the same as December 2012 sales. (M3) C. Suppose that capacity is 120000 gallons. Is the company likely to encounter a capacity problem? (D3) d. Abruzzi sells its oil for $ 25 per gallon. The variable cost per gallon is $10. What will be the annual impact on profits of obtaining the consolidated business (assuming there is no capacity problem)? (M4) Abruzzi 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. Month January February March April May June July Sales(Gallons) 9400 9200 9000 8800 8100 8500 8800 August September October November December 8500 9000 9300 9200 9600 At a planning meeting in November 2011, jay Peters, the marketing manager for Abruzzi, told Cheryl that he expected monthly sales to increase by 5 % to 15% in the coming years. But in late December 2011, Jay rushed into Cheryl's office with some good news." Cheryl, I just had a meeting with consolidated restaurants, and they are considering an order for 1500 gallons each month for all of 2012". "Gosh", Cherylreplied," that's and exiting bit news, but I am concerned about whether we have the capacity to accept such a large order. I will prepare budgets assuming we don't get the consolidated business, but we increase monthly sales by 5,10, or 15 percent. This should give us a good idea of whether we will bump up against capacity." Jay thought that this sounded fine, but he wondered whether Cherylhad the time to do this much work. Cheryl indicated that the analysis was relatively easy since she was preparing the budget on spreadsheet and each analysis world require only a simple change. Required: a. Prepare a report which explain different types of budgets Chreyl can prepare based on the above information. Jay indicated that it is easy to use spread sheet for budgets analysis; explain what tools and techniques can be used in budget planning and control and what are their advantages and disadvantages. (P4,P5) b. Using a spreadsheet, prepare the six-monthly budget schedules that Cherylsuggested (i.e. monthly budgets with and without the consolidated business assuming other sales increases of 5,10 and 15 %). As a general rule, Cheryl likes to have ended inventory equal to 15% of next month's sales. Assume that the company ended 2011 with an inventory of 1400 gallons of olive oil. To calculate ending inventory at the end of December 2012, assume that sales in January 2013 will be the same as December 2012 sales. (M3) C. Suppose that capacity is 120000 gallons. Is the company likely to encounter a capacity problem? (D3) d. Abruzzi sells its oil for $ 25 per gallon. The variable cost per gallon is $10. What will be the annual impact on profits of obtaining the consolidated business (assuming there is no capacity problem)? (M4)