Please help me to solve this problem:) thanks in advance Abruzzi Olive Oil Compa
ID: 2488121 • Letter: P
Question
Please help me to solve this problem:)
thanks in advance
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 2014, the company had monthly sales as follows: At a planning meeting in November 2014, 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 2014, 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 2015." "Cosh," 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 A 5 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 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. Using a spreadsheet prepare the six monthly budget chedules that Cheryl suggested (i.e., monthly budgets with md without the Consolidated business assuming other sales ncreases of 5,10, and 15 percent). As a general rule, Cheryl likes o have ending inventory equal to 12 percent of next month's,ales. Assume that the company ended 2014 with an inven-ory of 1,500 gallons of olive oil. In order to calculate ending inventory at the end of December 2015, assume that sales in January 2016 will be the same as December 2015 sales. b. Suppose that capacity is 12,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)?Explanation / Answer
12290
Seperate colomns have been made increasing sales by 5%, 10% and 15% - with and without consolidated business.
Opening stocks will be 1500 as already mentioned and when we add sales for one month plus opening stock and subtract the closing stock we will get Monthly budget as shown in above tables
B. Without consolidated business, there will be no capacity problem even if sale increased by maximum 15%
but if consolidated business is considered then at 5% and 10% increase there will be no capacity problem but at 15% capacity problem comes.
C. If there is no capacity problem all 1250 gallons per month will be sold from consolidated business
Total gallons sold in a year is 1250*12 = 15000
Net Contribution per gallon is 25-10 = 15
Total net profit earned from consolidated business = 15000 * 15 = $ 225,000
1500 Budget without Consolidated business 2015 Sales increase 5% Monthly Budget 5% Closing Inventory 5% Sales increase 10% Monthly Budget 10% Closing Inventory 10% Sales increase 15% Monthly Budget 15% Closing Inventory 15% 9660 9,294 1,134 10120 10,432 1,188 10580 10,810 1,270 9450 9,400 1,184 9900 9,847 1,241 10350 10,378 1,242 9870 9,971 1,084 10340 10,446 1,135 10810 10,755 1,297 9030 9,106 1,008 9460 9,539 1,056 9890 10,000 1,187 8400 8,337 1,071 8800 8,734 1,122 9200 9,283 1,104 8925 8,963 1,033 9350 9,390 1,082 9775 9,706 1,173 8610 8,698 945 9020 9,112 990 9430 9,471 1,132 7875 7,699 1,121 8250 8,065 1,175 8625 8,722 1,035 9345 9,295 1,172 9790 9,737 1,228 10235 10,042 1,228 9765 9,778 1,159 10230 10,243 1,214 10695 10,640 1,283 9660 9,610 1,210 10120 10,067 1,267 10580 10,594 1,270 10080 10,080 1,210 10560 10,560 1,267 11040 10,985 1,325 Jan'16 10080 10560 11040