PREPARE A MASTER BUDGET FOR GMW Company.

Use data below to create Master Budget file for GMW.

You are the accountant for GMW Company and have been asked to prepare a budget for the last quarter of 2022. You will prepare a master budget and budgeted financial statements for GMW Company. You will design this workbook yourself. Include the following: Assumptions Page or section Sales Budget Schedule of Cash Collections Merchandise Purchases Budget Schedule of Cash Payments Selling Expense Budget General and Administrative Expenses Budget Capital Expenditures Budget Depreciation Schedule Cash Budget Budgeted Income Statement Budgeted Balance Sheet Specific requirements are as follows: 1. You must create a template for your project that could be used in future quarters. 2. The user will be someone other than you, so plan accordingly. Keep in mind that the user will not have the Excel skills that you do. Make it user-friendly. 3. The template must include an assumptions page or area. Data must only be entered in one place. Since this will be used again, changing amounts should be easily accomplished. 4. You may design your template however you wish. Use good form for the financial statements and proper formatting of numbers. You are not required to use dollar signs on the budgets and schedules. Numbers should be formatted to zero decimal places, with comma format. 5. Give the user enough information so they know where to enter their data and if it is in units or dollars or percentages. 6. Use the template as dummy data to test your formulas and functionality. 7. Enter your name and ACC 202 Master Budget in the right side of a header. Make sure the header shows on all worksheets. 8. Use a function to show the name of the worksheet tab in the left side of a header. Make sure the tab names show on all worksheets. 9. In the left side of footer, use a function to show the file name on all worksheets. 10. Using your template, create a workbook for the last quarter of 2022 using the information provided. 11. Use the data provided to prepare a budget. 12. Include your name in the file name and email to your instructor.

GMW is the company for whom we are preparing budgets and budgeted financial statements for the last quarter of 2022. They have given us the following information for the coming quarter. Sales are anticipated to be (units): October 6,500 November 8,500 December 12,500 January 9,000 Selling price will be $75 per unit. Collections of sales are: Cash sales are 25% of total sales for month. Credit sales are collected 60% in the month after the sale and the remainder in the second month after the sale. The Accounts Receivable beginning balance is expected to be received 60% in October and 40% in November. Purchase cost is $50 per unit. Ending inventory is anticipated to be 25% of the next months sales in units. Payments for the inventory purchases are expected to be paid in the following manner: 40% in the month after purchase and the remainder in the second month after purchase. The accounts payable beginning balance will be collected 40% in October and 60% in November. Other payables are expected to be: Sales Commissions at 2% of sales, Sales Salaries at $10,000 per month, and advertising at $2,000 per month. General and administrative salaries are $12,500 per month, rent on the office space is $13,500 per month, and clerical wages are $15,000 per month. Office supplies are anticipated to be $375 per month. The company plans to purchase for cash: new computer equipment in October for $90,000; a new truck in November for $126,000; and new software in December for $3,600. Depreciation begins that month. The prepaid insurance will expire in December and is not to be renewed. The current portion of Long Term Debt will be paid in December. Cash balance is required to be $145,000 per month. The interest rate is 10% per year on bank borrowings. Interest must be paid monthly on all bank borrowings. You cannot pay back a loan if you need to borrow. If you need to borrow to establish the minimum cash balance, that loan is a line-of- credit loan. The beginning note payable to the bank is not a line of credit requiring a minimum cash balance. The beginning cash at $45,000 is correct. Depreciation is calculated using the straight-line method using the following: 5 year life on all but the computer software, which is 3 year life. You do not have to re-set up Income and Payroll Taxes Payable as you are paying them off this final quarter of the year. Round all numbers up to the nearest dollar, comma format.