Dr. M.S. Bazaz

bazaz@oakland.edu

MANAGERIAL ACCOUNTING

GROUP COMPUTER PROJECT

 

 

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

 

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 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

 

Sun expects the following for the first five months of year 2004:

 

	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 Sun 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 Sun 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

• Other than purchase of materials, all expenses are expected to be paid in the month incurred. Sixty (60) percent of purchases are paid in the month of purchase and the remaining is paid in the following month.

.

5. Capital Expenditures

• Sun 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 its financing obligations, the company is going to have the following cash outflows:

January             February           March  

    Loan Principal           $230,000          $240,000          $210,000

    Interest Expense          $25,000            $25,500            $20,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 2002 were $3,360,000.
• Direct material purchases for December 2002 were $1,800,000.
• The balance of cash on December 31, 2002 was $21,000.
• The Company desires to maintain a minimum balance of $20,000 cash on hand at all times.
• Applicable income tax rate is 30%.

 

 

Requirements:

1.      Using the data provided by Sara in Table I calculate all possible regression equations that could be used for forecasting sales of Sun Company.  In your memo, discuss these various equations and state which of the equations would you 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 Sun 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.   Copy Exhibit I (in the below) to an Excel sheet.

 

4.      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). Save your work and:

 

a. Obtain a print copy of the budgets for January to March (including the data section). Your computer printout for this requirement should not exceed one or two pages. To fit everything in one or two pages follow these instructions: (a) Highlight the area that you want to print, (b) Click “File”, then “Print Area”, and then “Set Print Area”.  Then, click “File”, then “Page Setup”, and then choose “Fit to” to 1 or 2 pages, then “OK”.

 

b. Obtain a print copy of the formulas. To convert number to formulas on the screen, follow these instructions: (a) Click “Tools”, then “Options”, then “Formulas”, then “OK”.  Do some cleaning and alignments before printing it –e.g., lineup, adjustments in the column weights, etc. Your printout for this part should not exceed one or two pages.

 

5.   According to rumors, the price of direct materials and direct labor may increase by 15% and 5%, respectively.  Management of Sun wants to know the impact of this increase, if realized, on its operations. Recalculate the budgets by incorporating the expected increases in the price of direct materials and direct labor.  Save your work under different file name. Obtain a print copy of the budgets for January to March (including the data section) under new changes. Your computer printout for this requirement should not exceed one or two pages.

 

6.  By comparing the original budgets to the budgets in Item 4, complete the following table to reflect the impact of changes in prices of materials and labor on the unit cost and profit of the company:

 

	January	February	March
% Of Increase (Decrease) in Unit Cost
% Of Increase (Decrease) in Profit

 

7. Prepare a one to two page single-spaced memo to the management of Sun Company with the following contents:

 

Date

To: Management of Sun Company

From: You

Re: Budgets For Period January to March 2004

 

In the first paragraph state your mission in this assignment. Then have the following headings and contents.

 

 

Cost Behavior

Discuss the requirement #1 above here.

 

Budgets

Under this heading, in simple words explain the purpose of each budget along with those numbers from the budget that you feel is important for reporting to the management of Sun Company.

 

Simulating Budgets

Under this heading explain the results of simulating the budgets for the expected increase in the prices of direct materials and direct labor and the impact on the unit cost and the profit of the company. Include a table such as the one below for displaying the percentage changes in the unit cost and profit of the company (in comparison to the original set of budgets).

 

	January	February	March
% Of Increase (Decrease) in Unit Cost
% Of Increase (Decrease) in Profit

 

Conclusion/Recommendations

Devote the last paragraph to your concluding remarks/recommendations--anything that you think is important.

 

 

What Ought to Be Submitted (in the following order and stapled-no folder is needed):

1) Your Memo

2) Printout of the Calculated budgets in Requirement #1 above.

3.  Your Excel work in #1

4) Printout of the Formulas

5) Printout of the Simulated Budgets

 

 

Hint:  To save time in typing, you could copy the necessary data from this Word file to your Excel file.

 

 

Points Assignment:

10 points	Clearness, completeness, and understandability of your memo
20 points	Part 1
10 points	Part 2
20 points	Part 3
20 Points	Part 4
20 Points	Part 5
100 Points	Total

 

Check Figures for Budgets:

Sales Forecasting Equation	If Unit Sales is a Function of Advertising Expense	If Unit Sales is a Function of No. Of Dealers	If Unit Sales is a Function of Advertising Expense & No. Of Dealers
January Production in Units
January Total Production Cost
February Total Cost of DM Purchases
March Financing/Borrowing

 

 

 

Exhibit I

SECTION ONE: DATA FOR BUDGETS
Sales Data:	January	February	March	April	May
Sales Units
Selling price per unit
Percentage of sales collected in the month of sales
Percentage of sales collected in the following month
Variable Expenses:
Pounds of material needed per unit of Alpha
Price of material per pound
Direct labor hours needed per unit of Alpha
Direct labor rate per hour
Variable manufacturing overhead per unit of Alpha
Variable operating expenses per unit of Alpha
Fixed Expenses:
Fixed manufacturing overhead
Depreciation portion of fixed overhead
Fixed operating expenses
Depreciation portion of fixed operating expenses
Inventory Policy:
Desired ending inventory of Alpha (% of next month sales)
Desired ending inventory of DM (% of next month's production needs)
Other Information:
Capital Expenditures
Loan Repayments & Interest Expense
Income tax rate
Minimum cash retained at the end of month
December 2002 Sales dollars
Cash balance as of January 1, 2004
SECTION TWO: BUDGETS
Sales Budget	January	February	March	April	May
Sales in Units
Unit Selling Price
Sales in Dollars
Production Budget	January	February	March	April	May
Sales Units
Add: Desired Ending Inventory
Total Required Units
Less: Beginning Inventory
Required Production Units
Direct Materials Budget (Purchases Budget)	January	February	March	April	May
Units to be Produced
Direct Material Qty Required Per Unit of Alpha (pounds)
Total Direct Materials Needed for Production (pounds)
Add: Desired Ending Inventory Direct Materials
Total Direct Materials Needed
Less: Beginning Inventory of Direct Materials
Direct Material Purchases (pounds)
Cost Per Pound
Total Cost of DM Purchases
Cost of Production Budget	January	February	March	April	May
Units to be Produced
Direct Material Costs
Direct Labor Cost
Variable Manufacturing Cost
Fixed Manufacturing Cost
Total Production Costs
Cost of Production Per Unit
Operating Expense Budget	January	February	March	April	May
Variable
Fixed
Total Operating Expenses
Budgeted Income Statement	January	February	March	April	May
Sales ($)
Cost of Goods Sold
Gross Profit
Operating Expenses
Income from Operations
Income taxes
Net Income
Cash Budget	January	February	March	April	May
Beginning Cash Balance
Add: Receipts
Current Month Sales
Prior Period Month Sales
Total Receipts
Total Cash Available
Less: Disbursements
Direct Materials Purchases
Direct Labor
Fixed Manufacturing Overhead
Variable Manufacturing Overhead
Fixed Operating Expenses
Variable Operating Expenses
Income Taxes
Capital Expenditures
Loan Repayment & Interest Expense
Total Disbursements
Excess (deficiency) of available cash over disbursements
Financing/Borrowing
Ending Cash Balance