Question

prepare cash budget, income statement and balance sheet

December 31, 2017 Accounts Receivable Inventory 112.500 57.000 12.000 Account Payable Working Capital Line Note Payable Prop, Plant Equip Acum Depr 200,000 300,000 100,000 300,000 Common Stock Retained Earnings Net PPBE $ Total Assets 0 .500 $ 1.500 You are the Business Manager of Garden Sales, Inc and the bank has asked you to prepare a cash and earnings budget forecast and Pro-Forma Balance Sheet and Income Statement for the next quarter. After your discussion with he various departments, you have come up with the following information/assumptions: - - November 2017 and December 2017 sales were $100,000, each month Sales for the following three months will decrease $3,000 each month, beginning January through April Cost of Sales represents 60% of sales, each month. Cash Operating Expenses are 15% of sales, exclusive of Depreciation expense Depreciation Expense is $10,000 per month and prepaid rent is amortized at $2,000 per month of the month's sales, 25% is collected as cash with the remaining placed on customer's account. Customer's accounts are usually paid off over a 2-month period (50% each month) beginning the month following the sale The CFO just informed you that one customer has filed bankruptcy therefore, $15,000 from Nov A/R Sales will be written of in Jan to Bad Debt Exp. The company purchases 100% of the next month's cost of sales. They pay for 60%, in cash, in the month they purchase and pay the remaining 40% in the following month. In March, the company is planning on replacing an outdated machine. The new machine will cost $50,000. The old machine originally cost $25,000, with a Net Book Value of $7,500 and will be sold for $10,000 The Company does not pay or accrue for taxes until the end of December. The Company plans to pay a cash dividend of $20,000 at the end of March. Assume all operating expenses incurred during the month are paid, in cash, during the same month incurred The Note Payable requires a principal payment of $5,000, plus interest of $500, at the end of March. For this note, no interest is Accrued/Expense until interest is paid The minimum cash month end balance required according to the bank agreement will $80,000 for any given month beginning Jan 2018. A working capital line of credit is available, up to $100,000, and if needed, money is taken out at the beginning of the month. Interest is 1.5% per month. Interest in not compounded Interest is paid on the working capital line when principal payments are paid on the debt; at the end of any given month, Interest is accrued on the working capital line effective the beginning of the month when money is received Any excess cash estimated at the end of the month will be used to pay the working capital line balance and associated interest at the end of the month. Prepare a detailed Cash Budget/Forecast for each month of Jan, Feb and March, 2018 Prepare an Income Statement for the 3-month period ending March 2018 (good form) Prepare a Balance Sheet as of March 31, 2018 (good form) Check Figures for 03/31 Assignment must be turned in as HAND WRITTEN document. Total Interest Exp: $992.34 Total Assets: $1,054,800.00

Answer 1

Garden Sales Inc. Sales Budget ReqA Cash sales Sales on account Total budgeted sales Jan 24,250.00 72,750.00 97,000.00 Feb 23,500.00 70,500.00 94,000.00 Mar 22,750.00 68,250.00 91,000.00 Quarter 70,500.00 211,500.00 282,000.00 Calculation Jan =97,000*25% =97,000*75% =100,000-3,000 Feb =94,000*25% =94,000*75% =97,000-3,000 Mar =91,000*25% =91,000*75% =94,000-3,000 Calculation Jan Feb Mar Jan 24,250.00 Feb 23,500.00 Mar 22,750.00 Quarter 70,500.00 Schedule of cash receipts ReqB Cash sales Collections from AR Accounts Receivable-Nov sales Accounts Receivable-Dec sales Jan Sales Feb Sales Total collections 22,500.00 37,500.00 =(100,000*75%*50%)-15,000 =100,000*75% 50% 37,500.00 36,375.00 22,500.00 75,000.00 72,750.00 35,250.00 276,000.00 =100,000*75% 50% =72,750*50% 36,375.00 35,250.00 94,375.00 =72,750*50% =70,500*50% 84,250.00 97,375.00 Jan Inventory Purchase budget ReqC Beginning Inventory Budgeted Purchases Less Budgeted cost of sales Ending Inventory Jan 57,000.00 56,400.00 (58,200.00) 55,200.00 Feb Mar Quarter 55,200.00 53,400.00 57,000.00 54,600.00 52,800.00 163,800.00 (56,400.00) (54,600.00) (169,200.00) 53,400.00 51,600.00 51,600.00 Feb End Inv of Jan =91,000*60% =94,000*60% Mar End Inv of Feb =191,000-3,000) *60% =91,000*60% =94,000*60% =97,000*60% Feb 32,760.00 Mar 31,680.00 Quarter 98,280.00 Jan =56,400*60% Feb =54,600*60% Mar =52,800*60% Schedule of cash payments budget for Inv Purchases RegD Jan Cash Purchases 33,840.00 Credit Purchases Accounts Payable 22,800.00 Credit Purchases Total disbursements for pur 56,640.00 =56,400*40% 22,800.00 44,400.00 165,480.00 22,560.00 55,320.00 =54,600*40% 21,840.00 53,520.00

