I'm working on a practice problem regarding Master Budget. I was able to complete almost all the schedule except for the Cash Budget (Part 4). I couldn't figure out why my calculation for repayment and total financing were wrong in the month of March. I would like a detailed explanation as to why it was wrong and how I could come up with the correct amount for repayment and total financing in the month of March. Attached is my spreadsheet. Highlighted boxes are the areas that I got wrong. Thank you.

Solution Given Cash Sale Credit Sale 1. Schedule of Expected Cash Collections 40% 60% Jan Cash sales Cash collections from credit sales : December January Febuary March Total cash collections ** Feb Mar Ist Qtr. $ 30,600.00 $ 35,480.00 $ 36,880.00 $ 102,960.00 $ 39360.00 0.00 0.00 69,960.00 $ 0.00 45900.00 0.00 81,380.00 $ 0.00 0.00 53220.00 90,100.00 $ 39360.00 45900.00 53220.00 241,440.00 Mar Ist Qtr. Working Notes Sales Budget For the Quartyer ended 31st March XXXX Jan Sales on account Cash sales Total sales 45,900 30,600 76,500 2 a) Feb 53,220 35,480 88,700 55,320 36,880 92,200 154,440 102,960 257,400 Feb 62,090 12,908 74,998 12,418 62,580 Mar 64,540 8,470 73,010 12,908 60,102 Ist Qtr. 180,180 8,470 188,650 10,710 177,940 Feb Mar Ist Qtr. - $ 41443.50 15,645.00 0.00 57,088.50 $ - $ 0.00 46,935.00 15,025.50 61,960.50 $ 32,280.00 55258.00 62580.00 15025.50 165,143.50 Mar Ist Qtr. Picanuy Corporation Merchandise Purchase Budget For the Quartyer ended 31st March XXXX Jan 53,550 12,418 65,968 10,710 55,258 COGS Add: Closing Inventory Total Required Inventory Less : Opening Inventory Purchases Value 2 b) Schedule of Expected Cash DisbursementsMerchandise Purchases Jan Cash payments for purchases December Purchases January Purchases February Purchases March Purchases Total Disbursements $ $ 3 32,280.00 $ 13,814.50 0.00 0.00 46,094.50 $ Picanuy Corporation Expected Cash Disbursements For the Quartyer ended 31st March XXXX Jan Feb Selling and Administrative Expenses : Commision Rent Other Expenses Total Selling & Admin. Expense 4 $ 16,510.00 $ 2000.00 6120.00 $ 24,630.00 $ 16,510.00 $ 2000.00 7096.00 25,606.00 $ 16,510.00 $ 49,530.00 6000.00 2000.00 20592.00 7376.00 25,886.00 $ 76,122.00 Picanuy Corporation Cash Budget for thequarter ended 31st March 2012 Jan Opening Balance of Cash Receipt from Debtors Total Cash Available Less: Cash disbursement For Inventory For Operating Expenses For Equipment Total Cash Disbursement $ Feb Mar Ist Qtr. 6,000.00 $ 69960.00 75960.00 5,445.50 $ 81380.00 86825.50 5,141.00 $ 90100.00 95241.00 6,000.00 241440.00 247440.00 46094.50 24630.00 3790.00 74514.50 57088.50 25606.00 8990.00 91684.50 61960.50 25886.00 0.00 87846.50 165143.50 76122.00 12780.00 254045.50 Excess (Deficiency) of Cash Financing : Borrowings Repayments Interest 1,445.50 (4,859.00) 7,394.50 (6,605.50) 4000.00 0.00 0.00 4000.00 10000.00 0.00 0.00 10000.00 0.00 -2074.5 -320.00 -2394.50 14000.00 -2074.50 -320.00 11605.50 5,445.500 $ 5,141.000 $ 5,000.000 $ Total financing Closing Cash Balance 5 $ Picanuy Corporation Budgeted Income Statement for theFirst Quarter of XXXX Sales revenue Less: Cost of goods sold Gross margin Operating Expenses : Commision Rent Other Expenses Interest Depreciation Total selling and administrative expenses $ $ $ 257,400.00 180,180.00 77,220.00 $ 76,942.00 $ 278.00 $ 68,790.00 $ $ 124,980.00 - $ $ 124,980.00 193,770.00 $ 45,076.50 11925.50 $ $ $ $ $ 49,530.00 6,000.00 20,592.00 320.00 500.00 Income for the quarter 6 Picanuy Corporation Budgeted Balance Sheet 31.03.XXXX Current Assets Cash Accounts receivable Inventory Total Current Assets Property, Plant & Equipment Buildings and equipment (net of accumulated depreciation) Land Total assets Current Liabilities : Accounts payable Short Term Borrowing Total Current Liabilities Shareholder's Equity Common stock Retained earnings Total Shareholder's Equity Total liabilities and stockholders' equity $ $ $ 5,000.00 55,320.00 8,470.00 $ $ 57,002.00 $ $ 136,768.00 193,770.00 100,000.00 36768.00 5,000.000 Practice Problem The following data relate to the operations of Randall Corporation, a wholesale distributor of consumer goods: Current Assets as of December 31: Cash Account Receivable Inventory 6,000 36,000 9,800 110,885 32,550 100,000 30,135 Buildings and equipment, net Accounts payable Capital Stock Retained Earnings a. The gross margin is 30% of sales. (In other words, cost of goods sold is 70% of sales.) b. Actual and budgeted sales data are as follows: December (actual) January February March April 60,000 70,000 80,000 85,000 55,000 c. Sales are 40% for cash and 60% on credit. Credit sales are collected in the month following sale. The accounts receivable at December 31 are the result of December credit sales. d. Each month's ending inventory should equal 20% of the following month's budgeted cost of goods sold. e. One-quarter of a month's inventory purchases is paid for in the month of purchase; the other three-quarters is paid for in the following month. The accounts payable at December 31 are the result of December purchases of inventory. f. Monthly expenses are as follows: commissions, $12,000; rent, $1,800; other expenses (excluding depreciation), 8% of sales. Assume that these expenses are paid monthly. Depreciation is $2,400 for the quarter and includes depreciation on new assets acquired during the quarter. g. Equipment will be acquired for cash: $3,000 in January and $8,000 in February. h. Management would like to maintain a minimum cash balance of $5,000 at the end of each month. The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $50,000. The interest rate on these loans is 1% per month, and for simplicity, we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter. Solution Given Part 1 Cash Sale Credit Sale Complete the following schedule: Cash sales Cash collections from credit sales : Total cash collections Part 2a 40% 60% Randall Corporation Schedule of Expected Cash Collections Jan Feb $ $ $ Complete the following: 28,000 $ 36,000 $ 64,000 $ 32,000 $ 42,000 $ 74,000 $ Ist Qtr. 34,000 $ 48,000 $ 82,000 $ 94,000 126,000 220,000 Randall Corporation Merchandise Purchase Budget Jan Budgeted Cost of Good Sold Add: Closing Inventory Total Needs Less : Opening Inventory Purchases Value Feb 56,000 11,900 67,900 11,200 56,700 49,000 11,200 60,200 9,800 50,400 Mar 59,500 7,700 67,200 11,900 55,300 Ist Qtr. 164,500 7,700 172,200 9,800 162,400 Mar Ist Qtr. Schedule of Expected Cash DisbursementsMerchandise Purchases Part 2b Jan December Purchases January Purchases February Purchases March Purchases Total Disbursements Part 3 Mar Complete the following: $ $ $ $ $ Feb 32,550 12,600 45,150 $ $ $ $ $ 37,800 14,175 51,975 Randall Corporation Expected Cash Disbursements $ $ $ $ $ 42,525 13,825 56,350 $ $ $ $ $ 32,550 50,400 56,700 13,825 153,475 Budgeted COGS for January = 70,000 sales x 70% = $49,000 Added desired ending inventory for January = 80,000 sales x 70% x 20% = 11,200 Jan 8% of Sales Part 4 Selling and Administrative Expenses : Commission $ Rent $ Other Expenses $ Total Selling & Admin. Expense $ Complete the following: Feb 12,000 1,800 5,600 $ 19,400 $ ### ### 6,400 20,200 $ $ $ $ Ist Qtr. 12,000 1,800 6,800 20,600 $ $ $ $ 36,000 5,400 18,800 60,200 Randall Corporation Cash Budget Jan Given Info Mar Feb Mar Ist Qtr. Cash Balance, Beginning Add: Cash collections Total Cash Available Less: Cash disbursement For Inventory For Operating Expenses For Equipment Total Cash Disbursement $ $ $ 6,000 $ 64,000 $ 70,000 $ 5,450 $ 74,000 $ 79,450 $ 5,275 $ 82,000 $ 87,275 $ 6,000 220,000 226,000 $ $ $ $ 45,150 19,400 3,000 67,550 51,975 20,200 8,000 80,175 56,350 20,600 76,950 $ $ $ $ 153,475 60,200 11,000 224,675 Excess (Deficiency) of Cash Financing : Borrowings Repayments Interest $ 2,450 $ 10,325 $ 1,325 Total financing $ $ $ $ 3,000 $ 3,000 $ 6,000 ### ### 6,000 Closing Cash Balance $ 5,450 $ 5,275 $ $ $ $ $ $ $ $ $ (725) $ $ $ $ $ (9,000) (210) (9,210) $ $ $ $ 9,000 (9,000) (210) (210) 1,115 $ 1,115 I thought the total repayment in March would be ($9,000) and total interest would be $210 becase the company has borrowed $9,000 so far. The total repayment in March would be ($9,210). However, according to the textbook answer, the repayment amount and the total financing amount for March is wrong. In March, the company's repayment was ($5,000) and total interest was ($210), Therefore, the total financing was ($5,210). How was $5,000 repayment in March calculated