CASE PROBLEM: FO RECASTIN G FOO D A N D B E V ERAGE S A L E S

Case Problem: Forecasting Food and Beverage Sales

Vintage Restaurant

Student’s Name

Institution

Date

Introduction

Vintage Restaurant is a luxury restaurant located near Fort Myer, Florida. It is owned and operated by Karen Payne. It has been in operation for the last three years and therefore, it is one of the new luxuries hotels in Florida. However, over the last three years, Vintage has established itself as the reputable restaurant as a high quality dining special in the provision of fresh seafood. Through efficient management, Vintage has become one of the best and the fastest growing restaurants located on the island. It is also expected to improve its market share and profitability in the next five years and therefore, it is important for the management to forecast its growth based on its performance. Therefore, the system is needed, which can provide a clear sales forecast of food and beverage per month. This managerial report therefore, provides detailed Vintage Restaurant’s performance for the last financial years. It also presents the analysis Vintage’s sales, including sales forecasts and recommendation to Karen the manager and owner of the restaurant.

Task 1: Analysis of the sales

In the last three years, the total sales for both food and beverages are $6,755, 000. The first year, Vintage realizes the total sales of $2, 106, 000 second year $2250, 000.00 and the third year $2,399,000. It is also noted that the lowest sales for the last three years is $110,000 and the highest sales is $ 282,000. Based on sales data of the total sales registered for the previous thirty six (36) months, the Vintage Restaurant’s total sales increased by almost 5% in every year. The average sales, which Vintage can register in a month is $187,640. It is therefore, important to point that Vintage Restaurant’s for a month could be 187,640 monthly and this is based on several market factors. The analysis of the sales also established that in the last thirty three months the monthly sales of several months is $ 235,000. However, the highest sales are registered within the first three months of the year. The data indicates that Vintage Restaurant has total sales of 235 above between January and April. This means that the first four months of the year are pick months, which the restaurant receive many customers compared to the months.

Table 1: Vintage’s total sales for the last 36 months

Months

First Year

Second Year

Third Year

January

242

263

282

February

235

238

255

March

232

247

265

April

178

193

205

May

184

193

210

June

140

149

160

July

145

157

166

August

152

161

174

September

110

122

126

October

130

148

November

152

167

173

December

206

230

235

Total

2106

2250

2399

Graph: Linear Regression for the total Sales for 36 months

Column1

Mean

187.6388889

Standard Error

7.860404366

Median

176

Mode

235

Standard Deviation

47.16242619

Sample Variance

2224.294444

Kurtosis

-1.058360471

Skewness

0.309909136

Range

172

Minimum

110

Maximum

282

Sum

6755

Count

Task 2: Plot Time

Descriptive Statistic Analysis of Vintage Restaurant’s Sales for the previous 36 months

Graph 1: Time Series Plot

Examining graph 1, it is indicates some seasonal pattern along the linear trend. The figures indicate that lowest reading occurs during the mid year and that is between May and August. It means that Vintage Restaurant lowest sales of beverage and food are registered during the month of May, June, July and August. The highest reading is therefore, occurs at the beginning and the end of the year. These are the months, which Vintage registers the highest sales of the year. It is also important to point those Vintage Restaurant monthly sales of beverages and food fluctuates. In this case, it means that there are high season and low season during the year and therefore, it is important to factor this fluctuation when designing a system for the company, which can be used for several years.

Task 3: Sales Forecast for the fourth year

Dummy variables

Month 1= {1if January or 0 otherwise}, Month 2 = {1 if February or 0 otherwise}, Month 3 = {1 if March or 0 otherwise}, Month 4 = {1 if April or 0 otherwise}, Month 5, {1 if May or 0 otherwise}, Month 6 = {1 if June or 0 otherwise}, Month 7 = { 1 if July or 0 otherwise}, Month 8 = {1 if August or 0 otherwise}, Month 9 = (1 if September or 0 otherwise} Month 10 = {1 if October or 0 otherwise}, Month 11 = {1 if November or 0 otherwise}, Month 12 = 1 if December or 0 otherwise}.

The forecast value is equivalent to

F = b0+ (b1month) + (b2month 2) + (b3 month23) + (b4month 4) + (b5 month5) + (b6 month6) + (b7 month7) + (b8month8) + (b9month9) + (b 10month 10) + (b11month 11) + b12t

