You are the new manager of BE. Top management made you prepare a budget for the store for July, August, and September (3rd quarter of the year). You wanna make a good impression on your superiors, so you gather the following data as of June 30, 2022.
Balance Statement Cash Accounts Receivable Inventory Net Furniture and Fixtures Total Assets Accounts Payable Owners Equity Total Liabilities and Owners' Equities 6/30/2022 $29,000 400,000 192,000 171,000 $792,000 237,600 554,400 $792,000 May Recent and Projected Sales: $500,000 June 700.000 July 800,000 August 700.000 September 600,000 October 400,000 a. Credit sales are 100% of total sales. b. Credit accounts are collected 50% in the month of the sale, 40% in the month following the sale, and 10% in the subsequent month. Assume that bad debts are negligible and can be ignored. c. The accounts receivable on June 30 are the result of the credit sales for May and June: (May: $500,000 * .1 = 50,000) + (June: $700,000 * 50 = 350,000) = $400,000. d. The average gross profit on sales is 40% (i.e., cost of goods sold is 60% of sales). e. The policy is to acquire enough inventory each month to equal 40% of the following month's projected cost of goods sold. INME> M - v command option tion + anunting 202 CHEGO f. 60% of a month's inventory purchases is paid a month's inventory purchases is paid for in the month of purchase and 40% u is paid for is paid for in the following month $177,600 of the $237,600 balance in accounts payable at May 31 are the result of May purchases of inventory. 8. Salaries, wages, and commissions average 20% of sales: all other variable expenses are 5% of sales. xed expenses for rent, property taxes, and miscellaneous payroll and other items are $50,000 monthly. Assume that these variable and fixed expenses require cash disbursements each month. Depreciation is $2,500 monthly. 1. In July, $60,000 is going to be disbursed for fixtures acquired and recorded in Turniture and fixtures in June. The June 30 balance of accounts payable includes this amount (i.e., 177,600 + 60,000 = 237,600). 3. Assume that a minimum cash balance of $25,000 is to be maintained. The company has an agreement with a local bank that allows th 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 the interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest on the portion of the principal repaid) at the end of any future month when cash is available. Required: Prepare a Master Budget (comprised of the following budgets) on Excel. Hand in a paper copy and e-mail me the Excel document: 1. Prepare a Sales Budget: July August September Quarter 3 Cash Sales Credit Sales Total Sales 800,000 $800,000 2. Prepare a Cash Collections from Sales Budget: May Credit Sales June Credit Sales July Credit Sales August Credit Sales Sept Credit Sales Total Collections July August September Quarter 3 $50,000 280,000 320,000 300,000 face.PNO 3. Prepare a Purchases Budget: July August Septeember Quarter 3 $480,000 168,000 COGS Plus: EI (FG) Total Needs Minus: BI (FG) Required Purchases 4. Prepare a Cash Disbursements for Purchases Budget: July August September Quarter 3 177,600 June's Purchases July's Purchases August's Purchases Sept's Purchases Total Disbursements 5. Prepare an Operating Expenses Budget: 1 July August September Quarter 3 Salaries and Comm $160,000 Other VC Depreciation Fixed Expenses Total Expenses face.PNG 6. Prepare a Cash Disbursements for Operating Expenses Budget: July August September Quarter 3 Salaries and Comm $160,000 Other VC Fixed Expenses Total Disbursements 7. Prepare a Cash Budget: July August September Quarter 3 $29,000 730,000 Beginning Cash Balance Cash Receipts: From Sales Total Cash Available Cash Disbursements: For Purchases For Fixtures For Operating Expenses Total Cash Disbursements Net Cash Before Financing Financing: Borrowing Repayment Interest Expense Total Financing Ending Cash Balance -560 206,640 face.PNG 8. Prepare a Budgeted Income Statement: $2,100,000 Budgeted Income Statement July 1, 2022 - September 31, 2022 Sales COGS (60%) Gross Margin (40%) Operating Expenses Net Operating Income Interest Expense Net Income 9. Prepare a Budgeted Balance Sheet: $206,640 Assets: Cash Accounts Receivable Merchandise Inventory Furniture and Fixtures Depreciation Total Assets $836,140 Liabilities & Stockholders' Equity: Accounts Payable Retained Earnings: Beginning Retained Earnings + Net Income Total Liabilities and SE 554,400
