Use FORMULAS in EXCEL

What I need is

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/2021 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/2021

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 1. The marketing department has estimated sales as follows for the remainder of the year: (Actual sales in June were 6,000 units) July August September 8,000 15,000 12,000 October November December 4,000 6,000 8,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 3rd Quarter Total July August September 3rd Quarter Sales in Units Selling Price per Unit Total Sales in $ Cash Collections June's Cash Collections July's Cash Collections August's Cash Collections September's Cash Collections Total Cash Collections 1 2. 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 Add: Desired Ending Inventory Total Needs Less: Beginning Inventory Required Production 3. 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,028 pounds. WHAM compound costs $5,00 per pound and Vaughan pays for 70% 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 70% was paid for in June. Vaughan Company RM Purchasing Budget 3rd Quarter Total 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 June's RM Purchases July's RM Purchases August's RM Purchases September's RM Purchases 2 Total Disbursements 3 4. The next Budget is the Direct Labor Budget. Let's assume that each unit takes 0.5 DLH to make and each DLH costs $16. Let's further assume that labor is paid in the month incurred. Vaughan Company DL Budget 3rd Quarter Total 3rd Quarter July August September Budgeted Production in Units DLH per Unit Total DLH needed Cost per DLH Total Direct Labor Cost 5. Next we will prepare our FOH budget. FOH is applied based on DLH. Estimated variable FOH is expected to be $420,000 and estimated DLH are expected to be 300,000. Fixed FOH is estimated to be $6,960 per month with $3,000 of that amount being depreciation of factory equipment and building. Like DL, assume that FOH is paid in the month incurred. Vaughan Company FOH Budget 3rd Quarter Total 3rd Quarter July August September Budgeted DLH Variable FOH rate Total Budgeted Variable FOH Total Budgeted Fixed FOH Total Budgeted FOH Less: Depreciation Cash Needed for FOH Total FOH per Budget Budgeted DLH this period Predetermined FOH per DLH 4 6. Now we need to prepare the Ending FG Inventory Budget. Vaughan Company Ending FG Inventory Budget 3rd Quarter Quantity Cost Total Cost Per Unit: Direct Materials Direct Labor FOH Unit Cost Ending Inventory in Units Cost Per Unit Ending FG Inventory 7. Now it is time for the Selling and Administrative Budget. It too will be divided into a variable portion and a fixed portion. Assume that variable S&A costs are $3 per unit plus bad debt expense. Further assume that monthly Fixed Costs are as follows: Advertising $20,000, Executive Salaries $50,000, Other $25,000, and Office depreciation is $3,000. S&A are paid in the month incurred. Vaughan Company S&A Budget 3rd Quarter Total 3rd Quarter July August September Budgeted Sales Variable S&A Expenses Budgeted Variable S&A Exp. Budgeted Fixed S&A Expenses: Advertising Executive Salaries Other Depreciation Total Fixed S&A Expenses Total Budgeted S&A Expenses Less: Depreciation Less: Bad Debt Expense Budgeted Cash S&A Expenses 5 8. From here we go to the cash budget. We need to know cash receipts, cash disbursements, Cash flows/deficit, and the financing section. Vaughan's cash guidelines are as follows: They have a line of credit that can be accessed in $1,000 increments at an annual interest rate of 6%. Money will be borrowed on the last day of a given month and paid back on the last day of the month when it can be. Minimum cash balance required by Vaughan is $50,000. The beginning cash balance on July 1 is $50,000. Interest is paid when money is paid back. Vaughan Company Cash Budget 3rd Quarter July August September Beginning Cash Balance Add: Cash Receipts Total Cash Available Less: Disbursements Direct Materials Direct Labor FOH S&A Expenses Total Disbursements Cash Balance (Deficit) Borrowings RePayments Interest Ending Cash Balance 9. From here we move on to the Budgeted Income Statement. Vaughan Company Budgeted Income Statement 3rd Quarter Total 3rd Quarter July August September Sales Less: CCS Gross Margin Less: S&A Expenses Net Operating Income Less: Interest Expense Net Income 6 10. And last but not least, we have the budgeted Balance Sheet! We need to know what our balance sheet looked like on July 1, so here goes: Vaughan Company Balance Sheet 7/1/Year 2XXX Liabilities & SE: A/P (given) $ $ 15,120 Assets: Cash (given) A/R (given) RM Inventory (2,028 $5.00) FG Inventory (400 * 13.30) Land Building&Equipment Less A/D Total Assets 50,000 115,500 10,140 5,320 30,000 170,000 (40,000) 340,960 Common Stock Retained Earnings Total Liab & SE: $ 250,000 75,840 340,960 5 Vaughan Company Budgeted Balance Sheet 9/30/Year 2XXX Liabilities & SE: A/P Assets: Cash A/R RM Inventory FG Inventory Land Building&Equipment Less A/D Total Assets Common Stock Retained Earnings Total Liab & SE: 7