ACCT 6060/Bazaz/ Winter intersection 2021 (Due : Nov 15th 2021 Professor Review)

Shiraz, Inc. is a small producer and distributor of a product called Alpha. Shiraz Company has become interested in the preparation of operating and financial budgets. The budgets are vitally needed for operational planning and cost control purposes. You have been asked to assist Sara, the accountant of the company, in the preparation of budgets for the first three months of year 20X2.

Sales Forecasting:

Sara knows that the first step for the preparation of budgets is to forecast sales. Sara has been able to identify two possible variables that drive/influence the sales of the company. They are: the level of distribution of Alpha (number of dealers) and the amount of money spent on advertising (advertising expenditures). Sara wants to use the Regression method for the development of a regression equation/model for forecasting the sales of the company. She has accumulated 12 months of recent data, 20X1, on sales units, advertising expenditures, and the number of dealers as shown in Table I.

TABLE I

Sales

Advertising

Number of

Units

Expenditures

Dealers

249,000

19080

310

256,000

19440

337

264,000

19800

375

304,000

27000

312

320,000

30600

318

272,000

21600

303

296,000

28800

315

216,000

19800

225

232,000

19440

247

240,000

19620

270

268,000

23400

306

280,000

25200

309

Shiraz expects the following for the first five months of year 20X2:

January

February

March

April

May

Advertising Expenditures

$28,000

$25,000

$27,000

$26,500

$25,800

No. Of Dealers

285

300

318

310

265

Budgeting:

Management of Shiraz Company is interested in a number of budgets. The preferred formats for budgets are displayed in Exhibit I. As shown in Exhibit I, Sara's Excel file has two sections. Section One contains the required data for the preparation of budgets. Section Two contains the budgets. Since management of Shiraz wants to simulate the budgets for various possible conditions, the content of budgets will be stated all in formulas. The only area where data should be entered manually is in the data section, under the January column only! (Hint: this will require the use of excel functions including: "IF", "AND", "SUM", "ROUNDUP", "ROUNDDOWN", "ROUND" etc.). Sara has prepared the following data for the preparation of budgets:

1. Sales

• Selling price per unit of Alpha is expected to be $12 in January through February. Five percent increase in selling price is expected in March. Aril and May are expected to have the same selling price as in March.
• Seventy-five (75) percent of each month's sales are collected in the month of sale. The remaining is collected in the following month.