The following data relates to the operations of Heritage Company:
		Units
Sales Budget:	June	22,000
	July	25,000
	August	35,000
	September	36,000
	October	35,000
	November	30,000
Finished Goods Inventory:	FG Inventory @ 6/30/04	5,000	units
	Desired FG Inventory	20%	of the following month's sales
Raw Materials:	RM pounds per finished unit	3.5
	RM Inventory (in pounds) @ 6/30/04	37,800
	Desired RM Inventory (in pounds)	40%	of the following month's production requirements
	RM cost per pound	$ 6.25
	Paid for in the month of purchase	55%
	Paid for in the month following purchase	45%
	A/P Balance @ 6/30/04 paid in July	$ 270,000
Direct Labor:	Direct Labor Hours per Unit	1.00
		Jul	Aug	Sept
	Direct Labor Hour Rate	$ 8.00	$ 8.50	$ 9.00
		Jul	Aug	Sep
Other costs	Manufacturing overhead	196,000	242,000	285,000
	Selling Expense	110,000	125,000	145,000
	Administrative Expense*	100,000	100,000	100,000
	Dividends	30,000	0	0
	Equipment purchases	0	120,000	50,000
	*Depreciation expense included in
	the administration expense above	15,000	15,000	15,000
Selling Price:	Selling price per unit	$ 55.00
Sales Collections :	Month of sale	35%
	Month following sale	65%
	A/R balance @ 6/30/04 collected in July	$ 650,000
Cash balances:	Balance at 7/1/2004	$ 40,000
	Minimum balance required	$ 35,000
Borrowings*:	Annual Interest Rate	12%
	Borrow in multiples of	$ 1,000
*The money is borrowed at the beginning of the month and paid back on the last day of the month. Therefore, money borrowed in July and paid back in September is outstanding for 3 months (July 1 to September 30), and the interest rate is 3/12 of 12%. Money borrowed in August and paid back in September is outstanding for 2 months (August 1 to September 30), and the interest rate is 2/12 of 12%.
Required:
Enter your name in the highlighted space provided at the top of this worksheet. Using Microsoft Excel, prepare the following schedules in separate worksheets (see the labels below). Check figures for selected schedules are provided.
1. Production Budget for July, August, September, and October (Example Schedule 2)
2. DM Purchases Budget for July, August, and September (Total quarter 352,800 pounds and $2,205,000)
Schedule of Expected Cash Payments for Direct Materials for July, August, September, and Quarter (Total quarter $2,129,681) (Example Schedule 3)
3. Direct Labor Budget for July, August, September and Quarter. (Example Schedule 4)
4. Schedule of Expected Cash Collections for July, August, September, and Quarter (Total quarter $4,643,000) (Example Schedule 1)
5. Cash Budget for July, August, September, and Quarter (September ending cash balance $153,279) (Example Schedule 8)
You are expected to use cell formulas to insert the numbers from the given information above to the schedules and for ALL calculations throughout the problem. Also, use cell formulas to carry forward numbers from one schedule to the next when applicable. You may type in numbers for the financing section of the Cash Budget.
This assignment is worth 50 points. You will be graded on format, presentation of the correct answer, and appropriate use of formulas throughout. The ONLY place you can enter a number is in the financing section of the cash budget. Everything else should be a formula.

Budget TOSHIBA