**please provide formulas in excel**

EXCEL PROJECT ACCT2102 SUMMER 2022 DUE SUNDAY JUNE 12, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Statesboro Glass Excel Project DROPBOX IN FOLIO. Information for Statesboro Glass Budgets. Statesboro Glass Ltd. produces windows. Sales of windows in units for the next 5 months are projected to be: July 60,000 August 55,000 September 60,000 October 30,000 November 30,000 The following information pertains to the budget assumptions. a. Finished goods inventory on July 1 is expected to be 18000 units. The desired ending FGI for any month (except for the June 30 inventory as noted previously) is expected to be 18% of the following month's sales. b. Data for materials used are: Glass 1.75 square yards per unit $16.00 per square yard $10.50 per rod Vinyl 0.67 rods per unit Raw materials ending inventory is always budgeted to equal 17% of the following month's production needs. On June 30, the ending inventory of Glass is expected to be 15,000 square yards and for Vinyl is expected to be 10,000 rods. c. Direct labor used: Glass Cutter: 1.5 hour at a rate of $10 per hour. Assembler: three quarters of an hour at a rate of $13 per hour. I d. Overhead each month is estimated at: Overhead estimates Fixed portion (per month variable cost (estimated as $ per DLH (sum of Glass Cutter plus Assembler Supplies 0.75 Power 0.85 Maintenance 26000 12 Supervision 29000 Depreciation 91000 Property Taxes 8600 Other 85000 13 e. Selling and Admin is estimated to be: Selling and admin per month Fixed portion (per month) variable cost ($ per unit sold (cost driver)) Salaries 70000 Commissions 1.4 Depreciation 8000 2.4 Shipping Other 22000 0.75 f. Selling price per Window is $110.00. 8. Sales are all on account. 45% of sales are collected in the month of sale and 55% in the month following the sale. Accounts receivable on January 1 is $150,000. h. July is a planned purchase of equipment for $2,700,000, 1. Borrowings are at 5% per year interest, and borrowings are assumed to be at the beginning of the month required and at the end of the month of repayment. July 1 beginning cash is $18,000. Interest on borrowings is paid before any principal can be paid. To the extent possible, the company strives to repay any debt at the end of each month if there is cash available; interest to date is paid before the principal reduction. Minimum cash balance at the end of any month is $12,000. REQUIRED: Please prepare the following budgets in an Excel spreadsheet for each month of the quarter beginning July 1 and for the quarter in total. The first six (light green highlighted) operating budgets are to be on one tab, separate from the original data, and the two (light blue highlighted) financial budgets are to be on a third, separate, tab. All Excel budget sheets should be nothing but formulae and cell references: there should be no Numbers directly keyed into a cell of your budgets. All the cells should have algebraic formulae (eg use "sum" function), or the cells would have a cell reference to the data sheet (eg use "=data!C5" to refer to the January sales volume). The original data tab should not have any changes - you should turn in the two budget tabs and the separate tab (untouched) with the original data all in the same Excel workbook. Formulae will be graded. As well, all numbers should be correct - this will be graded. Good organization, presentation, and formatting for the budgets should be followed and will be graded. As well, making the budgets easy for the reader to read will also count toward the grade. DUE SUNDAY JUNE 12, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Statesboro Glass Excel Project DROPBOX IN FOLIO. Sales budget Direct labor budget 2. Production budget 3. Direct materials purchases budget 5. Overhead budget. 6. Selling and Admin budget Cash Budget 8. Income Statement I Statesboro Glass SALES INFORMATION 4 Projected Window sales in units July 60000 6 August 55000 60000 7 September October 30000 9 November 30000 10 11 Selling price of window (finished product outptut) 12 13 Collections prediction 14 collected in month of sale 15 collected in subsequent month 16 17 Selling and admin per month 18 Salaries Commissions Depreciation Shipping Other 24 25 PRODUCTION INFORMATION 26 Direct materials usage-two types 27 28 Glass Vinyl 29 30 31 Raw Materials inventory policy 32 33 34 19 20 21 22 23 0.45 of current month sales 0.55 of prior month sales Fixed portion (per month) 70000 8000 22000 per unit of output (per Window) Desired ending of the month RMI Assume beginning RMI (on July 1) is 110 variable cost ($ per unit sold (cost driver)) 14 24 0.75 price Glass Vinyl 1.75 square yards 0.67 rods 16 dollars 105 dollars 0.17 (percent) of the newmonth's production needs 15000 10000 per square yard per rod C 41 36 Overhead estimates 37 38 Supplies Power 39 40 Maintenance Supervision Depreciation 42 43 Property Taxes 44 Other 45 46 47 Direct labor two types 48 Glass Cutter 49 each Glass Cutter takes 50 wage rate for Class Cutter is 51 52 Assembler 53 each Assembler takes 54 wage rate for Assembler 55 56 D Fixed portion (per month) 26000 29000 91000 8600 85000 E F G H M variable cost (estimated as $ per DLH (sum of Glass Cutter plus Assembler 0.75 0.85 1.2 1.3 15 hours of direct labor 10 dollars per hour 0.75 hours of direct labor 13 dollars per hour N 66 ADDITIONAL INFORMATION 67 Accounts receivable as at July 1 68 69 Finished Goods Inventory predictions 70 71 Finished good inventory, July 1 Finished Goods Inventory policy Desired ending FGI 72 73 74 Equipment purchase for 75 76 77 78 150000 18000 units 0.18 (percent) of next months sales volume in July 2700000 dollars EXCEL PROJECT ACCT2102 SUMMER 2022 DUE SUNDAY JUNE 12, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Statesboro Glass Excel Project DROPBOX IN FOLIO. Information for Statesboro Glass Budgets. Statesboro Glass Ltd. produces windows. Sales of windows in units for the next 5 months are projected to be: July 60,000 August 55,000 September 60,000 October 30,000 November 30,000 The following information pertains to the budget assumptions. a. Finished goods inventory on July 1 is expected to be 18000 units. The desired ending FGI for any month (except for the June 30 inventory as noted previously) is expected to be 18% of the following month's sales. b. Data for materials used are: Glass 1.75 square yards per unit $16.00 per square yard $10.50 per rod Vinyl 0.67 rods per unit Raw materials ending inventory is always budgeted to equal 17% of the following month's production needs. On June 30, the ending inventory of Glass is expected to be 15,000 square yards and for Vinyl is expected to be 10,000 rods. c. Direct labor used: Glass Cutter: 1.5 hour at a rate of $10 per hour. Assembler: three quarters of an hour at a rate of $13 per hour. I d. Overhead each month is estimated at: Overhead estimates Fixed portion (per month variable cost (estimated as $ per DLH (sum of Glass Cutter plus Assembler Supplies 0.75 Power 0.85 Maintenance 26000 12 Supervision 29000 Depreciation 91000 Property Taxes 8600 Other 85000 13 e. Selling and Admin is estimated to be: Selling and admin per month Fixed portion (per month) variable cost ($ per unit sold (cost driver)) Salaries 70000 Commissions 1.4 Depreciation 8000 2.4 Shipping Other 22000 0.75 f. Selling price per Window is $110.00. 8. Sales are all on account. 45% of sales are collected in the month of sale and 55% in the month following the sale. Accounts receivable on January 1 is $150,000. h. July is a planned purchase of equipment for $2,700,000, 1. Borrowings are at 5% per year interest, and borrowings are assumed to be at the beginning of the month required and at the end of the month of repayment. July 1 beginning cash is $18,000. Interest on borrowings is paid before any principal can be paid. To the extent possible, the company strives to repay any debt at the end of each month if there is cash available; interest to date is paid before the principal reduction. Minimum cash balance at the end of any month is $12,000. REQUIRED: Please prepare the following budgets in an Excel spreadsheet for each month of the quarter beginning July 1 and for the quarter in total. The first six (light green highlighted) operating budgets are to be on one tab, separate from the original data, and the two (light blue highlighted) financial budgets are to be on a third, separate, tab. All Excel budget sheets should be nothing but formulae and cell references: there should be no Numbers directly keyed into a cell of your budgets. All the cells should have algebraic formulae (eg use "sum" function), or the cells would have a cell reference to the data sheet (eg use "=data!C5" to refer to the January sales volume). The original data tab should not have any changes - you should turn in the two budget tabs and the separate tab (untouched) with the original data all in the same Excel workbook. Formulae will be graded. As well, all numbers should be correct - this will be graded. Good organization, presentation, and formatting for the budgets should be followed and will be graded. As well, making the budgets easy for the reader to read will also count toward the grade. DUE SUNDAY JUNE 12, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Statesboro Glass Excel Project DROPBOX IN FOLIO. Sales budget Direct labor budget 2. Production budget 3. Direct materials purchases budget 5. Overhead budget. 6. Selling and Admin budget Cash Budget 8. Income Statement I Statesboro Glass SALES INFORMATION 4 Projected Window sales in units July 60000 6 August 55000 60000 7 September October 30000 9 November 30000 10 11 Selling price of window (finished product outptut) 12 13 Collections prediction 14 collected in month of sale 15 collected in subsequent month 16 17 Selling and admin per month 18 Salaries Commissions Depreciation Shipping Other 24 25 PRODUCTION INFORMATION 26 Direct materials usage-two types 27 28 Glass Vinyl 29 30 31 Raw Materials inventory policy 32 33 34 19 20 21 22 23 0.45 of current month sales 0.55 of prior month sales Fixed portion (per month) 70000 8000 22000 per unit of output (per Window) Desired ending of the month RMI Assume beginning RMI (on July 1) is 110 variable cost ($ per unit sold (cost driver)) 14 24 0.75 price Glass Vinyl 1.75 square yards 0.67 rods 16 dollars 105 dollars 0.17 (percent) of the newmonth's production needs 15000 10000 per square yard per rod C 41 36 Overhead estimates 37 38 Supplies Power 39 40 Maintenance Supervision Depreciation 42 43 Property Taxes 44 Other 45 46 47 Direct labor two types 48 Glass Cutter 49 each Glass Cutter takes 50 wage rate for Class Cutter is 51 52 Assembler 53 each Assembler takes 54 wage rate for Assembler 55 56 D Fixed portion (per month) 26000 29000 91000 8600 85000 E F G H M variable cost (estimated as $ per DLH (sum of Glass Cutter plus Assembler 0.75 0.85 1.2 1.3 15 hours of direct labor 10 dollars per hour 0.75 hours of direct labor 13 dollars per hour N 66 ADDITIONAL INFORMATION 67 Accounts receivable as at July 1 68 69 Finished Goods Inventory predictions 70 71 Finished good inventory, July 1 Finished Goods Inventory policy Desired ending FGI 72 73 74 Equipment purchase for 75 76 77 78 150000 18000 units 0.18 (percent) of next months sales volume in July 2700000 dollars