Provide solution in excel sheet with formulas solve these two parts g and h only also show supplementary working (,g) a selling & administrative budget for each month and the quarter

h) a cash budget for each month (but not quarter) used to solve Vaughan Company makes AMAZING SUPER DUPER Widgets. You and your team are about to

prepare the detailed budgets for the third quarter, July through September. Using the budget template

and information given in this workbook, prepare the master budget for third quarter including: Master Budget Project

Group O11

Vaughan Company makes AMAZING SUPER DUPER Widgets. Management

is now preparing detailed budgets for the third quarter, July through September, and

has assembled the following information to assist in budget preparation:

Sales Budget

The marketing department has estimated sales as follows for the remainder of

the year: Actual sales in June were 5,000

July 8,000 October 8,000

August 15,000 November 6,000

September 12,000 December 10,000

The selling price of a SUPER DUPER widget is $35 and all sales are on account.

Based on past experience, sales are collected in the following pattern:

40% in the month of sale

55% in month following the sale

5% are never collected (uncollectible)

Vaughan Company

Sales Budget Total

3rd Quarter July August September 3rd Quarter

Sales in Units 8,000 15,000 12,000 35,000

Selling Price per Unit 35 35 35 35

Total Sales in $ 280,000 525,000 420,000 1,225,000

2

Cash Collections -

June's Cash Collections 96,250 96,250

July's Cash Collections 112,000 154,000 266,000

August's Cash Collections 210,000 288,750 498,750

September's Cash Collections 168,000 168,000

Total Cash Collections $ 208,250 $ 364,000 $ 456,750 $ 1,029,000

Production Budget

The company maintains a finished goods inventory equal to 5% of the following

month's sales. The inventory of finished goods on July 1 is as it should be.

Vaughan Company

Production Budget Total

3rd Quarter July August September 3rd Quarter

Budgeted Sales in Units 8,000 15,000 12,000 35,000

Add: Desired Ending Inventory 750 600 400 1,750

Total Needs 8,750 15,600 12,400 36,750

Less: Beginning Inventory 400 750 600 1,750

Required Production 8,350 14,850 11,800 35,000

Raw Materials Purchasing Budget

Each unit of SUPER DUPER widget requires 0.8 pounds of WHAM compound. To

prevent shortages, the company would like the inventory of WHAM compound on hand

at the end of each month to equal 30% of the following month's production needs.

The inventory on July 1 is 2,004 pounds.

WHAM compound costs $8.00 per pound and Vaughan pays for 50% of its purchases

in the month of purchase; the remainder is paid in the following month. $50,400

of WHAM compound was purchased in June and 50% was paid for in June.

Vaughan Company

RM Purchasing Budget Total

3rd Quarter July August September 3rd Quarter

Required Production

RM per Unit

Production Needs

Add: Desired Ending Inventory

Total Needs

Less: Beginning Inventory

RM to be Purchased

Cost of RM per pound

Cost of RM to be Purchased

Cash Disbursements for RM 2

June's RM Purchases

July's RM Purchases

August's RM Purchases

September's RM Purchases

Total Disbursements

The next Budget is the Direct Labor Budget. Let's assume that each unit takes

0.6 DLH to make and eac

a) a sales budget for each month and the quarter

b) a production budget for each month and the quarter

c) a RM budget for each month and the quarter

d) a DL budget for each month and the quarter

e) a FOH budget for each month and the quarter

f) an ending FG Inventory budget for 9/30/2024

g) a selling & administrative budget for each month and the quarter

h) a cash budget for each month (but not quarter)

i) a budgeted income statement for each month and the quarter

j) a budgeted balance sheet for 9/30/2024

Do not worry about taxes. Assume that there are no cash dividends to be paid in 2024 and there will be

no purchases of P,P and E.