Master Budget Case, 50 Points
Required: Prepare the budgets shown below using Microsoft Excel. Be sure to use links and formulas as much as possible. Do not prepare the budgets by hand and then merely input the numbers into Excel. Make sure to adjust the width of your columns so your numbers are readable rather than seeing a row of the ##### symbols. You should check print preview. You may place more than one budget on a page, but each budget should be contained on one page rather than continuing at the top of another page. Bring a printout of your budgets to class. While you will need information from months that are not a part of the third quarter, your budgets should NOT show the information from months that are not included in the third quarter.
1. Sales Budget
2. Schedule of Expected Cash Receipts from Sales
3. Production Budget
4. Direct Materials Budget
5. Schedule of Expected Cash Payments for Materials
6. Direct Labor Budget
7. Manufacturing Overhead Budget (including calculating the total (variable and fixed overhead combined) predetermined overhead rate based on direct labor hours and showing it at the bottom of the budget)
8. Ending Finished Goods Inventory Budget (using absorption costing) (as of September 30, 2011, only and not per month) 9. Selling and Administrative Expenses Budget
10. Cash Budget
11. Budgeted Income Statement (Multi-Step Form) (not by month—for the third quarter as a whole only). The income tax rate is a flat 40 percent. 12. Budgeted Balance Sheet (Classified) (as of September 30, 2011, only)
Prepare the budgets shown above for Blanco Company for the Third Quarter of 2011 by month (July, August, September, and for the Third Quarter as a Whole) unless instructed otherwise above.
Facts: Blanco Company sells one product—the White Gadget. (Blanco is Spanish for white.) The company expects the sales price to remain steady at $947 per unit during the third quarter of 2011. Blanco Company expects the following unit sales for the months shown below:
24,000 (not a part of the third quarter)
29,000 (not a part of the third quarter)
Blanco Company expects to make 36 percent of these sales for cash. Cash sales appear on the Schedule of Expected Cash Receipts from Sales in the month of sale. The remaining 64 percent of sales will be on account. Of the 64 percent of total sales that it makes on account, Blanco Company expects to collect 70 percent in the same month as the sale and the remaining 30 percent in the month following the month of sale. Blanco Company expects to have accounts receivable on July 1, 201, in the amount of $4,000,000, which it expects to collect in July 2011. Note that this beginning amount is to be taken as a given. It may not conform to percentages that you are to use to calculate budgeted accounts receivable and collections on accounts receivable.
Blanco Company wants to have an ending inventory of finished goods each month equal to 12 percent of the following month’s expected sales. The beginning inventory of finished goods on July 1, 2011, is expected to be 4,500 units. Note that this beginning amount is to be taken as a given. It may not conform to percentages that you are to use to calculate budgeted inventory.
To make its product, Blanco Company uses one raw material called gizmo. To make each unit of its product, Blanco Company uses five (5) pounds of gizmo. The cost of gizmo is $14.00 per pound. Blanco Company has a long-term contract with the supplier so the $14.00 price will be the same for all of calendar year 2011.
Blanco Company wants to keep an ending inventory of gizmo each month equal to 15 percent of the following month’s production needs (units to be produced multiplied by the pounds required per unit). Blanco Company expects to have a beginning inventory of gizmo on July 1, 2011, of 20,000 pounds. Note...
Please join StudyMode to read the full document