Excel Homework...................................................

Information: Wild Wood Company's management asks you to prepare its master budget using the following information. The budget is to cover the months of April, May, and June of 2015. The followiing informa has been summarized in a "data table" on the Budget Data tab which also contains a Balance Sheet as o March 31, 2015. a. Sales for March total 10,000 units. Each month's sales are expected to exceed the prior month's resu by 5%. The product's selling price is $25 per unit. b. Company policy calls for a given month's ending inventory to equal 80% of the next month's expecte unit sales. The March 31 inventory is 8,400 units, which complies with the policy. The purchase price is per unit. c. Sales representatives' commissions are 12.5% of sales and are paid in the month of the sales. The sa manager's monthly salary will be $3,500 in April and $4,000 per month thereafter. d. Monthly general and administrative expenses include $8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable. e. The company expects 30% of sales to be for cash and the remaining 70% on credit. Receivables are collected in full in the month following the sale (none is collected in the month of the sale). f. All merchandise purchases are on credit, and no payables arise from any other transactions. One month's purchases are fully paid in the next month. g. The minimum ending cash balance for all months is $50,000. If necessary, the company borrows eno cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1 each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess be applied to repaying the short-term notes payable balance. h. Dividends of $100,000 are to be declared and paid in May. i. No cash payments for income taxes are to be made during the second calendar quarter. Income taxe will be assessed at 35% in the quarter. j. Equipment purchases of $55,000 are scheduled for June. g the following The followiing information ins a Balance Sheet as of d the prior month's results he next month's expected y. The purchase price is $15 nth of the sales. The sales er. salaries, $5,000 Required: Prepare the following budgets and required: 1. Sales budget, including budget 2. Purchases budget. 3. Selling expense budget. 4. General and administrative exp 5. Expected cash receipts from cu balance of accounts receivable. 6. Expected cash payments for pu balance of accounts payable. 7. Cash budget. credit. Receivables are of the sale). 8. Budgeted income statement, b and budgeted balance sheet. er transactions. One Use the templates provided on th greatly facilitated if you use Excel full use of the information in the d tab through the use of "cell refere e company borrows enough n interest payment of 1% at e minimum, the excess will ar quarter. Income taxes e following budgets and other financial information as udget, including budgeted sales for July. ses budget. expense budget. l and administrative expense budget. ed cash receipts from customers and the expected June 30 accounts receivable. ed cash payments for purchases and the expected June 30 accounts payable. udget. ed income statement, budgeted statement of retained earnings, ted balance sheet. mplates provided on the relevant tabs. Your work will be ilitated if you use Excel forumlas for all computations and make the information in the data table provided in the Budget Data h the use of "cell referencing." BUDGETING DATA: Product purchase price (per unit) March 2015 sales (units) Monthly projected percentage increase Product sales price (per unit) Inventory, 3/31/15 (units) Target inventory percent of next month's sales $ $ 15 10,000 5% 25 8,400 80% Cash sales percent Credit sales percent Collections: First month after sale 100% Payments for purchases: First month after purchase 100% Sales commissions Manager's salary (April 2015) Manager's salary (monthly after April 2015) Administrative salaries (monthly) Depreciation expense (monthly) Interest on long-term note payable (monthly) 30% 70% $ $ $ $ 12.5% 3,500 4,000 8,000 5,000 0.9% Equipment purchases planned: June 2015 $ 55,000 Dividends planned for May 2015 $ 100,000 Interest rate on bank loans (monthly) Minimum ending cash balance $ 1% 50,000 Income tax rate 35% WILD WOOD COMPANY Estimated Balance Sheet March 31, 2015 Assets Cash Accounts receivable Inventory Total current assets Equipment Less accumulated depreciation Total assets $ $ $ 50,000 175,000 126,000 351,000 $ 390,000 741,000 480,000 90,000 Liabilities and Equity Accounts payable Short-term notes payable Total current liabilities Long-term note payable Total liabilities Common stock Retained earnings Total stockholders' equity Total liabilities and equity $ 156,000 12,000 $ $ 168,000 200,000 368,000 235,000 138,000 $ 373,000 741,000 Part 1 WILD WOOD COMPANY Sales Budget April, May, and June, 2015 April May June Prior period unit sales Plus 5% growth Projected unit sales Selling price per unit Projected sales (in dollars) Total for quarter (units and dollars) Part 2 WILD WOOD COMPANY Merchandise Purchases Budgets April, May, and June, 2015 April May June Next month's budgeted unit sales Ending inventory percent Desired ending inventory Current period budgeted unit sales Required units of available inventory Less beginning inventory Units to be purchased Budgeted cost per unit Projected purchases Part 3 WILD WOOD COMPANY Selling Expenses Budgets April, May, and June, 2015 April Budgeted sales Sales commission rate Sales commissions Manager's salary Total selling expenses May June Part 4 WILD WOOD COMPANY General and Administrative Expense Budgets April, May, and June, 2015 April Administrative salaries Depreciation Interest on long-term note payable Totals May June July Quarter Quarter ets Quarter Part 7 WILD WOOD COMPANY Cash Budgets April, May, and June, 2015 April Beginning cash balance Cash receipts* Total cash available Cash payments: Payments merchandise** Sales commissions Salaries: Sales Administrative Dividends Equipment purchase Interest on long-term note Interest on short-term notes Total cash payments Preliminary balance Loan activity: Additional loan Loan repayment Ending cash balance Ending balance short-term notes May June Part 5 * Budgeted cash receipts from sales: April May June April May June Total budgeted sales Ending accounts receivable (70%) Cash receipts: Cash sales (30% of budgeted sales) Collection of prior receivables Total cash to be collected Part 6 ** Budgeted cash payments to suppliers: Cash payments (prior month purchases) Expected June 30 balance of Accounts Payable (June purchases) Quarter Quarter Part 8 WILD WOOD COMPANY Budgeted Income Statement For Quarter Ended June 30, 2015 Sales Cost of goods sold (33,101 units @ $15) Gross profit Operating expenses Sales commissions Sales salaries Adminstrative salaries Depreciation Interest on long-term note Interest on short-term notes Total operating expenses Net income before taxes Income taxes Net incomes after taxes WILD WOOD COMPANY Budgeted Statement of Retained Earnings For Quarter Ended June 30, 2015 Beginning retained earnings Net income Total Less: Cash dividends Ending retained earnings WILD WOOD COMPANY Budgeted Balance Sheet June 30, 2015 Assets Cash Accounts receivable Inventory Total current assets Equipment Less accumulated depreciation Total assets Liabilities and Equity Accounts payable Short-term notes payable Income taxes payable Total current liabilities Long-term notes payable Total liabilities Common stock Retained earnings Total stockholders' equity Total liabilities and equity