All the work is already done . could just help me verify if i did anythng wrong ?

here is the question and balanced sheet

Insight Glass makes sliding glass doors for two local construction companies and wants to prepare a master budget for the next month of operation, June 2014. The sales department estimates that it can sell 180 doors in June. Each door retails for $1,100. In order to avoid delays in shipping, management wants to maintain ending inventory each month equal to 10% of the estimated unit sales in that month. Beginning inventory of finished doors is expected to be 20 units as of June 1, 2014 with a per unit cost of $624. Note: The per unit cost of doors produced during June may vary slightly from the per unit cost of June's beginning Finished Goods Inventory (given as $624). In other words, you will need to arrive at a new per unit cost for the month of June. Each door takes 36 square feet of glass and 4 hours of direct labor. Glass purchases are estimated at $10 per square foot and direct labor averages $16 an hour, including benefits. As of June 1st, Insight Glass estimates it will have 1500 square feet of glass in raw materials inventory and would like to have ending inventory of 1000 feet. Variable overhead costs are estimated at $100 per door. Fixed overhead for the month is estimated to be $17,010. Insight Glass anticipates selling and administrative costs of $18,750 monthly and the monthly interest cost on its long-term debt is 1% of the outstanding balance, paid on the 30th of each month. The principal payment on the debt is $25,000 per month. Insight Glass, as a corporation, expects to pay 40% of its net income in income taxes. Monthly estimates are sent to the appropriate taxing authority by the 10th of the next month (therefore, income taxes payable as of 5/31/14 will be paid on 6/10). Monthly depreciation of the building and the equipment is $8,000 and $10,000 respectively (this is the sales/administrative portion of depreciation and not a part of overhead production costs). All sales are on account. However, in estimating cash flows, Insight Glass expects 60% of the current month sales to be received by the end of the month and the balance to be collected in the next month. Therefore, all the Accounts Receivable owing at June 1s (which represents 40% of May sales) is expected to be received in June. Glass is purchased on account. Exactly 70% of the purchases are paid in the current month and the balance is paid early in the next month. The balance of Accounts Payable owing at June 1st, will be paid in June. Assume all other expenses (both delays manag ending inventory each month equal to 10% of the estimated unit sales in that month. Beginning inventory of finished doors is expected to be 20 units as of June 1, 2014 with a per unit cost of $624. Note: The per unit cost of doors produced during June may vary slightly from the per unit cost of June's beginning Finished Goods Inventory (given as $624). In other words, you will need to arrive at a new per unit cost for the month of June. Each door takes 36 square feet of glass and 4 hours of direct labor. Glass purchases are estimated at $10 per square foot and direct labor averages $16 an hour, including benefits. As of June 1st, Insight Glass estimates it will have 1500 square feet of glass in raw materials inventory and would like to have ending inventory of 1000 feet. Variable overhead costs are estimated at $100 per door. Fixed overhead for the month is estimated to be $17,010. Insight Glass anticipates selling and administrative costs of $18,750 monthly and the monthly interest cost on its long-term debt is 1% of the outstanding balance, paid on the 30th of each month. The principal payment on the debt is $25,000 per month. Insight Glass, as a corporation, expects to pay 40% of its net income in income taxes. Monthly estimates are sent to the appropriate taxing authority by the 10th of the next month (therefore, income taxes payable as of 5/31/14 will be paid on 6/10). Monthly depreciation of the building and the equipment is $8,000 and $10,000 respectively (this is the sales/administrative portion of depreciation and not a part of overhead production costs). All sales are on account. However, in estimating cash flows, Insight Glass expects 60% of the current month sales to be received by the end of the month and the balance to be collected in the next month. Therefore, all the Accounts Receivable owing at June 1s* (which represents 40% of May sales) is expected to be received in June. Glass is purchased on account. Exactly 70% of the purchases are paid in the current month and the balance is paid early in the next month. The balance of Accounts Payable owing at June 1st, will be paid in June. Assume all other expenses (both production and administrative) are paid in the month incurred. The Board of Directors for the company plans to declare and pay a $.50 per share cash dividend during the month of June. AutoSave CF HD BA 213 Insight Glass Project Template Search anthony andriamalala File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments AutoSum Calibri 89 Wrap Text General 27 O ~ A A A NE EX Insert Delete format X Cut LECopy + Format Painter Clipboard ril Peste Idees BTU H Merge & Center $ % -8 LA Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter Select Eding Font Alignment Number Cels dias 135 > AB C D E F H L M N C a R s T Prepare a master budget for the month of June 2014 Insight Glass Company Sales Budget For the Month Ending lune 30, 2014 3 4 5 6 7 B 9 10 11 12 13 14 15 16 Data Table Part 1 Company Name Insight Glass Company Budgeted Sales, in units 180 Expected Sales price S 1,100.00 Beginning Inv. FG (in units): 20 Ending Irv. ol FG desired fin units): 18 -10% *Desired El in Units is 10% of current's month sales Budgeled Sales, Door Units Sales Price per Unit Total Sales 5 180 1,100.00 198,000.00 $ Insight Glass Company Production Budget For the Month Ending lune 30, 2014 Deg Irv, Direct Material (pass) in so it Ending Inv, Glass in sq ft Square feet of glass required per unit Cost Per sq ft glass Direct Material (glass) Cost per unit 1,500 1,000 36 10.00 360.00 S s S 18 19 20 21 Budeeted Sales, Doors Add Desired Ending Inventory Total Units Needed Less Beginning Inventory Budgeted Doors to produce in June 180 18 198 (20) 1/8 Direct Labor Hours per unit Direct Labor Cost per Hour 4.0 16.00 S 100.00 Variable OH per Unit Fixed OH S $ 17.010.00 23 24 25 26 27 28 Insight Glass Company Direct Materials Budget For the Month Ending June 30, 2014 Insight Glass Company Direct Labor Budget For the Month Ending June 30, 2014 30 t uritta irai- May 31 Balance Sheet Budgets Proforma Income Statement Dudestad lie to Broduca Proforma Balance Sheet + - 100% Type here to search o Ei NG 433 AM 6/12/2020 AutoSave CF HD BA 213 Insight Glass Project Template Search anthony andriamalala AA File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri - AHA AutoSum 89 Wrap Text General 27 O NE EX Insert Delete format ril X Cut LECopy + Format Painter Clipboard Peste BIU Idees EE A Merge & Center $ -% Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Font Alignment Number Cels dias 135 fx C D E F G H 1 L M N C P a R s 1 A B 26 T Insight Glass Company Direct Materials Budget For the Month Ending June 30, 2014 Insight Glass Company Direct Labor Budget For the Month Ending, June 30, 2014 S $ Budgeted Units to produce Direct Material Cost per Unit Direct Materials Needed for Production Add Value of Desired Direct Materials in Ending Inv. Total Direct Materials Needed Less: Value of Direct Materials in Beginning Irv. Budeeted Purchases of Direct Materials 178 36 64,080 10,000.00 74,080 15,000.00 59,080 Budgcted Units to produce Direct Labor Hours per Unit Direct Labor Hours needed Direct Labor Cost per Hour Budgeted Direct Labor Cost 178 4.0 712 16.00 11,392.00 5 S $ $ 5 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 49 49 50 51 52 53 Total Projected Manufacturing Costs per Unit Insight Glass Company Manufacturing Overhead Budget For the Month Ending June 30, 2014 Direct Materials Direct Labor (for 4 hours) Allocated MOH(for 4 hours) Total Projected Cost per Unit S $ $ S 360.00 64.00 123.89 547.89 Budgeted Units to Produce VOH per Door Budgeted VOH Budgeted FOH Budgeted Manufacturing Overhead Costs $ $ $ $ 178 100.00 17,800.00 17,010.00 34,810 Ending Inv. of FG desired (in units): Total Projected Cost per Unit Ending rivolg $ $ 18 547.89 9,862.02 Direct Labor Hours 712 48.89 Predetermined MOH Allocation rate (per hour) - Proforma Balance Sheet May 31 Balance Sheet Budgets Proforma Income Statement - 100% Type here to search O NG 433 AM 6/12/2020 AutoSave C E BA 213 Insight Glass Project Template Search anthony andriamalala File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri - AHA AutoSum 89 Wrap Text Ceneral 27 O NI EX Insert Delete format X Cut LECopy + Format Painter Clipboard Peste Idees BTU H A Merge & Center $ % -8 Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter Select Eding Font Alignment Number Cels deas 135 1 AB C D E F G H M N C P a R s T J K L Predetermined MOH Allocation rate (per hour) - BMOHC/DLHR per unit S 48.89 Insicht Glass Company Cost of Goods Sold Budget For the Month Ending June 30, 2014 54 55 56 57 58 59 60 61 62 63 Beginning Inventory 20 Units Insight Glass Company Selling & Administrative Expense Budget For the Month Ending June 30, 2014 Cost per unit: 5 Doors Produced and sold in June 2014 160 units Cost per unit: $ 547.89 S 87,662.40 General Selling & Admin Depreciation - Building Depreciatiuni - Equipment Total Budgeted S & A expense S $ $ $ 18,750.00 8,000.00 10,000.00 36,750.00 64 Total Budgcted Cost of Goods Sold S 87,662.40 Insight Glass Cumpany Cash Budget For the Month Endine June 30, 2014 Data Table - Part 2 Annual Interest Rate on Debt Debt from Balance Sheet Monthly interest on debt (-1%): $ 65 66 67 69 69 70 71 72 73 74 75 76 77 78 Beginning Cash Balance Cash Receipts A/R balance on May 31, 2014 June Sales, 60% collected in same month Income tax rate General Selline & Admin Depreciation - Building Depreciatiuni - Equipment $ $ 118,800.00 $ 118,800.00 40% 18,750.00 8,000.00 10,000.00 Cash Available $ 118,800.00 Income Tax Payable from Balance Sheet Debt Principal Repayment by Month $ 25,000.00 80 Cash Payments Accounts Payable fr. May 31 to be paid off Jurie Purchases, 70% paid in same munith 41,356.00 May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet A/R collections % in month of sale 60% + - 1007 Type here to search o Pi NG 434 AM 6/12/2020 AutoSave C E BA 213 Insight Glass Project Template Search anthony andriamalala AA File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri AutoSum 11 ~ A A 89 Wrap Text Ceneral 27 O NI EX Insert Delete format X Cut [Copy Format Painter Clipboard Peste Idees BIU Merge & Center Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Font Alignment Number Cels dias 135 f AB D E M N C P a R s T F H For the Month Ending lune 30, 2014 L Debt from Balance Shoot Monthly interest on debt (=1%): $ Beginning Cash Balance Cash Receipts A/R balance on May 31, 2014 lunc Sales, 60% collected in same month 118,800.00 $ 118,800.00 69 70 71 72 73 74 75 76 77 78 79 80 81 B2 Income tax rate General Selling & Admin Depreciation Building Depreciation - Equipment 40% 18,750.00 8,000.00 10,000.00 $ $ $ Cash Available $ 118,800.00 Income Tax Payable from Balance Sheet Debt Principal Repayment by Month $ 25,000.00 A/R collections % in month of sale A/P payment % in month of purchase 60% 70% Cash Payments Accounts Payable I1. May 31 lu be paid oll June Purchases, 70% paid in same month Direct Labor MOH Variable MOH - Fixed Selling & Admin Income Taxes Interest Expense Ending Cash belure Financing . 41 356.00 11,392.00 17,800.00 17,010.00 18,750.00 $ 0.50 Dividends per share Number of shares outstanding $ 106,300.00 $ 12,492.00 Cash Reciepts from Customers 84 85 86 87 89 89 90 91 92 Total Sales Forecast for June 198,000.00 Financing Principal Repayments Dividends declared and paid $ 25,000.00 $ Cash Receipts A/R balance on May 31, 2014 Junc Sales, 60% collected in same month Ending Cash Balance $ (12,508.00) $ 118,800.00 95 96 May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet + - 100% Type here to search OBI NG 434 AM 6/12/2020 AutoSave C E BA 213 Insight Glass Project Template Search anthony andriamalala File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments AutoSum X Cut [Copy Calibri 89 Wrap Text Ceneral ~ A A A 27 O NE EX Insert Delete format Peste EE BIU Idees Merge & Center $ -% -8 Format Painter Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Clipboard Font Alignment Number Cels dias 135 F H J L M N C P a R s T $ 25,000.00 $ AB D E 90 Principal Repayments 91 Dividends declared and paid 92 91 Ending Cash Balance 94 95 96 Cash Receipts A/R balance on May 31, 2014 Junc Sales, 60% collected in same month $ (12,508.00) S 118,800.00 Cash Disbursements Total Direct Materials purchases 59,080 Cash Payments Accounts Payable fr. May 31 to be paid oll June Purchases, 70% paid in same month $ 41.356.00 98 99 100 101 102 103 104 105 106 107 108 109 1:10 111 112 113 114 115 116 117 118 11,392.00 17,800.00 17,010.00 18.750.00 Direct labor MOH - Variable MOH - Fixed Selling & Admin Income Taxes to be paid (from May Interest Expense Principal Repayments Dividends Paid $ $ $ $ $ S S $ 25,000.00 May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet + - 100% Type here to search o Ei NG 434 AM 6/12/2020 Required: 1. Sales budget for June 2. Production budget for June in units. 3. Direct Materials budget for June. 4. Direct Labor budget for June. 5. Manufacturing Overhead budget for June. 6. Cost of Goods Sold budget for June. 7. Cash Receipts & Cash Disbursements budget for June 8. Cash Budget for June 9. Pro-forma financial statements for June a. Multi-step Income Statement i. Link the CGS to the production budget (average cost per unit). ii. Reconcile retained earnings at the bottom of income statement. b. Classified Balance Sheet 10. Management is unsure of the projected sales figure and would like to know how many units need to be sold for the company to break-even. AutoSave CF HD BA 213 Insight Glass Project Template Search anthony andriamalala AA File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri AutoSum 11 ~ A A == 89 Wrap Text General EX 47 O NE Brill Peste X Out LECopy + Format Painter Clipboard Insert Delete Format Idees BIU Merge & Center $ -% Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Tind Filter Select Eding Font Alignment Number Cels dias 024 > A B D G H 1 J K L M N 0 P R S T U V F Proforma Balance Sheet May 31, 2014 June 30, 2014 Change S Current Assets Cash Accounts Receivable Inventory--Raw Material Finished Gouds Total Current Assets 43,000 88,000 15,000 12,480 158,180 $ S $ $ $ (12,508) 79,200 10,000 9,862 86,551 $ S S $ $ (55,508) (8,800) (5,000) (2,618) 71,926) 3 4 5 6 7 6 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 241 25 26 27 28 29 30 31 Land, Duilding and Equipment Land Building (nel of thepreciation) Equipment inet of depreciation) Total Property, Plant & Equipment 100,000 997,624 746,310 1,843,935 $ $ $ 100,000 989,625 736,310 1,825,935 S $ $ $ (8,000) (10,000) (18,000) $ Other Assets Total Assets 125,585 2,128,000 $ $ 125,585 2,038,074 S $ $ (89,926) Liabilities $ Current Liabilities Accounts Payable Income Tax Payable Total Current Liabilities 28,500 20,000 48,580.00 $ $ 17,724 29,435 47,159 S $ $ (10,776) 9,355 (1,421) $ S Long term debt Total Liabilities 575,000 623,580 550,000 597,159 S $ (25,000) (26,421) $ May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet + FI - 100% Type here to search o Ei 3:22 PM 6/12/2020 AutoSave CH BA 213 Insight Glass Project Template Search anthony andriamalala File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri AutoSum 11 ~ A A 89 Wrap Text General 47 O NE Brill Paste Insert Delete Format Idees BIU H Merge Center $ -% Conditional Format as Cell Formatting Table Styles Styles Painter Sort & Tind Filter Sclect Clear Font Alignment Number Cels Eding dias 024 > A B D E F H - J K L M N 0 P R S T U V $ Land, Huilding and Equipment Land Building (net of depreciation) Equipment inet of depreciation) Tolal Property, Plant & Equipment 100,000 997,625 746,310 1843,935 $ $ $ 100,000 989,625 736,310 1,825,935 $ $ $ S (8,000) (10,000) (18,000) 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Other Assets Total Assets 125,585 2,128,000 $ $ 125,585 2,038,071 $ $ $ S (89,926) Liabilities O $ $ Current Liabilities Accounts Payable Income Tax Payable Total Current Liabilities 28,500 20,080 48,580.00 $ 17,724 29,435 47,159 $ $ $ (10,776) 9,355 (1,421) $ Long term debit Total Liabilities 575,000 623,500 $ $ 550,000 597,159 $ $ (25,000) (26,421) 30 31 32 33 Slockholder's Equity Common Stock: $4 Par Value 100,000 shares outste Additional Paid-In Capital Retained Earnings Total Stockholders' Equity 400,000 600,000 504,420 1,501,120 S $ $ $ $ 400,000 600,000 548,573 1,5118,573 $ 44,153 11.153 $ $ 35 36 37 30 39 Tolal Liabilities and Stockholders' Equity S 2,128,000 $ 2.145,732 $ 17,732 $ 107,657.58 May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet + 100% Type here to search o Ei 3:22 PM 6/12/2020 Insight Glass makes sliding glass doors for two local construction companies and wants to prepare a master budget for the next month of operation, June 2014. The sales department estimates that it can sell 180 doors in June. Each door retails for $1,100. In order to avoid delays in shipping, management wants to maintain ending inventory each month equal to 10% of the estimated unit sales in that month. Beginning inventory of finished doors is expected to be 20 units as of June 1, 2014 with a per unit cost of $624. Note: The per unit cost of doors produced during June may vary slightly from the per unit cost of June's beginning Finished Goods Inventory (given as $624). In other words, you will need to arrive at a new per unit cost for the month of June. Each door takes 36 square feet of glass and 4 hours of direct labor. Glass purchases are estimated at $10 per square foot and direct labor averages $16 an hour, including benefits. As of June 1st, Insight Glass estimates it will have 1500 square feet of glass in raw materials inventory and would like to have ending inventory of 1000 feet. Variable overhead costs are estimated at $100 per door. Fixed overhead for the month is estimated to be $17,010. Insight Glass anticipates selling and administrative costs of $18,750 monthly and the monthly interest cost on its long-term debt is 1% of the outstanding balance, paid on the 30th of each month. The principal payment on the debt is $25,000 per month. Insight Glass, as a corporation, expects to pay 40% of its net income in income taxes. Monthly estimates are sent to the appropriate taxing authority by the 10th of the next month (therefore, income taxes payable as of 5/31/14 will be paid on 6/10). Monthly depreciation of the building and the equipment is $8,000 and $10,000 respectively (this is the sales/administrative portion of depreciation and not a part of overhead production costs). All sales are on account. However, in estimating cash flows, Insight Glass expects 60% of the current month sales to be received by the end of the month and the balance to be collected in the next month. Therefore, all the Accounts Receivable owing at June 1s (which represents 40% of May sales) is expected to be received in June. Glass is purchased on account. Exactly 70% of the purchases are paid in the current month and the balance is paid early in the next month. The balance of Accounts Payable owing at June 1st, will be paid in June. Assume all other expenses (both delays manag ending inventory each month equal to 10% of the estimated unit sales in that month. Beginning inventory of finished doors is expected to be 20 units as of June 1, 2014 with a per unit cost of $624. Note: The per unit cost of doors produced during June may vary slightly from the per unit cost of June's beginning Finished Goods Inventory (given as $624). In other words, you will need to arrive at a new per unit cost for the month of June. Each door takes 36 square feet of glass and 4 hours of direct labor. Glass purchases are estimated at $10 per square foot and direct labor averages $16 an hour, including benefits. As of June 1st, Insight Glass estimates it will have 1500 square feet of glass in raw materials inventory and would like to have ending inventory of 1000 feet. Variable overhead costs are estimated at $100 per door. Fixed overhead for the month is estimated to be $17,010. Insight Glass anticipates selling and administrative costs of $18,750 monthly and the monthly interest cost on its long-term debt is 1% of the outstanding balance, paid on the 30th of each month. The principal payment on the debt is $25,000 per month. Insight Glass, as a corporation, expects to pay 40% of its net income in income taxes. Monthly estimates are sent to the appropriate taxing authority by the 10th of the next month (therefore, income taxes payable as of 5/31/14 will be paid on 6/10). Monthly depreciation of the building and the equipment is $8,000 and $10,000 respectively (this is the sales/administrative portion of depreciation and not a part of overhead production costs). All sales are on account. However, in estimating cash flows, Insight Glass expects 60% of the current month sales to be received by the end of the month and the balance to be collected in the next month. Therefore, all the Accounts Receivable owing at June 1s* (which represents 40% of May sales) is expected to be received in June. Glass is purchased on account. Exactly 70% of the purchases are paid in the current month and the balance is paid early in the next month. The balance of Accounts Payable owing at June 1st, will be paid in June. Assume all other expenses (both production and administrative) are paid in the month incurred. The Board of Directors for the company plans to declare and pay a $.50 per share cash dividend during the month of June. AutoSave CF HD BA 213 Insight Glass Project Template Search anthony andriamalala File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments AutoSum Calibri 89 Wrap Text General 27 O ~ A A A NE EX Insert Delete format X Cut LECopy + Format Painter Clipboard ril Peste Idees BTU H Merge & Center $ % -8 LA Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter Select Eding Font Alignment Number Cels dias 135 > AB C D E F H L M N C a R s T Prepare a master budget for the month of June 2014 Insight Glass Company Sales Budget For the Month Ending lune 30, 2014 3 4 5 6 7 B 9 10 11 12 13 14 15 16 Data Table Part 1 Company Name Insight Glass Company Budgeted Sales, in units 180 Expected Sales price S 1,100.00 Beginning Inv. FG (in units): 20 Ending Irv. ol FG desired fin units): 18 -10% *Desired El in Units is 10% of current's month sales Budgeled Sales, Door Units Sales Price per Unit Total Sales 5 180 1,100.00 198,000.00 $ Insight Glass Company Production Budget For the Month Ending lune 30, 2014 Deg Irv, Direct Material (pass) in so it Ending Inv, Glass in sq ft Square feet of glass required per unit Cost Per sq ft glass Direct Material (glass) Cost per unit 1,500 1,000 36 10.00 360.00 S s S 18 19 20 21 Budeeted Sales, Doors Add Desired Ending Inventory Total Units Needed Less Beginning Inventory Budgeted Doors to produce in June 180 18 198 (20) 1/8 Direct Labor Hours per unit Direct Labor Cost per Hour 4.0 16.00 S 100.00 Variable OH per Unit Fixed OH S $ 17.010.00 23 24 25 26 27 28 Insight Glass Company Direct Materials Budget For the Month Ending June 30, 2014 Insight Glass Company Direct Labor Budget For the Month Ending June 30, 2014 30 t uritta irai- May 31 Balance Sheet Budgets Proforma Income Statement Dudestad lie to Broduca Proforma Balance Sheet + - 100% Type here to search o Ei NG 433 AM 6/12/2020 AutoSave CF HD BA 213 Insight Glass Project Template Search anthony andriamalala AA File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri - AHA AutoSum 89 Wrap Text General 27 O NE EX Insert Delete format ril X Cut LECopy + Format Painter Clipboard Peste BIU Idees EE A Merge & Center $ -% Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Font Alignment Number Cels dias 135 fx C D E F G H 1 L M N C P a R s 1 A B 26 T Insight Glass Company Direct Materials Budget For the Month Ending June 30, 2014 Insight Glass Company Direct Labor Budget For the Month Ending, June 30, 2014 S $ Budgeted Units to produce Direct Material Cost per Unit Direct Materials Needed for Production Add Value of Desired Direct Materials in Ending Inv. Total Direct Materials Needed Less: Value of Direct Materials in Beginning Irv. Budeeted Purchases of Direct Materials 178 36 64,080 10,000.00 74,080 15,000.00 59,080 Budgcted Units to produce Direct Labor Hours per Unit Direct Labor Hours needed Direct Labor Cost per Hour Budgeted Direct Labor Cost 178 4.0 712 16.00 11,392.00 5 S $ $ 5 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 49 49 50 51 52 53 Total Projected Manufacturing Costs per Unit Insight Glass Company Manufacturing Overhead Budget For the Month Ending June 30, 2014 Direct Materials Direct Labor (for 4 hours) Allocated MOH(for 4 hours) Total Projected Cost per Unit S $ $ S 360.00 64.00 123.89 547.89 Budgeted Units to Produce VOH per Door Budgeted VOH Budgeted FOH Budgeted Manufacturing Overhead Costs $ $ $ $ 178 100.00 17,800.00 17,010.00 34,810 Ending Inv. of FG desired (in units): Total Projected Cost per Unit Ending rivolg $ $ 18 547.89 9,862.02 Direct Labor Hours 712 48.89 Predetermined MOH Allocation rate (per hour) - Proforma Balance Sheet May 31 Balance Sheet Budgets Proforma Income Statement - 100% Type here to search O NG 433 AM 6/12/2020 AutoSave C E BA 213 Insight Glass Project Template Search anthony andriamalala File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri - AHA AutoSum 89 Wrap Text Ceneral 27 O NI EX Insert Delete format X Cut LECopy + Format Painter Clipboard Peste Idees BTU H A Merge & Center $ % -8 Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter Select Eding Font Alignment Number Cels deas 135 1 AB C D E F G H M N C P a R s T J K L Predetermined MOH Allocation rate (per hour) - BMOHC/DLHR per unit S 48.89 Insicht Glass Company Cost of Goods Sold Budget For the Month Ending June 30, 2014 54 55 56 57 58 59 60 61 62 63 Beginning Inventory 20 Units Insight Glass Company Selling & Administrative Expense Budget For the Month Ending June 30, 2014 Cost per unit: 5 Doors Produced and sold in June 2014 160 units Cost per unit: $ 547.89 S 87,662.40 General Selling & Admin Depreciation - Building Depreciatiuni - Equipment Total Budgeted S & A expense S $ $ $ 18,750.00 8,000.00 10,000.00 36,750.00 64 Total Budgcted Cost of Goods Sold S 87,662.40 Insight Glass Cumpany Cash Budget For the Month Endine June 30, 2014 Data Table - Part 2 Annual Interest Rate on Debt Debt from Balance Sheet Monthly interest on debt (-1%): $ 65 66 67 69 69 70 71 72 73 74 75 76 77 78 Beginning Cash Balance Cash Receipts A/R balance on May 31, 2014 June Sales, 60% collected in same month Income tax rate General Selline & Admin Depreciation - Building Depreciatiuni - Equipment $ $ 118,800.00 $ 118,800.00 40% 18,750.00 8,000.00 10,000.00 Cash Available $ 118,800.00 Income Tax Payable from Balance Sheet Debt Principal Repayment by Month $ 25,000.00 80 Cash Payments Accounts Payable fr. May 31 to be paid off Jurie Purchases, 70% paid in same munith 41,356.00 May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet A/R collections % in month of sale 60% + - 1007 Type here to search o Pi NG 434 AM 6/12/2020 AutoSave C E BA 213 Insight Glass Project Template Search anthony andriamalala AA File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri AutoSum 11 ~ A A 89 Wrap Text Ceneral 27 O NI EX Insert Delete format X Cut [Copy Format Painter Clipboard Peste Idees BIU Merge & Center Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Font Alignment Number Cels dias 135 f AB D E M N C P a R s T F H For the Month Ending lune 30, 2014 L Debt from Balance Shoot Monthly interest on debt (=1%): $ Beginning Cash Balance Cash Receipts A/R balance on May 31, 2014 lunc Sales, 60% collected in same month 118,800.00 $ 118,800.00 69 70 71 72 73 74 75 76 77 78 79 80 81 B2 Income tax rate General Selling & Admin Depreciation Building Depreciation - Equipment 40% 18,750.00 8,000.00 10,000.00 $ $ $ Cash Available $ 118,800.00 Income Tax Payable from Balance Sheet Debt Principal Repayment by Month $ 25,000.00 A/R collections % in month of sale A/P payment % in month of purchase 60% 70% Cash Payments Accounts Payable I1. May 31 lu be paid oll June Purchases, 70% paid in same month Direct Labor MOH Variable MOH - Fixed Selling & Admin Income Taxes Interest Expense Ending Cash belure Financing . 41 356.00 11,392.00 17,800.00 17,010.00 18,750.00 $ 0.50 Dividends per share Number of shares outstanding $ 106,300.00 $ 12,492.00 Cash Reciepts from Customers 84 85 86 87 89 89 90 91 92 Total Sales Forecast for June 198,000.00 Financing Principal Repayments Dividends declared and paid $ 25,000.00 $ Cash Receipts A/R balance on May 31, 2014 Junc Sales, 60% collected in same month Ending Cash Balance $ (12,508.00) $ 118,800.00 95 96 May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet + - 100% Type here to search OBI NG 434 AM 6/12/2020 AutoSave C E BA 213 Insight Glass Project Template Search anthony andriamalala File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments AutoSum X Cut [Copy Calibri 89 Wrap Text Ceneral ~ A A A 27 O NE EX Insert Delete format Peste EE BIU Idees Merge & Center $ -% -8 Format Painter Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Eding Clipboard Font Alignment Number Cels dias 135 F H J L M N C P a R s T $ 25,000.00 $ AB D E 90 Principal Repayments 91 Dividends declared and paid 92 91 Ending Cash Balance 94 95 96 Cash Receipts A/R balance on May 31, 2014 Junc Sales, 60% collected in same month $ (12,508.00) S 118,800.00 Cash Disbursements Total Direct Materials purchases 59,080 Cash Payments Accounts Payable fr. May 31 to be paid oll June Purchases, 70% paid in same month $ 41.356.00 98 99 100 101 102 103 104 105 106 107 108 109 1:10 111 112 113 114 115 116 117 118 11,392.00 17,800.00 17,010.00 18.750.00 Direct labor MOH - Variable MOH - Fixed Selling & Admin Income Taxes to be paid (from May Interest Expense Principal Repayments Dividends Paid $ $ $ $ $ S S $ 25,000.00 May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet + - 100% Type here to search o Ei NG 434 AM 6/12/2020 Required: 1. Sales budget for June 2. Production budget for June in units. 3. Direct Materials budget for June. 4. Direct Labor budget for June. 5. Manufacturing Overhead budget for June. 6. Cost of Goods Sold budget for June. 7. Cash Receipts & Cash Disbursements budget for June 8. Cash Budget for June 9. Pro-forma financial statements for June a. Multi-step Income Statement i. Link the CGS to the production budget (average cost per unit). ii. Reconcile retained earnings at the bottom of income statement. b. Classified Balance Sheet 10. Management is unsure of the projected sales figure and would like to know how many units need to be sold for the company to break-even. AutoSave CF HD BA 213 Insight Glass Project Template Search anthony andriamalala AA File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri AutoSum 11 ~ A A == 89 Wrap Text General EX 47 O NE Brill Peste X Out LECopy + Format Painter Clipboard Insert Delete Format Idees BIU Merge & Center $ -% Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Tind Filter Select Eding Font Alignment Number Cels dias 024 > A B D G H 1 J K L M N 0 P R S T U V F Proforma Balance Sheet May 31, 2014 June 30, 2014 Change S Current Assets Cash Accounts Receivable Inventory--Raw Material Finished Gouds Total Current Assets 43,000 88,000 15,000 12,480 158,180 $ S $ $ $ (12,508) 79,200 10,000 9,862 86,551 $ S S $ $ (55,508) (8,800) (5,000) (2,618) 71,926) 3 4 5 6 7 6 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 241 25 26 27 28 29 30 31 Land, Duilding and Equipment Land Building (nel of thepreciation) Equipment inet of depreciation) Total Property, Plant & Equipment 100,000 997,624 746,310 1,843,935 $ $ $ 100,000 989,625 736,310 1,825,935 S $ $ $ (8,000) (10,000) (18,000) $ Other Assets Total Assets 125,585 2,128,000 $ $ 125,585 2,038,074 S $ $ (89,926) Liabilities $ Current Liabilities Accounts Payable Income Tax Payable Total Current Liabilities 28,500 20,000 48,580.00 $ $ 17,724 29,435 47,159 S $ $ (10,776) 9,355 (1,421) $ S Long term debt Total Liabilities 575,000 623,580 550,000 597,159 S $ (25,000) (26,421) $ May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet + FI - 100% Type here to search o Ei 3:22 PM 6/12/2020 AutoSave CH BA 213 Insight Glass Project Template Search anthony andriamalala File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments Calibri AutoSum 11 ~ A A 89 Wrap Text General 47 O NE Brill Paste Insert Delete Format Idees BIU H Merge Center $ -% Conditional Format as Cell Formatting Table Styles Styles Painter Sort & Tind Filter Sclect Clear Font Alignment Number Cels Eding dias 024 > A B D E F H - J K L M N 0 P R S T U V $ Land, Huilding and Equipment Land Building (net of depreciation) Equipment inet of depreciation) Tolal Property, Plant & Equipment 100,000 997,625 746,310 1843,935 $ $ $ 100,000 989,625 736,310 1,825,935 $ $ $ S (8,000) (10,000) (18,000) 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Other Assets Total Assets 125,585 2,128,000 $ $ 125,585 2,038,071 $ $ $ S (89,926) Liabilities O $ $ Current Liabilities Accounts Payable Income Tax Payable Total Current Liabilities 28,500 20,080 48,580.00 $ 17,724 29,435 47,159 $ $ $ (10,776) 9,355 (1,421) $ Long term debit Total Liabilities 575,000 623,500 $ $ 550,000 597,159 $ $ (25,000) (26,421) 30 31 32 33 Slockholder's Equity Common Stock: $4 Par Value 100,000 shares outste Additional Paid-In Capital Retained Earnings Total Stockholders' Equity 400,000 600,000 504,420 1,501,120 S $ $ $ $ 400,000 600,000 548,573 1,5118,573 $ 44,153 11.153 $ $ 35 36 37 30 39 Tolal Liabilities and Stockholders' Equity S 2,128,000 $ 2.145,732 $ 17,732 $ 107,657.58 May 31 Balance Sheet Budgets Proforma Income Statement Proforma Balance Sheet + 100% Type here to search o Ei 3:22 PM 6/12/2020