CREATING A BUDGETING INFORMATION SYSTEM IN EXCEL

SIGMA CORPORATION

SIGMA company is preparing its budget for the first quarter of 2011 and has collected the data below:

a. A unit of finished product requires 3 pounds of direct material at $2 a pound, and 30 minutes of direct labor at $7 an hour. Direct materials purchases are paid 25% in the month of purchase and 75% in the following month. Accounts payable is for credit purchases only. All other costs are paid in the month incurred.

b. At the end of each month SIGMA wants a finished goods inventory equal to 70% of the next month’s sales and a direct materials inventory of 120% of the current month’s production requirement.

c. Manufacturing overhead consist of the following: indirect materials $0.50 per unit, indirect labor $0.60 per unit, other variable costs $0.40 per unit, salaries $3,000 per month, depreciation $1,500 per month, and other fixed costs $1,110 per month.

d. Selling costs are sales commissions ($0.30 per unit sold) and shipping ($0.14 per unit sold). Administrative costs each month are: salaries $800; rent $500; and depreciation $400.

e. The sales forecast is 2,000; 1,700; 1,600; and 1,500 units for January, February, March, and April, respectively. The unit sales price is $16. All sales are on credit and collections are 40% in the month of sale and 60% the following month. Accounts receivable is for credit sales only.

f. SIGMA plans to buy equipment costing $10,000 in January.

______________________________________________________________

SIGMA Corporation has the following balance sheet at December 31, 2010:

Assets Liabilities

Cash……………………………….. $10,200 Accounts payable …………… $ 5,400

Accounts receivable ………………. 17,200

Inventories:

Direct materials (4,400 units)…….. 8,800

Finished goods (1,400 units) 20,160 Stockholder’s Equity

Equipment …………………………. 50,000 Common stock ………………..10,500

Less: Accumulated depreciation ...(30,000) Retained earnings ………….. 60,460

Total liabilities and

Total assets ……………………$76,360 Stockholders’ equity $ 76,360

Prepare the master budget for the first quarter of 2011 for each month and the quarter in total. This should include the sales budget, production budget, direct material budget, direct labor budget, manufacturing overhead budget, selling and administrative budget, cash receipts and disbursements budget, budgeted income statement, and budgeted balance sheet.

2