Note: F is the value of the forecast for the time period of t, b1, b2, b3, b4, b5, b6, b7, b8, b9, b10, and b11.

Note: The b12 is the decision variable of the value of sales, t is regarded as the period time while month1, moth2, month3, month4, month5, month6, mpnth7, month8, month9, month10, month11 are regarded as the dummy variable being used for projection.

However, the value of b0, b1, b2, b3, b4, b5, b6, b7, b8, b9, b10, and b11 is likely to be 199.25, 49.86, 29.17, 33.49, -23.53, -20.88, -67.89, --62.58, -57.63, -101.28, -85.63, -58.65 and 1.02 respectively.

Note: 199.25 for b0, 49.86 for b1, 29.17 for b2, 33.49 for b3, -23.88 for b4, -67.89 for b5, -62.58 for b6, -57.63 for b7, -101.28 for b8, -85.63 for b9, -58.65 for b10, 1.02 for b11

F= {199.25 +(49.86 month1) + (29.17month2) + (33.49month3) +( -23.53month 4)+ ( -20.88month5) +( -67.89 month 6) + (-62.58month 7)+ ( -57.63month8) + ( -101.28 month 9) + ( -85.63 month 10) + (-58.65 month 11) + (1.02 t). }

In order to get the forecast sales for the next year, this is the fourth year. It is important to substitute: 199.25 for b0, 49.86 for b1, 29.17 for b2, 33.49 for b3, -23.88 for b4, -67.89 for b5, -62.58 for b6, -57.63 for b7, -101.28 for b8, -85.63 for b9, -58.65 for b10, 1.02 for b11

Therefore, the Forecast for the January of the fourth year will be

F1= 199.25 +(49.86 X1) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X 0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F1 = 199.25 + 49.86 + 0+0+0+0+0+0+0+0+0+37.74

F1= $286.85.

Vintage Restaurant sales for the January of the fourth year will be = 286.85.

Therefore, the Forecast for the February of the fourth year will be

F2= 199.25 +(49.86 X0) + (29.17 X1) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X 0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F2 = 199.25 + 0 + 29.17+0+0+0+0+0+0+0+0+37.74

F2= $266.16

The sales for February of the fourth year will be $266.16

Forecast for March

F3= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X1) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X 0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F3 = 199.25 + 0 + 0+33.49+0+0+0+0+0+0+0+37.74

F3= $270.48

The forecast sales for March = $270.48

Forecast for April

F4= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 1)+ ( -20.88 X 0) +( -67.89 X 0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F4 = 199.25 + 0 + 0+0-23.53+0+0+0+0+0+0+37.74

F4 = 213. 46.

The forecast for April = $213.46.

Forecast for May

F5= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 1) +( -67.89 X 0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F5 = 199.25 + 0 + 0+0-0- 20.88+0+0+0+0+0+37.74

F5 = 216.11

The forecast for May = $216.11

Forecast for June

F6= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X1) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F6 = 199.25 - 67.89+37.74

F6 = 169.10

The forecast for June = $169.10

Forecast for July

F7= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X0) + (-62.58 X1)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F7 = 199.25 - 62.58+37.74

F7 = 174.16

The forecast for July = $174.16

Forecast for August

F8= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X0) + (-62.58 X0)+ ( -57.63 X 1) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F8 = 199.25 – 57.63+37.74

F8 = 179.36

The forecast for August = $179.36

Forecast for September

F9= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 1) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F9 = 199.25 – 101.28+37.74

F9 = 135.71

The forecast for September = $135.71

Forecast for October

F10= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 1) + (-58.65 X 0) + (1.02 X 37)

F10 = 199.25 – 85.65+37.74

F10 = 151.34

The forecast for October = $151.34

Forecast for November

F11= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 1) + (1.02 X 37)

F11 = 199.25 –58.65+37.74

F11 = 178.34

The forecast for November = $178.34

Forecast for December

F11= 199.25 +(49.86 X0) + (29.17 X0) + (33.49 X0) +( -23.53 X 0)+ ( -20.88 X 0) +( -67.89 X0) + (-62.58 X0)+ ( -57.63 X 0) + ( -101.28 X 0) + ( -85.63 X 0) + (-58.65 X 0) + (1.02 X 37)

F11 = 199.25 +37.74

F11 = 237.02.

The forecast for November = $237.02

The forecast for the fourth year is as listed below:

Forecast

Value of sales for both food and beverage

286.85

266.16

270.48

213.46

