Complete the "worksheet" tab using the information on the "input data" tab. Do not change any of the formulas. 
Managerial Accountin Chapter 20 - Master Budget and Planning exercise Input data Months October, November and December Sales Budget Selling price per unit $60.00 Monthly Sales Forecast: 1000; 800; 1400 (January 900) Production Budget Target ratio of ending inventory to next month's sales-90% Beginning inventory at 9/30 1,010 Direct Materials Budget Material required per unit 1/2 pound Beginning inventory- 178 pounds Ending inventory materials target set at 50% of next month's production January production target set at 990 Material price per pound $20.00 Direct Labor Budget Labor hours required per unit 1/4 hour Labor rate $12 Factory Overhead Rate Variable overhead rate per unit $2.50 Budgeted fixed overhead $1,500 per month Selling and Administration Expense (Period costs Sales commissions-10% of sales Sales manager salary- $2,000 per month Other administration costs per month $4,500 Cash Flow Budget Receivable balance at 9/30 - $25200 Cash sales-40% Credit sales-60% in the month following the sale Accounts payable balance at 9/30$7,060 Purchases are all on credit and 100% is paid the following month Beginning cash balance at 9/30 is $20,000 A $3,000 cash dividend will be paid in November Equipment purchase of $25,000 budgeted for December Tax liability from the prior quarter $20,000 to be paid in October Pays 40% tax rate the month following the quarter Bank loan agreement - borrow if cash is below $20,000 at month end Loan paid down if month end cash balance exceeds $20,000 Loan balance at 9/30 is $10,000 interest charge is 1% per month based on prior month balance Managerial Accountin Chapter 20 - Master Budget and Planning exercise Input data Months October, November and December Sales Budget Selling price per unit $60.00 Monthly Sales Forecast: 1000; 800; 1400 (January 900) Production Budget Target ratio of ending inventory to next month's sales-90% Beginning inventory at 9/30 1,010 Direct Materials Budget Material required per unit 1/2 pound Beginning inventory- 178 pounds Ending inventory materials target set at 50% of next month's production January production target set at 990 Material price per pound $20.00 Direct Labor Budget Labor hours required per unit 1/4 hour Labor rate $12 Factory Overhead Rate Variable overhead rate per unit $2.50 Budgeted fixed overhead $1,500 per month Selling and Administration Expense (Period costs Sales commissions-10% of sales Sales manager salary- $2,000 per month Other administration costs per month $4,500 Cash Flow Budget Receivable balance at 9/30 - $25200 Cash sales-40% Credit sales-60% in the month following the sale Accounts payable balance at 9/30$7,060 Purchases are all on credit and 100% is paid the following month Beginning cash balance at 9/30 is $20,000 A $3,000 cash dividend will be paid in November Equipment purchase of $25,000 budgeted for December Tax liability from the prior quarter $20,000 to be paid in October Pays 40% tax rate the month following the quarter Bank loan agreement - borrow if cash is below $20,000 at month end Loan paid down if month end cash balance exceeds $20,000 Loan balance at 9/30 is $10,000 interest charge is 1% per month based on prior month balance
