Check Accounting Work- Master Operating Budget & Executive Summary

profilecowboygene
students_original_excel.xlsx
Home>Business & Finance homework help>Accounting homework help>Check Accounting Work- Master Operating Budget & Executive Summary

Sheet1

Calculate The Bottled Water Company's net incone for the new product in the coming year by completing the operating budgets and budgeted income statement that follow
The Bottled Water Company
Sales Bueget
For Year Ended December 31
Quarter
1 2 3 4 Year
Sales in units 40,000 30,000 50,000 55,000 175,000
x Selling price per unit $1 $1 $1 $1 $1
Total sales $40,000 $30,000 $50,000 $55,000 $175,000
The Bottled Water Company
Production Bueget
For Year Ended December 31
Quarter
1 2 3 4 Year
Sales in units 40,000 30,000 50,000 55,000 175,000
Plus desired units of ending finished goods inventory*
3,000 5000 5500 6,000 6,000
Desired total units 43,000 35,000 55,500 61,000 181,000
Less desired units of beginning finished goods inventory (t)
4,000 3,000 5,000 5,500 4,000
Total production units 39,000 32,000 50,500 55,500 177,000
* Desired units of ending finished goods inventory = 10% of next quarter's budgeted sales
(t) Desired units of beginning finished goods inventory = 10% of current quarter's budgeted sales
The Bottled Water Company
Direct Materials Budget
For Year Ended December 31
Quarter
1 2 3 4 Year
Total production units 39,000 32,000 50,500 55,500 177,000
x 20 ounces per unit 20 20 20 20 20
Total production needs in ounces
780,000 640,000 1,010,000 1,110,000 3,540,000
Plus desired ounces of ending direct materials inventory*
128,000 202,000 222,000 240,000 240,000
908,000 842,000 1,232,000 1,350,000 3,780,000
Less desired ounces of beginning direct materials inventory (t)
156,000 128,000 202,000 222,000 156,000
Total ounces of direct materials to be purchased
752,000 714,000 1,030,000 1,128,000 3,624,000
x Cost per ounce 0.01 0.01 0.01 0.01 0.01
Total cost of direct materials purchases
7,520 7,140 10,300 11,280 36,240
*Desired ounces of ending direct materials inventory = 20% of next quarter's budgeted production needs in ounces
(t) Desired ounces of beginning direct materials inventory = 20% of current quarter's budgeted production needs in ounces.
The Bottled Water Company
Direct labor budget
For Year Ended December 31
Quarter
1 2 3 4 Year
Total production units 39,000 32,000 50,500 55,500 177,000
x Direct labor hours per unit 0.001 0.001 0.001 0.001 0.001
Total direct labor hours 39 32 50.5 55.5 177
x Direct labor cost per hour $8 $8 $8 $8 $8
Total direct labor cost $312 $256 $404 $444 $1,416
The Bottled Water Company
Overhead budget
For Year Ended December 31
Quarter
1 2 3 4 Year
Variable overhead costs
Factory supplies ($.01) $390 $320 $505 $555 $1,770
Employee benefits ($.05) $1,950 $1,600 $2,525 $2,775 $8,850
Inspection ($.01) $390 $320 $505 $555 $1,770
Maintenance and repair ($.02) $780 $640 $1,010 $1,110 $3,540
Utilities ($.01) $390 $320 $505 $555 $1,770
Total variable overhead costs $3,900 $3,200 $5,050 $5,550 $17,700
Total fixed overhead costs $1,500 $1,500 $1,500 $1,500 $6,000
Total overhead costs $5,400 $4,700 $6,550 $7,050 $23,700
Note: The figures in the parentheses are variable costs per unit
The Bottled Water Company
Selling and administrative expense budget
For Year Ended December 31
Quarter
1 2 3 4 Year
Variable selling and administrative expenses
Delivery expenses ($.01) $400 $300 $500 $550 $1,750
Sales commissions ($.02) $800 $600 $1,000 $1,100 $3,500
Accounting ($.01) $400 $300 $500 $550 $1,750
Other administrative expenses ($.01) $400 $300 $500 $550 $1,750
Total variable selling and administrative expenses
$2,000 $1,500 $2,500 $2,750 $8,750
Total fixed selling and administrative expenses
$5,000 $5,000 $5,000 $5,000 $20,000
Total selling and administrative expenses
$7,000 $6,500 $7,500 $7,750 $28,750
Note: The figures in the parentheses are variable costs per unit
The Bottled Water Company
Cost of goods manufactured budget
For Year Ended December 31
Direct materials used
Direct materials inventory, beginning $1,560
Purchases $36,240
Cost of direct materials available for use $37,800
Less direct materials inventory, ending $2,400
Cost of direct materials used $35,400
Direct labor costs $1,416
Overhead costs $23,700
Total manufacturing costs $60,516
Work in process inventory, beginning * $0
Less work in process inventory, ending * $0
Cost of goods manufactured $60,516
Manufactured Cost per Unit = Cost of Goods Manufactured / Units Produced $0.34
*It is the company's policy to have no units in process at the end of the year
The Bottled Water Company
Budgeted income statement
For Year Ended December 31
Sales $175,000
Cost of goods sold
Finished goods inventory, beginning 1360
Cost of goods manufactured $60,516
Cost of finished goods available for sale 61876
Less finished goods inventory, ending 2040
Cost of goods sold 59836
Gross margin $115,164
Selling and administrative expenses $28,750
Income from operations $86,414
Income taxes expense (30%)* $25,924
Net income $60,490
*The figure in parentheses is the company's income tax rate.

Sheet2

Sheet3