George and Co makes floor mats. George is preparing its budget for the second quarter of 2022. 1. Each mat sells for $22. Sales in units are as follows: February March April May June July 12,000 12,000 13,500 15,000 15,500 16,000 $264,000 264,000 297,000 330,000 341,000 352,000 In order to prevent stockouts they maintain an ending inventory at the end of each month. That inventory is 30% of the following month's sales. 2. The mats are made of a compound made of rubber and plastic which comes in a variety of colors and is made in India. George buys the compound, rolls it flat, cuts the mats and stamps its logo on each one. Each mat requires 1.5 square yards of compound. Because the compound comes from India, they require that a 20% inventory of the following months production. The compound costs $3 per square yard. Materials needed for production in July are $25,125. 3. Each mat requires 3 hours of direct labor and Jamison pays its assembly line workers $15 per hour. Variable overhead includes inexpensive cording that is attached to the mats and is included in the Variable OH application rate of $12 per direct labor hour. Fixed manufacturing overhead is applied at $8 per direct labor hour. 4. Delivery expenses are 8% of sales and sales commissions are another 4%. Fixed general and administrative are $24,000 per month and 50% of that amount is depreciation. 5. Sales are 30% cash and 70% on credit. Credit sales are collected as follows: a. 10% in the month of sale b. 50% in the month following sale c. 10% in the second month following sale 6. Payments for Purchases are made 25% in the month of purchase and 75% in the month following purchase. Purchases for March total $57,375 7. George requires that a minimum of $20,000 remain in the bank at all times. If the balance drops below $20,000 Jamison borrows from it's line of credit in increments of $10,000. It pays all borrowings back as soon as possible. There are no borrowings on the line of credit on April 1. The beginning cash balance on April 1 is $21,000. 8. George expects to purchase new equipment in April totaling $25,000 and pays a $10,000 dividend in the last month of each quarter. Requirements: Using the template provided, prepare a master budget and financial budgets for George for the months of April-June 2022, showing quarterly totals. These should include the following: a. Sales Budget b. Production Budget C. Materials purchases budget d. Direct labor Budget e. Manufacturing Overhead f. Selling, General and Administrative Budget g. Cost of Goods Sold Budget h. Budgeted Income Statement i. Cash receipts budget j. Cash payments budget k. Cash budget All blue cells are for data input. Green cells are for formula input. I expect you to use Excel formulas whenever possible. April May June Total Sales Budget # of mats sold Price per Mat Total Sales April May June Total Production Budget #Units Sold Ending Inventory Beginning Inventory Units to be produced April May June Total Materials Purchases Budget # Units Produced Yds per mat Materials Needed for Production Ending Materials Inventory Beginning Inventory Total Purchases of materials Cost per Yard Total Cost of Direct Materials Purchases April May June Total April May June Total Direct Labor Budget #Units to be produced Hours per Unit Total Direct Labor Hours Cost per hour Total Direct Labor Manufacturing Overhead # Units Produced Hours Per Unit Total Hours Variable OH rate per hour Total Variable Overhead Fixed OH rate per hour Fixed Overhead Total Overhead April May June Total Selling General and Administrative Budget Total Sales Delivery & Sales Commissions Total Selling Expenses Fixed G&A Expenses Total Selling General and Administrative Exp April May June Total Cost of Goods Sold: #Units Sold Cost Per Unit (See below) Cost of Goods Sold April May June Total Income Statement Sales Cost of Goods Sold Gross Profit Selling General and Administrative Operating Income Cost of Goods Sold: Materials Direct Labor VOH FOH Total Cost per Mat Sold Cash Receipts Budget Cash Sales (30%) AR Collections: Month of Sale (10%) Month after Sale (50%) Second Month after sale (10%) Total Cash Collected April May June Total April May June Total Cash Payments Budget: Materials Purchases: Month of Purchase (25%) 2nd Month of Purchase (75%) Total Materials Purchases Direct Labor Variable Overhead Fixed Overhead Selling Expenses General & Administrative Less Depreciation: General & Administrative Cash paid for Equipment Cash Paid for Dividends Total Cash Payments April May June Total Cash Budget Cash Balance, Beginning of Period Cash Receipts Cash Payments Cash Balance Before Financing Cash Borrowed or (Repaid) Ending Cash Balance George Budget Check Figures Total Units to be produced = 44,750 Total May Materials Purchases = $68,625 Total Variable Overhead = $161,100 June Operating Income = $43,580 April Cash Collected/Total Cash Receipts = $277,200 Total payments for materials purchases = $193,365