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
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 2nd month after sale. The remaining receivables are deemed uncollectible at the end of the 2nd month after sale. Bad debts are written off in the month the debt is deemed uncollectible
PRODUCT INVENTORY
The firms 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
please include Cell formulas if possible !!
Sales Budget For Year Ending Dec 31, 2020 Nov '19 11,250 10.00 112,500 $ Budged unit sales Selling price per unit Total Sales Dec '19 11,600 10.00 $ 116.000 Jan 20 10,000 10.00 $ 100.000 Feb 20 11,400 10.00 $ 114.000 $ Mar '20 12,600 10.00 $ 126.000 Jan 21 11,000 Apr 20 15,000 10.00 $ 150.000 $ May 20 18,000 10.00 180.000 Jun 20 22,000 10.00 220.000 $ Jul 20 18,000 10.00 180.000 Aug 20 18,000 10.00 $ 180.000 Sep 20 17,000 11.25 $ 191.250 Oct 20 16,500 11.25 $ 185.625 S Nov 20 12,000 11.25 $ 135.000 Dec 20 Annual Total 12,500 205,850 11.25 140.625$ 2. 131.000 Cash Sales % Credit Sales % 30% 70% 37,800 $ 40,500 $ Cash Sales Credit Sales Total Sales 33,750 $ 78,750 F 112,500 $ 34,800 $ 81,200 116.000 $ 30,000 $ 70,000 100.000 $ 34,200 $ 79,800 114.000 88,200 F 45,000 $ 105.000 150.000 $ 54,000 $ 126,000 180.000 66,000 $ 154.000 220.000 $ 54,000 $ 126.000 180.000 54,000 $ 126,000 180.000 $ 57,375 Fs 133.875 191.250 $ 55,688 FS 129,938 185.625 94,500 42, 188$ 98,438 140.625 $ 639.300 1,491,700 2.131.000 126.000 135.000 Current month A/R Collections 1 month prior AVR Collections 2 months prior A/R Collections Uncollectible 30% 50% 16% 4% Hedron, Inc. Cash Collections Budget For Year Ending Dec 31, 2020 Jan '20 Feb 20 Mar 20 Apr 120 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov '20 Dec 20 Annual Total Current month cash Sales Current month A/R Collections 1 month prior AVR Collections 2 months prior A/R Collections Total cash collections Bad Debt Expense Desired ending inventory % Hedron, Inc. Purchase 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 Oct 20 Nov 20 Dec '20 Summary Budged unit sales Add desired ending inventory Total unit needs Less Beginning Inventory Required Unit Purchases Cost per unit Cost of Purchases % Paid in Month of Purchase % Paid in Month after Purchase Hedron, Inc. Schedule of Budgeted Cash Disbursements for Merchandise Purchases For Year Ending Dec 31, 2020 Jan 120 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov 20 Dec 20 Annual Total Current purchases A/P Payments 1 month prior A/P Payments Cash disbursements for merchandise purchases Fixed Operating expenses: Salary and Wage Exp Rent Exp Supplies Exp Gen Admin Fixed Exp Gen & Admin Depreciation Exp Variable Operating Expenses: Sales Commission Exp % of Revenue Gen & Admin Variable Exp % of Revenue Hedron, Inc. Operating Expense 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 Oct 20 Nov 20 Dec 20 Annual Total Salary and Wage Exp Sales Commission Exp Rent Exp Gen & Admin Variable Exp Supplies Exp Gen & Admin Fixed Exp Gen & Admin Depreciation Exp Bad Debt Exp Total Operating Exp Less: Depreciation Exp (noncash) Less: Bad Debt Exp (noncash) Cash Disbursements for Operating Expenses Note payment Equipment - January Note payment Equipment- February Dividends - July Minimum Monthly Cash Budget Hedron, Inc. Cash 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 Oct 20 Nov 20 Dec 20 Summary Jan 1st Balance Add collections from customers Total cash available Cash balance, beginning Add collections from customers Total cash available Less disbursements: Cash disbursements for merchandise purchases Cash Disbursements for Operating Expenses Note Payments - Equipment Total cash disbursements Excess of receipts over disbursements Financing: Borrowing-note Repayments-note Cash balance, ending Dividends Hedron, Inc. Inventory Data COGS Calculation using FIFO Cost Assumption Beg Inventory Purchases Beg Inventory Units Purchases Units COGS $ COGS Units Ending Inventory Ending Inventory $ Units Jan Feb Mar Apr May May Jun Ju Aud AUS Sep Oct Nov Dec Hedron, Inc. Budgeted Income Statement For Year Ending Dec 31, 2020 Jan 20 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov 20 Dec 20 Annual Total Sales Cost of goods sold: Gross Profit Total Operating Exp Net Operating Income Hedron, Inc. Budgeted Balance Sheet As of Dec 31, 2020 2019 2020 assets Current Assets: Cash Accounts receivable Supplies Merchandise Inventory Plant and Equipment: Buildings and Equipment Accumulated Depreciation otal assets Liabilities and Equity Accounts Payable Notes Payable Capital stock Retained earnings Total liabilities and equity Sales Budget For Year Ending Dec 31, 2020 Nov '19 11,250 10.00 112,500 $ Budged unit sales Selling price per unit Total Sales Dec '19 11,600 10.00 $ 116.000 Jan 20 10,000 10.00 $ 100.000 Feb 20 11,400 10.00 $ 114.000 $ Mar '20 12,600 10.00 $ 126.000 Jan 21 11,000 Apr 20 15,000 10.00 $ 150.000 $ May 20 18,000 10.00 180.000 Jun 20 22,000 10.00 220.000 $ Jul 20 18,000 10.00 180.000 Aug 20 18,000 10.00 $ 180.000 Sep 20 17,000 11.25 $ 191.250 Oct 20 16,500 11.25 $ 185.625 S Nov 20 12,000 11.25 $ 135.000 Dec 20 Annual Total 12,500 205,850 11.25 140.625$ 2. 131.000 Cash Sales % Credit Sales % 30% 70% 37,800 $ 40,500 $ Cash Sales Credit Sales Total Sales 33,750 $ 78,750 F 112,500 $ 34,800 $ 81,200 116.000 $ 30,000 $ 70,000 100.000 $ 34,200 $ 79,800 114.000 88,200 F 45,000 $ 105.000 150.000 $ 54,000 $ 126,000 180.000 66,000 $ 154.000 220.000 $ 54,000 $ 126.000 180.000 54,000 $ 126,000 180.000 $ 57,375 Fs 133.875 191.250 $ 55,688 FS 129,938 185.625 94,500 42, 188$ 98,438 140.625 $ 639.300 1,491,700 2.131.000 126.000 135.000 Current month A/R Collections 1 month prior AVR Collections 2 months prior A/R Collections Uncollectible 30% 50% 16% 4% Hedron, Inc. Cash Collections Budget For Year Ending Dec 31, 2020 Jan '20 Feb 20 Mar 20 Apr 120 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov '20 Dec 20 Annual Total Current month cash Sales Current month A/R Collections 1 month prior AVR Collections 2 months prior A/R Collections Total cash collections Bad Debt Expense Desired ending inventory % Hedron, Inc. Purchase 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 Oct 20 Nov 20 Dec '20 Summary Budged unit sales Add desired ending inventory Total unit needs Less Beginning Inventory Required Unit Purchases Cost per unit Cost of Purchases % Paid in Month of Purchase % Paid in Month after Purchase Hedron, Inc. Schedule of Budgeted Cash Disbursements for Merchandise Purchases For Year Ending Dec 31, 2020 Jan 120 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov 20 Dec 20 Annual Total Current purchases A/P Payments 1 month prior A/P Payments Cash disbursements for merchandise purchases Fixed Operating expenses: Salary and Wage Exp Rent Exp Supplies Exp Gen Admin Fixed Exp Gen & Admin Depreciation Exp Variable Operating Expenses: Sales Commission Exp % of Revenue Gen & Admin Variable Exp % of Revenue Hedron, Inc. Operating Expense 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 Oct 20 Nov 20 Dec 20 Annual Total Salary and Wage Exp Sales Commission Exp Rent Exp Gen & Admin Variable Exp Supplies Exp Gen & Admin Fixed Exp Gen & Admin Depreciation Exp Bad Debt Exp Total Operating Exp Less: Depreciation Exp (noncash) Less: Bad Debt Exp (noncash) Cash Disbursements for Operating Expenses Note payment Equipment - January Note payment Equipment- February Dividends - July Minimum Monthly Cash Budget Hedron, Inc. Cash 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 Oct 20 Nov 20 Dec 20 Summary Jan 1st Balance Add collections from customers Total cash available Cash balance, beginning Add collections from customers Total cash available Less disbursements: Cash disbursements for merchandise purchases Cash Disbursements for Operating Expenses Note Payments - Equipment Total cash disbursements Excess of receipts over disbursements Financing: Borrowing-note Repayments-note Cash balance, ending Dividends Hedron, Inc. Inventory Data COGS Calculation using FIFO Cost Assumption Beg Inventory Purchases Beg Inventory Units Purchases Units COGS $ COGS Units Ending Inventory Ending Inventory $ Units Jan Feb Mar Apr May May Jun Ju Aud AUS Sep Oct Nov Dec Hedron, Inc. Budgeted Income Statement For Year Ending Dec 31, 2020 Jan 20 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov 20 Dec 20 Annual Total Sales Cost of goods sold: Gross Profit Total Operating Exp Net Operating Income Hedron, Inc. Budgeted Balance Sheet As of Dec 31, 2020 2019 2020 assets Current Assets: Cash Accounts receivable Supplies Merchandise Inventory Plant and Equipment: Buildings and Equipment Accumulated Depreciation otal assets Liabilities and Equity Accounts Payable Notes Payable Capital stock Retained earnings Total liabilities and equity
