Need the yellow square formulas please!!! I can't seem to figure out the rest.
- 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.
INFORMATION FOR HEDRON, INC. BUDGET PROJECT 1. 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. 2. As of 11/01/2019, each lawn chair costs Hedron $4 per unit. Hedron sells each chair for $10 per unit. 3. The estimated sales (in units) are as follows: Nov 2019 11,250 Dec 2019 11,600 Jan. 2020 10,000 Feb. 2020 11,400 Mar. 2020 12,000 Apr. 2020 15,600 May 2020 | 18,000 June 2020 22,000 July 2020 18,000 4. Per an existing contract, the cost of each chair is scheduled to increase by 5% on March 1, 2020. In addition, because of increasing costs of plastic webbing, the cost is anticipated to increase by an additional 5% on May 1, 2020. To offset these increases, the company plans to raise the sales price to $11.25 per unit beginning May 1, 2020. The sales forecast (i.e., estimated sales in units) takes this price increase into account. 5. Thirty percent of any month's sales are for cash, and the remaining 70% are on credit. Thirty percent of the credit sales are collected in the month of sale, 50% are collected in the following month, and 16% are collected in the second month after the sale. The remaining receivables are deemed uncollectible. Bad debts are written off in the month the debt is deemed uncollectible (e.g. if the sale is made in January and is not collected by the end of March, it is written off in March.) No accrual for estimated bad debts is made in the month of sale. 6. The firm's policy regarding inventory is to stock (i.e. have in ending inventory) 40% of the forecasted demand in units (i.e., estimated sales) for the next month. Hedron uses the first-in, first-out (FIFO) method in accounting for inventories. 7. Forty percent of the inventory purchases are paid for in the month of purchase and the remaining 60% are paid in the following month (i.e. all the previous month's Accounts Payable are paid off by the end of any month.) 8. Per a prior contract, a cash payment of $50,000 for equipment previously purchased is due in January. Another payment of $30,000 is due in February. Depreciation on the equipment previously purchased is included in the overhead cost detailed in item 11 below. Also, dividends of $12,000 are to be paid in March. 12. See below the other Balance Sheet accounts with their balances as of December 31, 2019: Supplies...... .$ 2,000 Property, Plant and Equipment.......... 970,000 Accumulated Depreciation.. 526,475 Common Stock....... 200,000 Retained Earnings......... 322,811 Purchase Budget For the 6 mos ending June '20 Nov '19 Dec '19 Jan '20 Feb '20 Mar '20 Apr '20 May'20 June '20 =B8 =C8 =D8 =E8 =F8 =G8 =H8 =18 6 mos total =SUM(D45, E45,F45, G45,H45,145) Budged unit sales Add desired ending inventory Total needs Less Beginning Inventory Required Purchases =B45+B46 =C45+C46 =D45+D46 =E45+E46 =F45+F46 =G45+G46 =H45+H46 =145+146 I=SUM(D47,E47, F47,647,H47,147) =C47-C48 =D47-D48 1=E47-E48 =F47-F48 =G47-G48 =H47-H48 =147-148 =SUM(C49,D49,E49,F49,649,H49, Cost per unit Purchases 4 =D49*D51 4 =E49*E51 14.2 =F49*F51 14.2 =G49*G51 14.41 =H49*H51 4.41 =149*151 =C49*C51 % Paid in Month of Purchase % Paid in Month after Purchase 0.4 10.6 = A40 Schedule of Budgeted Cash Disbursements for Merchandise Purchases =A42 Jan '20 Feb '20 Mar '20 Apr '20 May'20 June '20 Cash purchases 1 month prior A/P Collections Cash disbursements for merchandise purch. 6 mos total =SUM(B63, C63,D63, E63,F63,663) =SUM(B64,C64,D64,E64,F64,664) =H63+H64 =B63+B64 =C63+C64 =D63+D64 =E63+E64 =F63+F64 =G63+G64 Variable Operating Expenses: Sales Commissions, % of Revenue Other Variable Cash Expenses, % of 0.07 0.06 Fixed Operating expenses: =A83 =A85 =A87 =A88 =A89 3000 8000 2000 24000 24000 =A58 Operating Expense Budget =A60 Jan '20 Feb '20 Mar '20 Apr '20 May'20 June '20 6 mos total Salaries and Wages Sales Commissions Rent Other Variable Cash Expenses Supplies Expense Other - Overhead Other - Depreciation Bad Debt Expense Total operating expenses Depreciation and noncash items Bad Debt Expense Cash disbursements for operating expenses =SUM(B83:B90) =B89 =B90 =SUM(B91:B93) I =SUM(C83:C90) =C89 I=C90 =SUM(C91:C93) =SUM(D83:D90) =D89 =D90 I=SUM(D91:093) =SUM(E83:E90) =E89 =E90 =SUM(E91:E93) =SUM(F83:F90) =F89 =F90 =SUM(F91:F93) =SUM(G83:G90) =G89 =G90 I=SUM(G91:G93) =SUM(H83:H90) =H89 =H90 =SUM(H91:H93) Equipment payment - January Equipment payment - February Dividends - March Minimum Monthly Cash Budget 50000 30000 12000 15000 =A78 Cash Budget =A80 Jan '20 Feb '20 Mar '20 Apr '20 May '20 June 20 6 mos total =D94*4 =194 =195 = 196 =197 =198 =B106+B107 =C106+C107 =D 106+D 107 =E106+E107 =F106+F107 =G106+G107 Cash balance, beginning Add collections from customers Total cash available Less disbursements: =A65 =A94 Equipment purchases Dividends Total cash disbursements Excess of receipts over disbursements Financing: Borrowing-note Repayments-note Total financing Cash balance, ending 39000 n/a =B117 =B115-B119 20000 n/a =C117 =C115-C119 3000 n/a =D117 I=D115-D119 n/a - 15000 =E118 =E115-E119 n/a -32000 =F118 =F115-F119 n/a - 15000 =G118 I=G115-G119 =SUM(B117,C117,D117) =SUM(E118,F118, G118) n/a =H115 =A101 Budgeted Income Statement =A103 Jan '20 Feb '20 Mar '20 Apr '20 May'20 June '20 Total 40000 45600 49440 65520 77868 97020 Sales, net Cost of goods sold: Gross margin =A91 Net Income =A123 Budgeted Balance Sheet 44012 Assets Current Assets: Cash Accounts receivable Supplies Merchandise Inventory Plant and Equipment: Buildings and Equipment Accumulated Depreciation Total assets 58121 121275 2000 31752 =SUM(B140:B143) 1050000 670475 =B145-B146 =C143+C146 53978 Liabilities and Equity Accounts payable Capital stock Retained earnings Total liabilities and equity 200000 367045 =C147-C150 =C150+C152
