Question

MUST SHOW ALL CELL FORMULAS

Required: You need to prepare a comprehensive 12-month budget, including supporting schedules and a report for the period January 1, 2020 to December 31, 2020 for Hedron, Inc.

INFORMATION FOR HEDRON, INC.

Hedron, Inc. is a company that re-sells one product, a particularly comfortable lawn chair. An overseas contractor makes the product exclusively for Hedron, so Hedron has no manufacturing related costs.

PRODUCT COSTS

In Nov of 2019, each lawn chair costs Hedron $4 per unit. Per an existing contract, the cost of each chair is scheduled to increase by 5% on May 1, 2020. In addition, because of increasing costs of plastic webbing, the cost is anticipated to increase by an additional 5% on Sept 1, 2020.

PRODUCT SALES

Hedron sells each chair for $10 per unit.

Projected Sales units:

Year	Month	Amt
2019	Nov	11,250
	Dec	11,600
2020	Jan	10,000
	Feb	11,400
	Mar	12,600
	Apr	15,000
	May	18,000
	Jun	22,000
	Jul	18,000
	Aug	18,000
	Sep	17,000
	Oct	16,500
	Nov	12,000
	Dec	12,500
2021	Jan	11,000

To offset increasing costs of the chairs, the company plans to raise the sales price to $11.25 per unit beginning Sept 1, 2020. The sales forecast (i.e., estimated sales in units) takes this price increase into account.

CREDIT SALES

Monthly sales are 30% cash sales, 70% credit sales. 30% of credit sales are collected in the month of sale, 50% are collected the following month, and 16% are collected the 2^nd month after sale. The remaining receivables are deemed uncollectible at the end of the 2^nd month after sale. Bad debts are written off in the month the debt is deemed uncollectible

PRODUCT INVENTORY

The firm’s policy regarding inventory is to maintain their stock (i.e. have in ending inventory) at 40% of the forecasted sales in units for the next month. Hedron uses the first-in, first-out (FIFO) method in accounting for inventories.

40% of the inventory purchases are paid in the month of purchase with the remaining 60% paid the following month.

EQUIPMENT

A Note payment of $50,000 for equipment previously purchased is due in January and another Note payment of $30,000 is due in February. There are no Note Payables at the end of 2020.

OPERATING EXPENSES

Monthly Cash expenses are paid when incurred

                          

Salary and Wage Exp	$3,000
Sales Commissions Exp	7% of sales revenue
Rent Exp	$8,000
Gen & Admin Variable Exp	6% of sales revenue
Supplies Exp	$2,000
Gen & Admin Fixed Exp	$24,000
Gen & Admin Depreciation Exp	$24,000

(Cash expenses are paid when incurred)

CASH MANAGEMENT

The company must maintain a minimum cash balance of $15,000. Borrowing can make up shortfalls. For simplicity, assume that the bank will only lend (and accept repayments) in $1,000 increments. (Ignore interest on the loans, but minimize the amount borrowed and pay off any loans as soon as possible.)

DIVIDENDS

Dividends of $12,000 are paid in July.

BALANCE SHEET

Balance Sheet balances as of December 31, 2019:

Cash	$ 15,000
Accounts Receivable	$ 72,590
Supplies*	$ 2,000
Merchandise Inventory	$ 16,000
Buildings and Equipment*	$ 1,050,000
Accumulated Depreciation	$ 526,475
Accounts Payable	$ 26,304
Notes Payable	$ 80,000
Capital Stock*	$ 200,000
Retained Earnings	$ 322,811

*same balance in 2020

CHECK FIGURES

Cash Collections Budget

              Total Cash Receipts for January:             $    104,200

              Total Cash Receipts, Total 2020:             $ 1,835,333

              Bad Debt Exp for January:                       $        3,150

              Bad Debt Exp, Total 2020:                       $      51,951

Purchase Budget

              Cost of Purchases for Dec 2020:              $      52,479

Operating Expense Budget

              Cash disbursements for Op Exp for Dec $      55,281