Jan Feb Mar Jan 60,000.00 Feb 80,000.00 Mar 100,806.80 Quarter 60,000.00 84,250.00 97,375.00 94,375.00 10,000.00 205,181.80 276,000.00 10,000.00 346,000.00 144,250.00 177,375.00 56,640.00 14,550.00 55,320.00 14,100.00 =97,000*15% =94,000*15% =91,000*15% Cash budget Req 1 Cash balance, beginning Add Cash collections From Customer sales From sale of machine Total cash available Less cash payments For Inventory purchases For Operating expenses Purchase of new machine Dividends Repayment of notes Interest expense Total budgeted payments Payments minus receipts Financing Borrowings Repayments Total Financing Cash balance, ending 53,520.00 13,650.00 50,000.00 20,000.00 5,000.00 500.00 142,670.00 62,511.80 165,480.00 42,300.00 50,000.00 20,000.00 5,000.00 708.20 283,488.20 62,511.80 =6,940*1.5%*2 71,190.00 73,060.00 208.20 69,628.20 107,746.80 6,940.00 17,488.20 =80,000-73,060 =80,000-62,511.80 24,428.20 (6,940.00) 17,488.20 80,000.00 (6,940.00) (6,940.00) 100,806.80 6,940.00 80,000.00 17,488.20 80,000.00 Jan Feb Mar Jan 97,000.00 58,200.00 38,800.00 Feb 94,000.00 56,400.00 37,600.00 Mar 91,000.00 54,600.00 36,400.00 Quarter 282,000.00 169,200.00 112,800.00 14,100.00 2,000.00 13,650.00 2,000.00 Income Statement Req2 Sales Cost of goods sold Gross Profit Operating Expenses Operating Expenses Rent Expense Bad Debt expense Depreciation Expenses Total Operating Expenses Operating income Other expenses/losses (income/gains) Gain on sale of machine Interest expense Total Other items Net income 14,550.00 2,000.00 15,000.00 10,000.00 41,550.00 (2,750.00) 42,300.00 6,000.00 15,000.00 30,000.00 93,300.00 19,500.00 10,000.00 26,100.00 11,500.00 10,000.00 25,650.00 10,750.00 =10,000-7,500 =500+(17,488.20*1.5%) =6,940*1.5% =6,940*1.5% 104.10 104.10 (2,854.10) 104.10 104.10 11,395.90 (2,500.00) 762.32 (1,737.68) 12,487.68 (2,500.00) 970.52 (1,529.48) 21,029.48

Balance Sheet Req3 Assets Current Assets Cash Inventory Accounts Receivable Prepaid Rent Total Current Assets 80,000.00 51,600.00 103,500.00 6,000.00 =(70,500*50%) +68,250 =12,000-6,000 241,100.00 Fixed Assets PPE Accmulated Depreciation Net Fixed Assets Total Assets 925,000.00 (112,500.00) =900,000+50,000-25,000 =100,000-(25,000-7,500)+30,000 812,500.00 1,053,600.00 =52,800*40% Liabilities and Equity Liabilities Accounts Payable Working Capital Line Notes Payable Interest Payable Total Liabilities 21,120.00 17,488.20 45,000.00 262.32 =50,000-5,000 =17,488.20*1.5% 83,870.52 Equity Common Stock Retained Earnings Total Equity Total Liabilities & Equity 200,000.00 769,729.48 =768,700+21,029.48-20,000 969,729.48 1,053,600.00