The following data relates to the operations of Heritage Company: Units Sales Bu

Question

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

Explanation / Answer

Formulas:

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you. Working-1 Sales Budget June July Aug Sep Oct Total Nov Estimated Unit Sale 22000 25000 35000 36000 35000 131000 30000 Selling Price Per Unit 55 55 55 55 55 55 55 Total Estimated Sale 1210000 1375000 1925000 1980000 1925000 7205000 1650000 1. Production Budget July Aug Sep Oct Total Expected units to be sold 25000 35000 36000 35000 131000 30000 add: desired ending inventory 7000 7200 7000 6000 6000 Total Units available for sale 32000 42200 43000 41000 137000 30000 Less: Beginning invenory 5000 7000 7200 7000 5000 6000 Production Needed 27000 35200 35800 34000 132000 24000 2. Direct Material Purchase Budget working July Aug Sep Oct Quarter Material Needed for production: 3.5 for one unit Prod*3.5 94500 123200 125300 119000 343000 add: desired ending inventory 49280 50120 47600 1400 47600 Total Units available for usage 143780 173320 172900 120400 390600 Less: Beginning invenory 37800 49280 50120 47600 37800 Total Purchases to be made 105980 124040 122780 72800 352800 Per Unit Purchase Price           6.25           6.25           6.25           6.25 Total Purchases in $ 662375 775250 767375 2205000 2. Schedule of Expected Cash Payments July Aug Sep Quarter Payable Accounts Payable-Last Year 270000 270000 Jul 364306 298069 662375 Aug 426388 348863 775250 Sep 422056 422056 345319 634306 724456 770919 0 2129681 345319 3. Direct Labor Budget July Aug Sep Oct Quarter Labor Hour Requried: Budgeted Production 27000 35200 35800 98000 Hours needed 1.0 1.0 1.0 Total Hour Required 27000 35200 35800 98000 Per Hour Cost 8 9 9 Total Labor Cost 0 216000 299200 322200 837400 4. Schedule of Expected Cash Collection July Aug Sep Quarter Receivable Accounts Receivable-Last Year 650000 650000 Jul 481250 893750 1375000 Aug 673750 1251250 1925000 Sep 693000 693000 1287000 1131250 1567500 1944250 0 4643000 1287000 5. Cash Budget July Aug Sep Quarter Beginning Balance 40000 35574 35748 40000 add: Cash Collection 1131250 1567500 1944250 4643000 Total Available Cash 1171250 1603074 1979998 4683000 Cash Disbursment for: Material 634306 724456 770919 2129681 Labor 216000 299200 322200 837400 Manufacturing overhead 196000 242000 285000 723000 Selling Expense 110000 125000 145000 380000 Administrative Expense (Excluding Dep) 85000 85000 85000 255000 Dividends 30000 30000 Equipment purchases 120000 50000 170000 Total Cash Disbursment 1271306 1595656 1658119 4525081 Cash Surplus/(Deficit) -100056 7418 321879 157919 Borrowing 137000 30000 167000 Interest 12%*Borrowing*Months/12 1370 1670 1670 4710 Repayment 167000 167000 Ending Cash Balance 35574 35748 153209 153209

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.