Question

Excel Assignment John L Leal Company, an office supplies specialty store, prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparing the master budget for the first quarter: a. As of December 31 (the end of the prior quarter), the company's general ledger showed the following account balances: Cash $ 48,000 Accounts receivable 224,000 Inventory 60,000 Buildings and equipment (net) 370,000 Accounts payable Common stock Retained earnings $ 702,000 $ 93,000 500,000 109,000 $702,000 b. Actual sales for December and budgeted sales for the next four months are as follows: December(actual) January February March April $280,000 $400,000 $600,000 $300,000 $200,000 c. Sales are 20% for cash and 80% on credit. All payments on credit sales are collected in the month following sale. The accounts receivable at December 31 are a result of December credit sales. d. The company's gross margin is 40% of sales. (In other words, cost of goods sold is 60% of sales.) c. Monthly expenses are budgeted as follows: salaries and wages, $27,000 per month: advertising, $70,000 per month; shipping, 5% of sales; other expenses, 3% of sales, Depreciation, including depreciation on new assets acquired during the quarter, will be $42,000 for the quarter. f. Each month's ending inventory should equal 25% of the following month's cost of goods sold. g. One-half of a month's inventory purchases is paid for in the month of purchase; the other half is paid in the following month. h. During February, the company will purchase a new copy machine for $1,700 cash. During March, other equipment will be purchased for cash at a cost of $84,500. i. During January, the company will declare and pay $45,000 in cash dividends. j. Management wants to maintain a minimum cash balance of $30,000. 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. 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. Create a schedule of expected cash collections, merchandise purchases budget, cash disbursements for merchandise purchases budget, and cash budget for Leal Company for January, February, and March.

Answer 1

Schedule of expected Cash collections January February March Quarter 224,000 224,000 80,000 80,000 320,000 320,000 From Accounts receivable From January Sales 20% x 400,000 80% x 400,000 From February Sales 20% x 600,000 80% x 600,000 From March Sales 20% x 300,000 120,000 120,000 480,000 480,000 60,000 540,000 60,000 1,284,000 Total 304,000 440,000 Merchandise Purhcase Budget January March Budgeted Cost of goods sold Add: Desired Ending Inventory Total Need Less: Estimated Beginning Inventory Required Purchases 240,000 90,000 330,000 60,000 February 360,000 45,000 405,000 90,000 315,000 180,000 30,000 210,000 45,000 165,000 Quarter 780,000 30,000 810,000 60,000 750,000 270,000 Working Notes: April Sales (A) Cost of goods sold (A x 60%) Ending Inventory anuary 400,000 240,000 90,000 (360,000 x 25%) February March 600,000 300,000 360,000 180,000 45,000 30,000 (180,000 x 25%) (120,000 x 25%) 200,000 120,000

Schedule of expected Cash Disbursement Schedule January February February March Quarter 93,000 93,000 135,000 135,000 135,000 135,000 For Accounts payable For January Purchases 50% x 270,000 50% x 270,000 For February Purchases 50% x 315,000 50% x 315,000 For March Purchases 50% x 165,000 157,500 157,500 157,500 157,500 28,00 82,500 240,000 20.00 82,500 760,500 Total 29,500 20.500 228,000 292,500

Cash Budget March Cash Budget January February 48,000 30,000 304,000 440,000 352,000 470,000 30,800 540,000 570,800 Quarter 48,000 1,284,000 1,332,000 228,000 27,000 70,000 20,000 12,000 292,500 27,000 70,000 30,000 18,000 1,700 240,000 27,000 70,000 15,000 9,000 84,500 760,500 81,000 210,000 65,000 39,000 86,200 45,000 1,286,700 45,300 30,000 15,300 45,000 Beginning Cash Balance Collection from sales Total Cash Available to spend (A) Less: Disbursement Payment for Inventory Salaries and Wages Advertising Shipping (Sales x 5%) Other expenses (Sales x 3%) Purchase of Equipment Dividend Total Cash Disbursement (B) Cash excess (deficiency) (C =A-B] Minimun Cash Balance [D] Cash excess (needed) (C-D] Financing Borrowings Repayments Interest Total Financing [E] Ending Cash Balance (C+E] Calculation of Interest repayment Amount Borrowed on 1st January Rate of interest Total Interest for quarter (80,000 > 1% x 3) 439,200 402,000 (50,000) 30,000 (80,000) 30,800 30,000 800 445,500 125,300 30,000 95,300 80,000 (80,000) (2,400) (82,400) 42,900 80,000 (80,000) (2,400) (2,400) 42,900 80,000 30,000 30,800 80,000 1% P.m 2,400