Hello, can you please help me with this? I'm confused about what formulas to put into Excel to solve this.

Questions

1. Develop a forecasting method for Bardstown and forecast the total demand for 20x6.

2. Should Bon's experience with the market be factored into the forecast? if so, how?

BARDSTOWN BOX COMPANY Bardstown Box Company is a small, closely held corpo- Bob attributes his success to the fact that he can serve ration in Bardstown, Kentucky. Its stock is divided the five-state area he considers his territory better than among three brothers, with the principal shareholder being the founding brother, Bob Wilson. Bob formed the rugated cartons to many regional distilleries and to several any of his large competitors. Bardstown Box supplies cof company 20 years ago when he resigned as a salesman breweries. Also, it prints standard-size boxes for mange for a large manufacturer of corrugated boxes, small manufacturing firms in the region. Bob feels that the Exercises boxes to the leased space. There has always been an increase in demand for stocking up. This seasonality in demand has always substan- Bob feels that it is now important to develop an and warehousing volume. He has compiled the following shipping delays, paper Sales (in number of boxes) Month January February March April May large box manufacturers cannot economically provide warehouse. Plans are to shift some of the slower-moving this personal service to his accounts. 155 Bob recognizes the danger of becoming too dependent upon any one client and has enforced the pol- boxes just before the holiday season, when customers begin icy that no single customer can account for over 20 per- percent of sales each, and hence are limited in their pur- cent of sales. Two of the distilleries account for 20 tially increased the difficulty of making a reliable forecast. chases. Bob has persuaded the purchasing agents of improved forecasting method to help smooth production these two companies to add other suppliers, since this alternative supply protects them against problems demand data. Bardstown might have such as shortages, or labor problems. Bardstown currently has over 600 customers with 20X1 20X2 orders ranging from a low of 100 boxes to blanket orders 20X3 20X4 20X5 12,000 for 50,000 boxes per year. Boxes are produced in 8,000 12,000 15,000 15,000 16 standard sizes with special printing to customers' 8,000 14,000 12,000 22,000 specifications. Bardstown's printing equipment limits 10,000 18,000 18,000 14,000 18,000 the print to two colors. The standardization and limited 18,000 13,000 18,000 18,000 printing allow Bardstown to be price-competitive with 14,000 16,000 14,000 15,000 16,000 the big producers while also providing service for small June 10,000 18,000 18,000 18,000 20,000 orders and emergency orders that large box manufac- July 16,000 14,000 17,000 20,000 28,000 turers cannot provide. August 18,000 28,000 20,000 22,000 28,000 Such personal service, however, requires tight September 20,000 22,000 25,000 26,000 20,000 inventory control and close production scheduling. So October 27,000 27,000 28,000 28,000 30,000 far, Bob has always forecast demand and prepared pro- November 24,000 26,000 18,000 20,000 22,000 duction schedules on the basis of experience, but because December 18,000 10,000 18,000 22,000 28,000 of the ever-growing number of accounts and changes in personnel in customer purchasing departments, the accu- Questions racy of his forecasts has been rapidly declining. The 1. Develop a forecasting method for Bardstown and fore- number of backorders is on the increase, late orders are cast the total demand for 20X6. more common, and inventory levels of finished boxes are also on the increase. A second warehouse has recently 2. Should Bob's experience with the market be factored into the forecast? If so, how? been leased because of the overcrowding in the main 8,000 15,000 A B C D E F G . 1 J K L M 20x2 35000 1 Month(Jan-Dec) 20x1 2 Jan 3 Feb 20x3 8000 20x4 12000 20x5 15000 20x6 (y=mx+b) 15000 12000 Sales 30000 8000 14000 8000 12000 22000 25000 4 Mar 10000 18000 15000 16000 18000 14000 18000 13000 14000 14000 18000 15000 18000 18000 16000 20000 15000 5 Apr 5 May 7 Jun 3 Jul 3 Aug 10000 18000 18000 20000 18000 17000 10000 16000 14000 20000 28000 5000 18000 20000 28000 22000 27000 20000 25000 28000 22000 26000 LO Sep 28000 20000 0 1 Oct 27000 28000 30000 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec L2 Nov 24000 26000 20000 22000 18000 10000 18000 18000 209000 22000 28000 20x1 20x2 20x3 20x4 3 Dec -4 Total 5 20x5 195000 216000 230000 No 265000 a=demand 20x1 b=yearly demand X=