Cash Budget

              Total cash available, Summary                 $ 1,850,333

              Total Cash Disbursements, Summary:     $ 1,549,658

              Ending Cash Balance for Dec:                  $   300,676

Budgeted Income Statement

              Gross Profit, Total:                                     $ 1,134,388

              Cost of Goods Sold Total                          $    768,112

              Net Income, Total:                                     $    103,113

Budgeted Balance Sheet

Total Assets Dec 2020:                             $    645,411

_________________________________________________________

с E M N Р H ONLY Pink Calls can contain a keyed in number Name: Yellow cells ist only contain formula Hedren. In Sales Budget For Year Ending Dec 31, 2020 Nov 19 Dec 19 Jan 20 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Ju 20 Aug 20 Sep 20 Oct20 Nov 20 De: 20 Annual TOAN Budged unit sales Beilng price per un Tor Sale Cash Sales Crocil Sale 9 10 11 12 13 14 10 16 17 18 19 Cash Sales Credit: Sales Tural Sales 21 Current month AR Colection 1 month price A Collection 2 ian's prir as Colections Uncalectic 24 Hedren. In Cash Collections Budget For Year Ending Dec 21, 2020 Jan 20 Feb 20 Mar 29 Apr '20 Jun 22 Jul 20 Aug 20 Sep 2 Oct20 Nov 20 Annual Total Curront month cach Sales Current month AR Collections month pro AR Colection 2 mentha prior AIR Collections Toral casa collectons 39 35 96 37 Bad Dahl Expense Desired ending Inventory 40 41 Hedron, Inc Purchase Budget Far Year Ending Dec 31, 2020 43 Nov '19 Dec 19 Jan 20 Mar20 Apr 20 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct20 Nov '20 De: 20 Summary 40 46 47 48 45 Budged unit saks Acd desired ending invertory Torres LASS Beginning inventory Required Unt Purchases 51 32 Cost perunt Cost of Purchases

Answer 1

Answer:

Sales Budget:

