Fill out the excel template attached using the information below. All blue cells are for data input. Green cells are for formula input. Use Excel formulas whenever possible.

Jamison and Co makes lightweight yoga mats. Jamison is preparing its budget for the second quarter of 2022.

1. Each mat sells for $22. Sales in units are as follows:

February 12,000 $264,000

March 12,000 264,000

April 13,500 297,000

May 15,000 330,000

June 15,500 341,000

July 16,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 months 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. Jamison 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:
   1. 10% in the month of sale
   2. 50% in the month following sale
   3. 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. Jamison requires that a minimum of $20,000 remain in the bank at all times. If the balance drops below $20,000 Jamison borrows from its 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 $11,000.
8. Jamison 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 Jamison for the months of April-June 2022, showing quarterly totals. These should include the following:

1. Sales Budget
2. Production Budget
3. Materials purchases budget
4. Direct labor Budget
5. Manufacturing Overhead
6. Selling, General and Administrative Budget
7. Cost of Goods Sold Budget
8. Budgeted Income Statement
9. Cash receipts budget
10. Cash payments budget
11. Cash budget

June Total 44750 April 13950 0.3 4185 May 15150 0.3 4545 15 15650 0.3 4695 15 70425 13425 15 62775 68175 201375 Total April 13950 May 15150 June 15650 44750 25 26 27 Direct Labor Budget 28 #Units to be produced 29 Hours per Unit 30 Total Direct Labor Hours 31 Cost per hour 32 Total Direct Labor 33 34 35 Manufacturing Overhead 36 #Units Produced 37 Hours Per Unit 38 Total Hours 39 Variable OH rate per hour 40 Total Variable Overhead 41 Fixed OH rate per hour 42 Fixed Overhead 43 Total Overhead 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 Manufacturing Operating Budget Income Statement Budgets Cash Budgets + 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 Per Unit Cost of Goods Sold: Materials Direct Labor VOH FOH Total Cost per Mat Sold Manufacturing Operating Budget Income Statement Budgets Cash Budgets + D April May June Total April May June Total 1 Cash Receipts Budget 2 Cash Sales (30%) 3 AR Collections: 4 Month of Sale (10%) 5 Month after Sale (50%) 6 Second Month after sale (10%) 7 Total Cash Collected 8 9 10 Cash Payments Budget: 11 Materials Purchases: 12 Month of Purchase (25%) 13 2nd Month of Purchase (75%) 14 Total Materials Purchases 15 Direct Labor 16 Variable Overhead 17 Fixed Overhead 18 Selling Expenses 19 General & Administrative 20 Less Depreciation: 21 General & Administrative 22 Cash paid for Equipment 23 Cash Paid for Dividends 24 Total Cash Payments 25 26 27 28 Cash Budget 29 Cash Balance, Beginning of Period 30 Cash Receipts 31 Cash Payments 32 Cash Balance Before Financing 33 Cash Borrowed or (Repaid) 34 Ending Cash Balance 35 36 April May June Total 37 38 Manufacturing Operating Budget Income Statement Budgets Cash Budgets +