master budget not adding up! please help!

managerial accounting master budget

Merge Center Com 15 20 16 cash credit $9,000.0 $.000.0 $32,000 $5,000 $12.000 $14.000. 13000 $27,000 23 May $12,000.0 $12.00 $40.000 30 budgeted cast of Goods sold 31 End 32 Total Need Bere Purchase 24 hranna Arruti Dovatie Cart Ches t er Schedule of Expected G r een 1379 Beginning Accounts Payable April May June Total BCIC Selling and Administrative penes For quartier de lune 11, 2018 $4,000 $2,000 $15.000 11 Variable sing and distrative expenses 1000 53 Faed selling and administrative expense 54 Wages and salaries 55 Insurance 56 Depreciation 57 Utilities 58 Advertising 59 Misc. BO Rent 51 Total15. AE Expense 62 Total 5 AE Expense 5 prev. varet 14 L a rent 65 tenance 6 le depreciation 67 Cash disements for sing and admin TO $25.550 $100 10000 15.00 $21,200 Sheet1 isan chega a 27 El nostro tertaklu Formining Table Style Clipboard Alignment 109 - Cash budget Quarter ended June 31, 2018 April Beg. Cash Add: cash collections 5 Total cash available $16,300 $46,400 $62.700 $15,750 $40,000.0 $55,750 $60.400.0 $45,600 $146,800 $192,400 23750 $23,200 21000 $21,200 75750 65600 5000 1500 48450 $14,250 42200 147850 $44,550 7 Inventory * Expenses 79 Equipment 30 Dividends 81 Total Cash Disbursements 82 Excess in cash 83 84 Financing 85 Borrowings 86 Repayments 87 interest 88 Total Financing 89 Ending Cash Balance $0 9 88 89 $14,250 $16,750 $44,550 Budgeted Income Statement For Quarter Ended June 31, 2018 93 Sales 94 COGS 95 Gross Margin 96 Selling and Admin Expense 97 Net Operating income 98 Interest Expense 99 Net Income $150,000 70000 S80,000 $70,650 $9,350 $9, 150 100 Sheet Eram cum canar... Ell masturbandoutstan. DE Net Income Budgeted Balance Sheet June 31. 2018 $16,750 $22,400.0 5000 2250 80000 $19.750.0 3000 7000 34 Assets 05 Cash 06 Accounts Receivable 107 Inventory 108 Prepaid Insurance 109 Land, Building, Equip 110 111 112 Total Assets: 113 114 115 Liabilities: Accounts payable Dividends payable Rent payable Loan payable Stockholder equity Capital equity Retained ning 54400 $50,000 ,150.0 $1 $126,400 Total Liabilities and Stockholders' Equity 116 117 118 119 120 121 122 124 125 127 128 120 130 Sheett Ready Tanagerial Accounting laster Budget Assignment all, 2019 came Ben Tounshend The BC Company needs a master budget for the three months beginning April 1. 2018. The company retails widgets. The 2018 budget should be based on the following information. An ending minimum cash balance of $10,000 each month is required. Sales are forecasted at an average selling price of 58 per widget. Merchandise costs are S4 per widget Currently the company maintains an ending inventory balance equal to 20 of the next month's projected cost of goods sold Purchases during any given month are paid half in the month of purchase and half during the following month. Sales are 20% cash and 80 on credit payable within 30 days, but experience has shown that 60% of monthly credit sales is collected in the current month, 40% in the next month. $15.000 Monthly operating expenses are as follows: Wages and salaries Insurance expired Depreciation Utilities Advertising Miscellaneous Rent 1.200 1.000 300 500 400 per month 10% of monthly sales All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $400 is paid at the beginning of each month, and the additional 10% of sales is paid in the month following the sales. The company plans to buy some new equipment for $5,000 cash in June. Cash dividends of $1,500 are to be paid quarterly, beginning April 15. Dividends are declared on the 15th of the last month in the calendar quartier BC) has an established line of credit with its bank, Third Fifth National Money can be borrowed and repaid in multiples of $1,000, at an interest rate of 6% per annum. Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid. Assume that borrowing occurs at the beginning and repayments at the end of the months in question. Money is never borrowed at the beginning and repaid at the end of the same month Compute interest to the nearest dollar. Balance Sheet March 31, 2018 Assets Cash Accounts receivable (net) Inventory Prepaid insurance Land, Building. Equipment (net) $16,300 19,200 4,000 1,800 75.000 Liabilities Accounts payable (inventory) Dividends payable Rent payable $13,750 1.500 6.000 21,250 Stockholders' Equity Capital stock (Si par value) Retained earnings Total Liabilities & Stockholders' Equity 54,400 40.650 S116,300 Total assets $116,300 April S40,000 Recent and forecasted sales: January $45,000 February S40,000 June $50,000 $70,000 March July $60,000 560,000 May equired: Prepare a master budget, using Excel, and all supporting schedules (including sales) for the months Anil through June, 2018. Penure the dested Income Statement and al l or the quarter ended un 2020 the budgeted Balance Sheet at June 30, 2018 te: The preparation of the Excel spreadsheet must be individual work Solutions must be turned ectronically Merge Center Com 15 20 16 cash credit $9,000.0 $.000.0 $32,000 $5,000 $12.000 $14.000. 13000 $27,000 23 May $12,000.0 $12.00 $40.000 30 budgeted cast of Goods sold 31 End 32 Total Need Bere Purchase 24 hranna Arruti Dovatie Cart Ches t er Schedule of Expected G r een 1379 Beginning Accounts Payable April May June Total BCIC Selling and Administrative penes For quartier de lune 11, 2018 $4,000 $2,000 $15.000 11 Variable sing and distrative expenses 1000 53 Faed selling and administrative expense 54 Wages and salaries 55 Insurance 56 Depreciation 57 Utilities 58 Advertising 59 Misc. BO Rent 51 Total15. AE Expense 62 Total 5 AE Expense 5 prev. varet 14 L a rent 65 tenance 6 le depreciation 67 Cash disements for sing and admin TO $25.550 $100 10000 15.00 $21,200 Sheet1 isan chega a 27 El nostro tertaklu Formining Table Style Clipboard Alignment 109 - Cash budget Quarter ended June 31, 2018 April Beg. Cash Add: cash collections 5 Total cash available $16,300 $46,400 $62.700 $15,750 $40,000.0 $55,750 $60.400.0 $45,600 $146,800 $192,400 23750 $23,200 21000 $21,200 75750 65600 5000 1500 48450 $14,250 42200 147850 $44,550 7 Inventory * Expenses 79 Equipment 30 Dividends 81 Total Cash Disbursements 82 Excess in cash 83 84 Financing 85 Borrowings 86 Repayments 87 interest 88 Total Financing 89 Ending Cash Balance $0 9 88 89 $14,250 $16,750 $44,550 Budgeted Income Statement For Quarter Ended June 31, 2018 93 Sales 94 COGS 95 Gross Margin 96 Selling and Admin Expense 97 Net Operating income 98 Interest Expense 99 Net Income $150,000 70000 S80,000 $70,650 $9,350 $9, 150 100 Sheet Eram cum canar... Ell masturbandoutstan. DE Net Income Budgeted Balance Sheet June 31. 2018 $16,750 $22,400.0 5000 2250 80000 $19.750.0 3000 7000 34 Assets 05 Cash 06 Accounts Receivable 107 Inventory 108 Prepaid Insurance 109 Land, Building, Equip 110 111 112 Total Assets: 113 114 115 Liabilities: Accounts payable Dividends payable Rent payable Loan payable Stockholder equity Capital equity Retained ning 54400 $50,000 ,150.0 $1 $126,400 Total Liabilities and Stockholders' Equity 116 117 118 119 120 121 122 124 125 127 128 120 130 Sheett Ready Tanagerial Accounting laster Budget Assignment all, 2019 came Ben Tounshend The BC Company needs a master budget for the three months beginning April 1. 2018. The company retails widgets. The 2018 budget should be based on the following information. An ending minimum cash balance of $10,000 each month is required. Sales are forecasted at an average selling price of 58 per widget. Merchandise costs are S4 per widget Currently the company maintains an ending inventory balance equal to 20 of the next month's projected cost of goods sold Purchases during any given month are paid half in the month of purchase and half during the following month. Sales are 20% cash and 80 on credit payable within 30 days, but experience has shown that 60% of monthly credit sales is collected in the current month, 40% in the next month. $15.000 Monthly operating expenses are as follows: Wages and salaries Insurance expired Depreciation Utilities Advertising Miscellaneous Rent 1.200 1.000 300 500 400 per month 10% of monthly sales All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $400 is paid at the beginning of each month, and the additional 10% of sales is paid in the month following the sales. The company plans to buy some new equipment for $5,000 cash in June. Cash dividends of $1,500 are to be paid quarterly, beginning April 15. Dividends are declared on the 15th of the last month in the calendar quartier BC) has an established line of credit with its bank, Third Fifth National Money can be borrowed and repaid in multiples of $1,000, at an interest rate of 6% per annum. Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid. Assume that borrowing occurs at the beginning and repayments at the end of the months in question. Money is never borrowed at the beginning and repaid at the end of the same month Compute interest to the nearest dollar. Balance Sheet March 31, 2018 Assets Cash Accounts receivable (net) Inventory Prepaid insurance Land, Building. Equipment (net) $16,300 19,200 4,000 1,800 75.000 Liabilities Accounts payable (inventory) Dividends payable Rent payable $13,750 1.500 6.000 21,250 Stockholders' Equity Capital stock (Si par value) Retained earnings Total Liabilities & Stockholders' Equity 54,400 40.650 S116,300 Total assets $116,300 April S40,000 Recent and forecasted sales: January $45,000 February S40,000 June $50,000 $70,000 March July $60,000 560,000 May equired: Prepare a master budget, using Excel, and all supporting schedules (including sales) for the months Anil through June, 2018. Penure the dested Income Statement and al l or the quarter ended un 2020 the budgeted Balance Sheet at June 30, 2018 te: The preparation of the Excel spreadsheet must be individual work Solutions must be turned ectronically