On the third day in your role as accountant the general manager has asked you to review the results of 2012 and 2013 to try and determine if there is a relationship between the number of meals prepared per month and the administration costs which she believes to be too high. Data regarding the number of conferences per month, number of meals prepared for that month and the administration costs for the months of 2012 and 2013 are as follows:
Year Month Number of conferences Total number of meals prepared Administrative cost
2012 Jan 20 700 27800
2012 Feb 22 250 14000
2012 Mar 10 200 12000
2012 Apr 20 500 20000
2012 May 24 650 23800
2012 Jun 28 450 18400
2012 Jul 16 650 20400
2012 Aug 14 150 8200
2012 Sep 24 350 18800
2012 Oct 24 600 22200
2012 Nov 16 300 16600
2012 Dec 32 750 32200
2013 Jan 18 850 41500
2013 Feb 19 175 19000
2013 Mar 12 100 17800
2013 Apr 18 775 35500
2013 Jun 24 475 27400
2013 Jul 16 775 30400
2013 Aug 14 25 12100
2013 Sep 23 325 28000
2013 Oct 21 700 33100
2013 Nov 16 250 24700
2013 Dec 26 925 48100
Required:
a) Draw a scatter diagram of Boutique Catering's Number of meals prepared and administrative costs for the year.
b) The relevant range has been identified as between 200 and 800 meals prepared. Mark the range on the scatter graph. Do you think this is an appropriate range? Why?
c) The high-low method in another way of estimating the cost behavior of Boutique Catering. Using administration costs and number of meals prepared within the relevant range create an equation to express the results.
d) A third method that can help understand the cost behavior relationship is by using regression analysis. Construct an excel spreadsheet and use regression analysis to estimate the;
a. Number of meals and administration cost. What is the administration costs' equation when 600 meals are prepared?
b. Number of meals, administration costs and number of conferences. What is the administration cost when 600 meals are prepared?
e) From the methods used which method would you feel most confident to report the findings to the General Manager? In your answer make particular reference to the advantages and disadvantages of each method and in addition discuss the cost-benefit of each method.