Problem - You are the Assistant Controller for the RJC Corporation and have been given the responsibility for formulating the budget for the upcoming year. The RJC Corporation is experiencing a period of rapid expansion, so production planning, inventory levels and cash flow will be exceedingly important.
The following information will be used to prepare the budget:
1. RJC Corporation has experienced an excellent record in accounts receivable collection and expects this trend to continue. Sixty percent of billings are collected in the month after the sale and 40% in the second month after the sale. Uncollectible accounts are minimal and can be ignored in your analysis.
2. The purchase of parts is RJC Corporation's largest expenditure: The cost of these items is equal to 50% of sales. Sixty percent of these parts are received one month prior to sale and 40% are received during the month of sale.
3. Eighty percent of accounts payable have been paid one month after receipt of purchases and the remaining 20% have been paid two months after receipt.
4. Wages, including fringe benefits, are a function of sales volume and are equal to 20% of the current month's sales. These wages are paid in the month incurred.
5. General and administrative expenses include:
A. $480,000 in salaries
B. Product promotions, 3% of Sales
C. $240,000 in property taxes
D. $360,000 for insurance
E. $300,000 in utilities
F. $600,000 in depreciation
G. Workers Compensation Insurance, 1.5% of Direct Labor
All expenses except property taxes, Product Promotions and Workers Compensation Insurance are incurred uniformly throughout the year. Property taxes are paid in four equal installments in the last month of each calendar quarter.
6. The company makes income tax payments in the first month of each quarter based on the income for the prior quarter. The company is subject to an effective income tax rate of 40%. The pretax income for the last quarter of this year is projected to be $612,000.
7. Factory overhead is a function of the cost of Direct Labor, and is predetermined at 85%,
Required:
A. Prepare the spreadsheets from the templates with the necessary figures.
B. Sales for June were 90% of budgeted. Prepare a flexible budget and a performance report for the month of June.
C. Sales for each month are listed below
|
November
|
1,798,500.00
|
July
|
3,270,000.00
|
|
December
|
1,853,000.00
|
August
|
3,270,000.00
|
|
January
|
1,962,000.00
|
September
|
3,488,000.00
|
|
February
|
2,180,000.00
|
October
|
3,488,000.00
|
|
March
|
1,962,000.00
|
November
|
3,270,000.00
|
|
April
|
2,398,000.00
|
December
|
3,706,000.00
|
|
May
|
2,725,000.00
|
January
|
3,597,000.00
|
|
June
|
3,052,000.00
|
|
|