216.11

169.10

174.16

179.36

135.71

F10

151.34

F11

178.34

F12

237.02

Vintage Restaurant

Sales Forecasts

Period

Year

Sales

M 6

M10

M11

Forecast

1st Year January

242

250

1st Year Feb

235

230

1st Year March

232

235

1st Year April

178

179

1st Year May

184

183

1st Year June

140

137

1st Year July

145

143

1st Year August

152

150

1st Year September

110

107

1st Year October

130

123

1st Year November

152

107

1st Year December

206

151

2nd Year January

263

211

2nd Year February

238

262

2nd Year March

247

248

2nd Year April

193

192

2nd Year May

193

195

2nd Year June

149

2nd Year July

157

156

2nd Year August

161

162

2nd Year September

122

119

2nd Year October

130

136

2nd Year November

167

164

2nd Year December

230

223

3rd Year January

282

274

3rd Year February

255

254

3rd Year March

265

260

3rd Year April

205

204

3rd Year May

210

207

3rd Year June

160

161

3rd Year July

166

168

3rd Year August

174

3rd Year September

126

131

3rd Year October

148

3rd Year November

173

176

3rd Year December

235

Monthly Sales Forecast for the Fourth Year

The forecast for the month of January = $286

Forecast for February = $266

Forecast for March = $270

Forecast for April = $213

Forecast for May = $216.11

Forecast for June = $169.10

Forecast for July = $174.16

Forecast for August = $179.36

Forecast for September = $135.71

Forecast for October = $151.34

Forecast for November = $178.34

Forecast for December = $237.02

Therefore, the projected sales for the four years are as listed below:

Months

First Year

Second Year

Third Year

Fourth Year

January

242

263

282

286

February

235

238

255

266

March

232

247

265

270

April

178

193

205

213.

May

184

193

210

216.11

June

140

149

160

169.10

July

145

157

166

174.16

August

152

161

174

179.36

September

110

122

126

135.71

October

130

148

151.34

November

152

167

173

178.34

December

206

230

235

237.02

Table 2: Projected sales for four years

Task 4: Model Illustration to Karen

The dummy variable is applied using two different values 1 and 0, which represent the month, which is to be obtained. In this case, the forecast value is represented by binary variable.

Task 5: Forecast Error Calculation

Actual Sales for January $ 295,000

Forecast sales $ 286, 850

The forecast Error is the different between the actual sales and forecast sales

$295,000 – $286,850

$8,150

And therefore the forecast Error = $8150X100/$295000

2.76%.

However, the tabulation established error of 2.76%, which is very small or ineligible and therefore, the forecast sales can be considered valid and therefore, accepted. Karen does not need to be worried about the error. It can be assumed that the forecast model is extremely good and therefore, Vintage Restaurant will make the forecast sales in the fourth year of its operation.

It is also essential to point that the forecast error could be as a result of estimation of the cost of sales, time and calculation. However, this error could be avoided with proper analysis of the data and accurate recording of the information capture. Serious attentive on the figures captured would be required so that future error could be avoided. Most importantly, the model is good and can be used anytime with computer.

Recommendation

It is important to point that Vintage Restaurant’s sales over the last three years is steady and therefore, it is expected to continue improving. The sales analysis for the last 36 months also indicates a systematic trend with a linear pattern. This means that the sales are seasonal and this could affect the financial performance of the hotel and therefore, it requires effective strategies to improve its market performance. It is recommended for the restaurant to invest in marketing since, the sales improvement is minimal. It is established that Vintage sales increases approximately 2.5% annually. This could be improved if the company invests in marketing strategies to bring more customers to the restaurant. It is also recommended for Karen to update the system monthly. This will ensure that the all the sales are properly captured in the system for the purpose of calculation.

Conclusion

Karen should not be worried about the error encountered at the end. It can be assumed that the model she used is extremely good. This is because of the minimal value of the error, which does not make much change. However, she must update the sales data monthly for her to be able to understand the pattern of the past sales. This will enable her to have a better prediction of any future sales of the company. The analysis derived could be easily updated monthly if any case computer software or application is used to conduct the analysis. However, the uncertainty can be removed when accuracy is kept. The estimation or approximation of data should be done efficiently to avoid any error on the sales.

More Subjects

Education

(5614)

English

(2294)

Business And Management

(1760)

Healthcare And Nursing

(1684)

Psychology

(929)

History And Anthropology