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 Vinyl 0.67 rods per unit $10.50 per rod 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 Lesezidan.com/a21/16/content/665752/viewContent/10327978/View 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. d. Overhead each month is estimated at: Overhead estimates Supplies Fixed portion (per month) Power Maintenance 26000 Supervision 29000 Depreciation 91000 8600 variable cost (estimated as $ per DLH (sum of Class Cutter plus Assembler 0.75 0.85 12 d. Overhead each month is estimated at: Overhead estimates variable cost (estimated as $ per DLH (sum of Glass Cutter plus Assembler)) 0.75 Fixed portion (per month) Supplies Power Maintenance 26000 Supervision 29000 Depreciation 91000 Property Taxes Other 8600 85000 0.85 12 13 e. Selling and Admin is estimated to be: Selling and admin per month Salaries Commissions Depreciation Fixed portion (per month) 70000 variable cost ($ per unit sold (cost driver)) 8000 1.4 Shipping Other 22000 f. Selling price per Window is $110.00. 2.4 0.75 g. 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. i. 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: artor-and for Please prepare the following budgets in an Excel spreadsheet for each month of the first quarter 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. B C D G H L M N 1 Statesboro Glass 2 3 SALES INFORMATION 4 Projected Window sales in units 5 July 60000 6 August 55000 7 September 60000 8 October 30000 9 November 30000 10 11 Selling price of window (finished product output) 12 13 Collections prediction 14 collected in month of sale 110 0.45 of current month sales 15 collected in subsequent month 0.55 of prior month sales 16 17 Selling and admin per month 18 Fixed portion (per month) variable cost (5 per unit sold (cost driver]) 19. Salaries 70000 20 21 22 23 24 Commissions Depreciation Shipping Other 25 PRODUCTION INFORMATION 26 Direct materials usage two types 1.4 8000 22000 +4 0.75 price 16 dollars 10.5 dollars per square yard per rod 0.17 (percent) of the next month's production needs 27 28 29 Glass Vinyl per unit of output (per Window) 1.75 square yards 0.67 rods 30 31 Raw Materials inventory policy 32 33 Desired ending of the month RMI Assume beginning RMI (on July 1) is 34 Glass Vinyl 15000 10000 35 36 Overhead estimates 37 Fixed portion (per month) 38 Supplies 39 Power 40 41 Maintenance Supervision 26000 29000 21000 variable cost (estimated as $ per DLH sum of Glass Cutter plus Assembler 0.75 0.85 1.2 F G M 4 4 K 40 Mantonance " M N 0 . 26000 12 Supervis 29000 42 Depreciation 91000 43 Property Taxes 8600 44 Other 85000 13 46 47 Direct labor two type 30 32 54 Glam Cutter sch Glass Cutter takes wage rate for Class Cutter is Assembler each Assembler takes wage rate for Assembler 13 hours of drealtor 10 dollars per hour 0.75 hours of direct labor 13 dollars per hour 18 33 BANKING INFORMATION 60 63 4X 13 14 Borrowings are assumed to occur at the beginning of the month payments are mad to occur at the end of the month. When the company has enough cash on hand at the end of the month, repays the interest to date, they principal to the exp Cash Balance at July 1 Minimum cash balance at and of each month 0.05 (percent) per year 18000 12000 65 66 ADOMONAL INFORMATION 17 7 Accounts receivable at July 1 150000 70 Finished good inventory, July 1 18000 0 " Desired ending F 74 Equipment purchase for 0.18 of next mans sales volume 2700000 lars in July