BUS A20200 MASTER BUDGET PROJECT SECOND SUMMER SESSION 2021 Data: Chapa Company, a sporting goods merchandising firm, prepares its master budget on a quarterly basis. The following data have been assembled to assist in the preparation of the master budget for the third quarter of 2021. The firm is particularly interested in its cash position over the next few months since it is considering a major loan to expand its business. On June 30, 2021, the company prepared the following balance sheet: a. $ 75,000 180,000 140,000 350,000 $745.000 Assets: Cash Accounts Receivable Inventory Land and Equipment $450,000 Less: Accumulated Depreciation (100,000) Total Assets Liabilities and Stockholders' Equity: Accounts Payable Notes Payable Interest Payable Capital Stock Retained Earnings Total Liabilities and Stockholders' Equity $126,000 0 400,000 219,000 $745,000 b. Actual sales for June 2021 were $600,000. Budgeted sales for July 2021 through October 2021 are as follows: July $700,000 August 800,000 September 850,000 October 550,000 c. d. e. f. Sales are 40% for cash and 60% on credit. 50% of each month's credit sales are collected in the month of sale and the remaining 50% is collected in the month after sale. Therefore, the balance of $180,000 for Accounts Receivable on the June 30, 2021 balance sheet is $600,000 June sales x 60% credit sales x 50% collected in month after sale. The company's budgeted cost of goods sold is 70% of budgeted sales. Monthly expenses are budgeted as follows: salaries and wages, $20,000 per month plus 15% of sales; shipping, 3% of sales, advertising, $10,000 per month; depreciation, $4,000 per month. At the end of each month, management desires inventory on hand to be equal to 20% of the following month's sales, stated at cost. Inventory purchases are all on credit. 70% of each month's purchases are paid for in the month of purchase and the remaining 30% is paid for in the month after purchase. Land purchases during the quarter are expected to be as follows: July, $135,000; September, $117,492 Dividends totaling $25,000 are expected to be declared and paid in September. The company must maintain a minimum cash balance of $25,000. An open line of credit is available at a local bank. All borrowing is done at the beginning of the month and all repayments of principal are made at the end of a month. Interest is repaid only at the time of repayment of principal and is accrued between the time the borrowing is made and the principal is repaid. The annual interest rate 12% and interest is calculated to the nearest whole month (e.g., 1912, 2/12, etc.) and rounded to the nearest penny. h. i. j REQUIRED: Using the data from the first page, complete the following schedules and statements Chapa Company for the third quarter of 2021 Your presentation of the schedules must be professional with appropriate headings, spacing, spelling, and underlining. In addition, all numbers in the same column must be aligned as discussed in class (i.e., points will be deducted if the numbers do not align). You may find it beneficial to use a spreadsheet program such as Excel to enter the data since it is comparatively easy to align the numbers using these programs. You must submit your project as a PDF file. When creating your PDF file, you may include more than one schedule on a page. However, make sure that no schedule appears across two pages. For example, if you find that schedules 1, 2, 3, 4 and only part of schedule 5 appear on your first page of your PDF file and the rest of schedule 5 appears on the next page, go back to Excel and use a page break after schedule 4 or space down to force schedule 5 to appear in whole on the next page as shown in class. As you type in the data below for July, make sure you understand how each number is generated or calculated. The heading "30" in the last column stands for Third Quarter and you should put the appropriate number in that column as discussed in class. 1. Schedule of Expected Cash Collections: AUG SEP 3Q JUL $280,000 Collection of Cash Sales Credit Sales: Collected Month of Sale Collected Month after Sale Total Collections 210,000 180,000 $670,000 2. Inventory Purchases Budget: AUG SEP 30 Budgeted Cost of Goods Sold Add Desired Ending Inventory Total Inventory Needs Deduct Beginning Inventory Total Purchases JUL $490,000 112,000 $602,000 140,000 $462,000 3. Schedule of Cash Disbursements - Inventory Purchases: JUL AUG SEP 3Q Inventory Purchases: Paid Month of Purchase Paid Month after Purchase Total Cash Disbursements $323,400 126,000 $449,400 4. Schedule of Cash Disbursements - Operating Expenses: JUL AUG SEP 30 Operating Expenses: Salaries and Wages Shipping Advertising Total Cash Disbursements $125,000 21,000 10,000 $156,000