Question

ABRUZZI OLIVE OIL COMPANY

Abruzzi Olive Oil Company is a small producer of premium olive oil. Cheryl Sounders, the owner of Abruzzi, is currently developing a budgets spreadsheet to explore the impact of various sales goals on production. In 2017, the company had monthly sales as follows:

January	9200
February	9000
March	9400
April	8600
May	8000
June	8500
July	8200
August	7500
September	8900
October	9300
November	9200
December	9600

At a planning meeting in November 2017, 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 2017, Jay rushed into Cheryl's office 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 2018." "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 increase 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 fine, but he wondered whether Cheryl had the time to 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 twelve 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 2017 with an inventory of 1,500 gallons of olive oil. In order to calculate ending inventory at the end of December 2017, assume that sales in January 2019 will be the same as December 2018 sales.

b. Suppose that capacity is 11,000 gallons. Is the company likely to encounter a capacity problem?

c. Abruzzi sells its oil for $25 per gallon. The variable cost per gallon is $10. What will be the annual impact on profit of obtaining the consolidated business (assuming that there is no capacity problem)?

Answer 1

(a)

(b) Assuming the capacity is 11,000 gallons, the company will face capacity problems if it is able to get consolidated business irrespective of the percentage increase in sales (let it be 5%, 10% or 15%). The company will also face capacity problem in the month of December if the sales of the company increase by 15% even without consolidation business.

(c) Contribution margin per gallon = $25- $10 i.e. $15

Sales from consolidated business = 1250 * 12 i.e. 15,000 gallons

Increase in profit = 15,000 gallons @ $15 i.e. $225,000