A 1 2 3 4 G H K M Hedron Inc. Sales Budget For Year Ending Dec 31, 2020 Nov'19 Dec 19 Jan'20 Feb'20 Mar'20 Apr 20 May'20 Jun'20 Jul 20 Aug 20 Sep 20 Oct20 Nov'20 Dec'20 Annual Total 11,250 11,600 10,000 11,400 12,600 15,000 18,000 22,000 18,000 18,000 17,000 16,500 12,000 12,500 183,000 $ 10 $ 10$ 10 $ 10$ 10$ 10$ 10$ 10$ 10 $ 10$ 11.25$ 11.25 $ 11.25$ 11.25 $ 112,500 $ 116,000 $ 100,000 $ 114,000 $ 126,000 $ 150,000 $ 180,000 $ 220,000 $ 180,000 $ 180,000 $ 191,250 $ 185,625 $ 135,000 $ 140,625 $ 1,902,500 =B5*B6 .=C5 C6 . =D5*D6 .=N5*N6 =O5*06 =SUM(D7:07 30% 30% 30% 30% 30% 30% 30% 30% 30% 30% 30% 30% 30% 30% 70% 70% 70% 70% 70% 70% 70% 70% 70% 70% 70% 70% 70% 70% 37,800 $ 45,000 $ 54,000 $ 66,000 $ 54,000 $ 54,000 $ 57,375$ 55,688 $ 88,200 $ 105,000 $ 126,000 $ 154,000 $ 126,000 $ 126,000 $ 133,875$ 129,938$ 5 Budget Unit sales 6 Selling price per unit 7 Total sales 8 9 Cash sales % 10 Credit sales % 11 12 Cash sales 13 14 Credit sales 15 16 Total sales 17 18 19 Current month A/R collection 20 21 1 month prior A/R collection 22 23 2 month prior A/R collection 24 25 Uncollectible $ 33,750 $ 34,800 $ 30,000 $ 34,200 $ =B7*B9 $ 78,750 $ 81,200 $ 70,000 $ 79,800 $ .=B7*B10 $ 112,500 $ 116,000 $100,000 $ 114,000$ =B12+B14 40,500 $ 42,188 .=07*09 94,500 $ 98,438 .=07*010 135,000 $ 140,625 .=O12+014 126,000 $ 150,000 $ 180,000 $ 220,000 $ 180,000 $ 180,000 $ 191,250 $ 185,625 $ 26,460 $ 31,500 $ 37,800 $ 46,200 $ 37,800 $ 37,800 $ 40,163 $ 39,900 $ 44,100 $ 52,500 $ 63,000 $ 77,000 $ 63,000 $ 63,000 $ $ 23,625 $ 24,360 $ 21,000 $ 23,940$ -=B14*30% .=C14*30% .=D14*30% $ 39,375 $ 40,600 $ 35,000 $ .=B14*50%.C14*50% $ 12,600 $ 12,992 $ .=B14*16% .=C14*16% $ 3,150 $ 3,248 $ =B14*4% =C14*4% 38,981 $ 28,350 $ 29,531 N14*30% .=014*30% 66,938 $ 64,969 $ 47,250 .=M14*50% N14*50% 20,160 $ 21,420$ 20,790 .=L14*16% .=M14*16% 5,040 $ 5,355$ 5,198 .=L14*4% .=M14*4% 11,200 $ 12,768 $ 14,112 $ 16,800 $ 20,160$ 24,640 $ 20,160 $ 2,800 $ 3,192 $ 3,528$ 4,200 $ 5,040$ 6,160 $ 5,040$ 26

Cash Collection Budget:

A B D F G Н K M N 28 Hedron Inc. 29 Cash Collection Budget 30 For Year Ending Dec 31, 2020 31 Jan 20 Feb'20 Mar'20 Apr 20 May'20 Jun'20 Jul 20 Aug 20 Sep 20 Oct 20 Noy'20 Dec'20 Annual Total 32 Current month cash sales $ 30,000 $ 34,200 $ 37,800 $ 45,000 $ 54,000 $ 66,000 $ 54,000 $ 54,000 $ 57,375$ 55,688$ 40,500 $ 42,188 $ 570,750 33 .=D12 .=E12 .-012 . ESUM(B32:M32) 34 Current month A/R collections 21,000 $ 23,940 $ 26,460 $ 31,500 $ 37,800 $ 46,200 $ 37,800 $ 37,800 $ 40,163 $ 38,981 $ 28,350 $ 29,531 $ 399,525 35 1 month prior A/R collection $ 40,600$ 35,000$ 39,900 $ 44,100 $ 52,500 $ 63,000 $ 77,000 $ 63,000 $ 63,000 $ 66,938 $ 64,969 $ 47,250 $ 657,256 36 2 month prior A/R collection $ 12,600 $ 12,992 $ 11,200 $ 12,768 $ 14,112 $ 16,800 $ 20,160 $ 24,640 $ 20,160$ 20,160 $ 21,420 $ 20,790$ 207,802 37 Total cash collection $ 104,200 $ 106,132 $ 115,360 $ 133,368 $ 158,412 $ 192,000 $ 188,960 $ 179,440 $ 180,698 $ 181,766 $ 155,239 $ 139,759 $ 1,835,333 38 .=SUM(B32:B36) 39 40 Bad debt expense $ 3,150 $ 3,248$ 2,800 $ 3,192 $ 3,528$ 4,200 $ 5,040 $ 6,160 $ 5,040$ 5,040$ 5,355 $ 5,198$ 51,951 41 42 Desired ending inventory 4,560 5,040 6,000 7,200 8,800 7,200 6,800 6,600 4,800 5,000 4400 43 .=E5*40% .=F5*40% .=05*40% .=11000*40% 7,200

Purchase Budget:

A 46 47 48 49 Budget Unit sales (Given) 50 Add: Desired ending inventory 51 Total unit needs 52 53 Less: Beginning inventory 54 55 Required unit purchase 56 57 Cost per unit 58 Cost of purchases 59 B D E F G H K M N Purchase Budget For Year Ending Dec 31, 2020 Jan'20 Feb'20 Mar'20 Apr'20 May'20 Jun 20 Jul 20 Aug 20 Sep 20 Oct20 Nov'20 Dec'20 Summary 10,000 11,400 12,600 15,000 18,000 22,000 18,000 18,000 17,000 16,500 12,000 12,500 $ 183,000 4,560 5,040 6,000 7,200 8,800 7,200 7,200 6,800 6,600 4,800 5,000 4,400 4400 14,560 16,440 18,600 22,200 26,800 29,200 25,200 24,800 23,600 21,300 17,000 16,900 187,400 .=B49+B50 .=M49+M50 4,000 4,560 5,040 6,000 7,200 8,800 7,200 7,200 6,800 6,600 4,800 5,000 4000 =B49*40% =B50 .=C50 .=L50 10,560 11,880 13,560 16,200 19,600 20,400 18,000 17,600 16,800 14,700 12,200 11,900 183,400 .=B51-B53 .=B51-B53 $ 4.00 $ 4.00 $ 4.00 $ 4.00 $ 4.20$ 4.20$ 4.20$ 4.20 $ 4.41 4.41 $ 4.41 $ 4.41 $ 42,240 $ 47,520 $ 54,240 $ 64,800 $ 82,320 $ 85,680$ 75,600 $ 73,920 $ 74,088$ 64,827 $ 53,802 $ 52,479 $ 771,516 .=B55*B57 .=M55*M57

Schedule of Budgeted Cash Disbursement for Merchandise Purchase:

А. - K M N 61 E F G H Hedron Inc. Schedule of Budgeted Cash Disbursement for Merchandise Purchase For Year Ending Dec 31, 2020 Apr'20 May'20 Jun'20 Jul 20 Aug 20 Sep 20 25,920 32,928 34,272 30,240 29,568 29,635 Oct 20 Nov'20 Feb'20 Mar'20 19,008 21,696 25,931 21,521 62 63 64 Jan'20 65 Current purchase A/P payment 16,896 66 .=B58*40% 67 1 month prior A/P payment 26,304 68 69 Cash disbursement for merchar 43,200 70 =B65+B67 28,512 32,544 38,880 49,392 51,408 45,360 44,352 44,453 38,896 25,344 .=B58*60% 44,352 Dec 20 Summary 20,992 =M58*40% 32,281 .=L58*60% 53,273 $ 766,333 .=M65+M67 50,208 58,464 71,808 83,664 81,648 74,928 73,987 70,384 60,417

Operating Expenses Budget:

Hedron Inc. Operating Expenses Budget For Year Ending Dec 31, 2020 Jan'20 Feb'20 Mar20 Apr'20 May'20 Jun'20 Jul 20 Aug 20 Sep 20 Oct20 Nov'20 Dec'20 Summary Salaries and wages expense $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000 $ 3,000 Sales commission exp 7,000 7,980 8,820 10,500 12,600 15,400 12,600 12,600 13,388 12,994 9,450 9,844 Rent exp 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 Gen and Admn variable exp 6,000 6,840 7,560 9,000 10,800 13,200 10,800 10,800 11,475 11,138 8,100 8,438 Supplies exp 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 Gen and Admn fixed exp 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 Gen and Admn depreciation ex 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 Bad debt exp 3,150 3,248 2,800 3,192 3,528 4,200 5,040 6,160 5,040 5,040 5,355 5,198 Total operating exp $ 77,150 $ 79,068 $ 80,180$ 83,692 $ 87,928 $ 93,800 $ 89,440$ 90,560$ 90,903 $ 90,171 $ 83,905 $ 84,479 Less: Depreciation exp (non-ca 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 24,000 Less: Bad debt exp (non-cash) 3,150 3,248 2,800 3,192 3,528 4,200 5,040 6,160 5,040 5,040 5,355 5,198 Cash disbursement for operatin $ 50,000 $ 51,820$ 53,380 $ 56,500$ 60,400$ 65,600 $ 60,400 $ 60,400 $ 61,863 $ 61,131$ 54,550 $ 55,281 $ 691,325

t