please complete the following listed in the Green and Blue, please show all formulas and work!!

1 Statesboro Glass 2 3 SALES INFORMATION 4 Projected Window sales in units July 5 60000 August 55000 Septemb 60000 October 30000 9 Novemb 30000 10 11 Selling price of window (finished product output) 12 13 Collections prediction 14 collected in month of sale 15 collected in subsequent mon 16 Selling and admin per month Salaries Commissions Depreciation Shipping Other 6 7 8 17 18 19 20 21 22 23 Sheet1 0.45 of current month sales 0.55 of prior month sales Fixed portion (per month) 70000 8000 22000 D A 110 H variable cost ($ per unit sold (cost driver)) 1.4 2.4 0.75 Other PRODUCTION INFORMATION Direct materials usage-two types B Glass 9 Vinyl 20 31 Raw Materials inventory policy 32 33 34853899334 36 Overhead estimates 37 Supplies Power Maintenance Supervision Depreciation Property Taxes Other 40 41 42 22000 per unit of output (per Window) Desired ending of the month RMI Assume beginning RMI (on July 1) is Glass Vinyl Fixed portion (per month) 26000 29000 91000 8600 85000 1.75 square yards 0.67 rods 0.75 price 16 dollars 10.5 dollars 0.17 (percent) of the next month's production needs 15000 10000 variable cost (estimated as s per DLH (sum of Glass Cutter plus Assembler)) 0.75 0.85 1.2 1.3 per square yard per rod Direct labor two types Glass Cutter 68 59 60 61 62 63 64 each Glass Cutter takes 1.5 hours of direct labor 10 dollars per hour wage rate for Class Cutter is Assembler 0.75 hours of direct labor each Assembler takes wage rate for Assembler 13 dollars per hour Borrowings are assumed to occur at the beginning of the month Repayments are assumed to occur at the end of the month. When the company has enough cash on hand at the Interest rate is 0.05 (percent) per year Cash Balance at July 1 18000 dollars Minimum cash balance at end of each month 12000 dollars BANKING INFORMATION 65 66 ADDITIONAL INFORMATION 67 Accounts receivable as at July 1 68 150000 Minimum cash balance at end of each month ADDITIONAL INFORMATION Accounts receivable as at July 1 Finished Goods Inventory predictions Finished good inventory, July 1 Finished Goods Inventory policy Desired ending FGI 4 Equipment purchase for 12000 dollars 150000 18000 units 0.18 (percent) of next honths sales volume 2700000 dollars in July DUE SUNDAY JUNE 12, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Statesboro Glass Excel Project DROPBOX IN FOLIO. Sales budget 2. Production budget 3. Direct materials purchases budget 4. Direct labor budget 5. Overhead budget 6. Selling and Admin budget 7. Cash Budget 8. Income Statement 1 Statesboro Glass 2 3 SALES INFORMATION 4 Projected Window sales in units July 5 60000 August 55000 Septemb 60000 October 30000 9 Novemb 30000 10 11 Selling price of window (finished product output) 12 13 Collections prediction 14 collected in month of sale 15 collected in subsequent mon 16 Selling and admin per month Salaries Commissions Depreciation Shipping Other 6 7 8 17 18 19 20 21 22 23 Sheet1 0.45 of current month sales 0.55 of prior month sales Fixed portion (per month) 70000 8000 22000 D A 110 H variable cost ($ per unit sold (cost driver)) 1.4 2.4 0.75 Other PRODUCTION INFORMATION Direct materials usage-two types B Glass 9 Vinyl 20 31 Raw Materials inventory policy 32 33 34853899334 36 Overhead estimates 37 Supplies Power Maintenance Supervision Depreciation Property Taxes Other 40 41 42 22000 per unit of output (per Window) Desired ending of the month RMI Assume beginning RMI (on July 1) is Glass Vinyl Fixed portion (per month) 26000 29000 91000 8600 85000 1.75 square yards 0.67 rods 0.75 price 16 dollars 10.5 dollars 0.17 (percent) of the next month's production needs 15000 10000 variable cost (estimated as s per DLH (sum of Glass Cutter plus Assembler)) 0.75 0.85 1.2 1.3 per square yard per rod Direct labor two types Glass Cutter 68 59 60 61 62 63 64 each Glass Cutter takes 1.5 hours of direct labor 10 dollars per hour wage rate for Class Cutter is Assembler 0.75 hours of direct labor each Assembler takes wage rate for Assembler 13 dollars per hour Borrowings are assumed to occur at the beginning of the month Repayments are assumed to occur at the end of the month. When the company has enough cash on hand at the Interest rate is 0.05 (percent) per year Cash Balance at July 1 18000 dollars Minimum cash balance at end of each month 12000 dollars BANKING INFORMATION 65 66 ADDITIONAL INFORMATION 67 Accounts receivable as at July 1 68 150000 Minimum cash balance at end of each month ADDITIONAL INFORMATION Accounts receivable as at July 1 Finished Goods Inventory predictions Finished good inventory, July 1 Finished Goods Inventory policy Desired ending FGI 4 Equipment purchase for 12000 dollars 150000 18000 units 0.18 (percent) of next honths sales volume 2700000 dollars in July DUE SUNDAY JUNE 12, midnight. PLACE YOUR SPREADSHEET SOLUTION IN THE Statesboro Glass Excel Project DROPBOX IN FOLIO. Sales budget 2. Production budget 3. Direct materials purchases budget 4. Direct labor budget 5. Overhead budget 6. Selling and Admin budget 7. Cash Budget 8. Income Statement