Provide solution in excel sheet with formulas $,$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 O$11$

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

$3$rd Quarter July August September $3$rd Quarter

Sales in Units $8,00015,00012,00035,000$

Selling Price per Unit $35353535$

Total Sales in $ $280,000525,000420,0001,225,000$

$2$

Cash Collections $-$

June's Cash Collections $96,25096,250$

July's Cash Collections $112,000154,000266,000$

August's Cash Collections $210,000288,750498,750$

September's Cash Collections $168,000168,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

$3$rd Quarter July August September $3$rd Quarter

Budgeted Sales in Units $8,00015,00012,00035,000$

Add: Desired Ending Inventory $7506004001,750$

Total Needs $8,75015,60012,40036,750$

Less: Beginning Inventory $4007506001,750$

Required Production $8,35014,85011,80035,000$

Raw Materials Purchasing Budget

Each unit of SUPER DUPER widget requires $0\mathrm{.}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\mathrm{.}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

$3$rd Quarter July August September $3$rd 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\mathrm{.}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$.$