The BCJ Company needs a master budget for the three months beginning October 1, 2016. The company retails widgets. The 2016 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 $10 per widget. Merchandise costs are $6 per widget. Currently, the company maintains an ending inventory balance equal to 10% 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 30% cash and 70% on credit (payable within 30 days), o experience has shown that 70% of monthly credit sales is collected in the current month, 30% in the next month. Monthly operating expenses are as follows: Wages and salaries Insurance expired Depreciation Utilities Advertising 400 Miscellaneous 500 Rent month+ 10% of monthly sales. $20,000 200 2.000 1,000 800 per Additonal information: . All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $800 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 $15,000 in November and $8,000 in December. Dividends are declared on the 15th of the last month in the calendar quarter and paid the following month. Dividends declared for the quarter ending December 31, 2016, which will be paid in January, 2017, are $5,000. BC) has an established line of credit with its bank. Third Fifth Money can be borrowed and repaid in multiples of $1.000 at an interest rate 6% per annum Management wants to minimize h owing and repay rapidly Interest is calculated and paid at the end of each quatter Assume that mowing us at the beginning and payments at the end of the months in question Money is never borrowed at the beginning and repand at the end of the same month -Compute interest to the nearest dollar Balance Sheet September 10, 2016 Assets Liabilities Cash S18,700 Accounts payable inventory $13.750 Accounts receivable (net) 12.600 Dividends payable 4,000 Inventory 4.200 Rent payable Prepaid insurance 1.800 37,300 23.750 Land, Building. Equipment (net) 85.000 Stockholders Equity Capital stock ( Spor value) 4.400 Retained earings 44.150 Total assets $122100 Total Liabilities $122.300 And Stockholders Equity Recent and forecasted sales September SHILO October 572 000 November $1,000 December $90,000 $60,000 REQUIRED: Prepare a master budget, using Excel, and all supporting schedules (including sales) for the months October, 2016 through December, 2016 Prepare the budgeted Income Statement and Statement of Cash Flows for the quarter ended December 31, 2016 and the budete Balance Sheet December 31, 2016 The preparation of the Excel spreadsheet must be individual Solutions must be turned in with a copy submitted electronically via email. BC) has an established line of credit with its bank. Third Fifth Money can be borrowed and repaid in multiples of $1.000 at an interest rate 6% per annum Management wants to minimize h owing and repay rapidly Interest is calculated and paid at the end of each quatter Assume that mowing us at the beginning and payments at the end of the months in question Money is never borrowed at the beginning and repand at the end of the same month -Compute interest to the nearest dollar Balance Sheet September 10, 2016 Assets Liabilities Cash S18,700 Accounts payable inventory $13.750 Accounts receivable (net) 12.600 Dividends payable 4,000 Inventory 4.200 Rent payable Prepaid insurance 1.800 37,300 23.750 Land, Building. Equipment (net) 85.000 Stockholders Equity Capital stock ( Spor value) 4.400 Retained earings 44.150 Total assets $122100 Total Liabilities $122.300 And Stockholders Equity Recent and forecasted sales September SHILO October 572 000 November $1,000 December $90,000 $60,000 REQUIRED: Prepare a master budget, using Excel, and all supporting schedules (including sales) for the months October, 2016 through December, 2016 Prepare the budgeted Income Statement and Statement of Cash Flows for the quarter ended December 31, 2016 and the budete Balance Sheet December 31, 2016 The preparation of the Excel spreadsheet must be individual Solutions must be turned in with a copy submitted electronically via email
