Kraft is a Wholesaler dairy products. The following information is available for May 2021. Note the beginning cash balance is $700,000.

Parameters:

Cheese 200,000 hoops at $20 each

Milk 300,000 at $10 each

Estimated costs:

Cheese $10 per hoop

Milk 5.00 per Gallon

Desired Inventories

Cheese 20,000 beginning, 24,000 ending

Milk 8,000 beginning, 10,000 ending

• Beginning cash balance is $700,000
• Purchases of merchandise are paid 60% in the current month and 40% in the following month. Purchases totaled $1,800,000 in April and are estimated to be $2,000,000 in June 2021.
• Employee wages and salaries are paid for in the current month. Employee Expenses for May totaled $156,000.
• Overhead expenses are paid in the next month. The accounts payable amount for these expenses from April is $80,000 and for June will be $90,000. Mays overhead expenses total $80,000.
• Sales are on credit and are collected 70% in the current period and the remainder in the next period. Aprils sales were $3,000,000 and Junes sales are estimated to be $3,200,000. Bad debts average 1% of sales.
• Selling and administrative expenses are paid monthly and total $450,000, including $50,000 of depreciation.
• All unit costs for May are the same as they were in April.

Required: Prepare the following for May 2021 using excel

1. Sales budget
2. Purchase Budget
3. Cash Budget
4. Budgeted Income Statement.
5. This is an example of what I have done given the template

700,000 2,170,000 870,000 3,040,000 3,740,000 Cash Budget - May Cash Balance Beginning Current Month Sales $3.1 X 70%) Previous Month Sales ($3.0 X 29%) Cash Available from Operations Less: Budgeted Disbursements April Purchases $1.8 x 40% May Purchases $2.0 x 60% Wages & Salaries Overhead - March Selling & Administrative 720000 1200000 156,000 80,000 400,000 Cash Ending $ 3,740,000 Budgeted Income Statement For the Month of May Sales Allowance for Bad Debts Net Sales 1% 7,000,000 70,000 6,930,000 Cost of Mdse sold: Cheese Ice Cream 2,000,000 1500000 3,500,000 Wages & Salaries Overhead Selling & Administrative 156,000 80,000 400,000 636,000 4,136,000 2,794,000.0 Net income $