Reference no: EM13683338

Shaw, Inc. is a small producer and distributor of a product called Alpha. Shaw 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 2015.

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 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 (2014) on sales units, advertising expenditures, and the number of dealers as shown in Table I.

TABLE I

Sales	Advertising	Number of
Units	Expenditures	Dealers
248,000	19080	300
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

Shaw expects the following for the first five months of year 2015:

	January	February	March	April	May
Advertising Expenditures	$26,000	$25,000	$27,000	$26,500	$25,800
No. Of Dealers	290	300	208	285	265

Budgeting:

Management of Shaw 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 Shaw wants to simulate the budgets for various possible conditions, the content of budgets will be stated all in formulas. 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 and after.
• Seventy-five (75) percent of each month's sales are collected in the month of sale. The  remaining is collected in the following month.

2. Manufacturing Expenses

Production of each Alpha requires:

• Direct materials: 5 pounds of direct material @ $0.80 per  pound
• Direct Labor: ½ hour of  direct labor @ $10 per hour
• Variable manufacturing  overhead (each unit): $0.50 per  unit
• Fixed manufacturing  overhead: Total of $25,000 per  month of which $3,000 is depreciation expense

3. Operating (Selling & Administrative) Expenses

• Sales Commission: $0.80  per unit
• Shipping and Handling:  $0.50 per unit
• Fixed Operating  Expenses: Total $12,000 per month of which $1,500 is depreciation expense.

4. Payment of Expenses

• All expenses are paid in the same month  incurred.

5. Capital Expenditures

• Shaw is in the process  of expansion of its operations by adding new equipments. The expansion  requires $200,000 cash outflow in the month of March.

6. Loan Repayments & Interest Expense

Due to the financing obligations, company is going to have the following cash outflows:

January            February     March 

$230,000    $240,000    $210,000

7. Inventory Policy

• It is the company's policy to maintain an inventory of Alpha at the end of each month equal to 20% of next month's anticipated sales.
• Company also maintains an inventory of raw materials equal to 25% of next month's production needs.

8. Other Information

• Sales of December 2014  were $3,360,000.
• The balance of cash on  December 31, 2014 was $21,000. Company requires a minimum balance of  $20,000 cash on hand at all times.
• Applicable income tax  rate is 30%.

Requirements:

Write a one to two page single-spaced memo to the management of Shaw Company and include/attach your findings (use 1" margin along with Arial Font with font size of 10 for your report). Specifically:

1. Using the data provided by Sara in Table I calculate all possible regression equations that could be used for forecasting sales of Shaw Company. In your memo, discuss these various equations and state which of the equations you would recommend to be used. Provide a complete and detailed justification for your choice. Also, discuss the meaning of the regression coefficients of the equation that you choose. Attach your Excel work for this section to your memo. In your formulas, carry two decimal points for coefficient of X variables and none for intercept.

2. Using the chosen equation in Item 1, forecast sales of Shaw Company for January to May and state the results in a Table similar to the following in the memo.

Month	January	February	March	April	May
Sales Units

3. Using the format in Exhibit I, summarize the requisite data for the preparation of budgets in Section One of your Excel file. Then, write the necessary formulas for all the cells in Section Two to calculate the budgets. Format nicely the content of budgets (allow two decimal points for "per unit" items and no decimal point for total items). Attach a print copy of (a) the budgets containing numbers, and (b) the budgets displaying the formulas in the cells.

4. There are rumors that the price of direct materials and direct labor for Alpha to be increased by 15% and 5%, respectively effective January 1, 2015. Management of Shaw wants to know the impact of this increase on its financial position. Recalculate the budgets by incorporating the expected increases in the price of direct materials and direct labor. Compute and discuss the percentages of change in the profit and cash balance of the company due to the increase in the price.