70.2K

Verified Solution

Link Copied!

I need help answering question 7 and 8. I am not sure where the numbers are supposed to come from in the problem... is it part of something that has already been calculated ... Which numbers? or how do you calculate the answers.... Please show me how in Excel.

Calculate The Bottled Water Company's net income for the new product in the coming year by completing the operating budgets and budgeted income statement that follow.
						1. Sales Budget:
	Quarter
	1	2	3	4	YEAR
Sales in Units	40,000	30,000	50,000	55,000	175,000
X selling price/unit	$1	$1	$1	$1	$1
Total Sales	$40,000	$30,000	$50,000	$55,000	$175,000
2. Production budget:
	Quarter
	1	2	3	4	YEAR
Sales in Units	40,000	30,000	50,000	55,000	175,000
Plus Desired units of ending finsished goods inventory*	3000	5,000.0	5,500.0	6000	6000
Desired total units	43,000	35,000.0	55,500.0	61,000.0	181,000.0
Less desire units of beginning finished goods inventory **	4000	3000	5000	5500	4000
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.
** Desired units of beginning finished goods inventory = 10% of current quarter's budgeted sales.
3. Direct materials purchases budget:
	Quarter
	1	2	3	4	YEAR
Total Production Units	39,000	32,000	50,500	55,000	176,500
x 20 ounces per unit	20	20	20	20	20
total production needs in ounces	780,000	640,000	1,010,000	1,100,000	3,530,000
plus desired ounces of ending direct materials inventory *	128,000	101,000	110,000	240,000	240,000
=	908,000	741,000	1,120,000	1,340,000	3,770,000
less desired ounces of beginning direct materils inventory **	156,000	128,000	101,000	110,000	156,000
total ounces of direct materials to be purchased	752,000	128,000.0	202,000.0	220,000.0	706,000.0
X cost per ounce	$0.01	$0.01	$0.01	$0.01	$0.01
Total Cost of direct materials purchases	$7,520.00	$1,280.00	$2,020.00	$2,200.00	$7,060.00
* Desired ounces of ending direct materials inventory = 20% of next wuarter's budgeted production needs in ounces.
** Desired ounces of beginning direct materials inventory = 20% of current quarter's budgeted production needs in ounces
4. Direct labor budget:
	Quarter
	1	2	3	4	YEAR
Total production Units	39000	32,000	50,500	55,000	176,500
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	176.5
x Direct labor cost per hour	$8	$8	$8	$8	$8
Total direct labor cost	$312	$256	$404	$440	$1,412
5. Overhead Budget
	Quarter
	1	2	3	4	YEAR
Variable overhead costs
Facrotry supplies ($.01)	390	320.00	505.00	550.00	1,765.00
Employee benefits ($.05)	1950	1,600.00	2,525.00	2,750.00	8,825.00
Inspection ($.01)	390	320.00	505.00	550.00	1,765.00
Maintenance and repair ($.02)	780	640.00	1,010.00	1,100.00	3,530.00
Utilities ($.01)	390	320.00	505.00	550.00	1,765.00
Total variable overhead costs	3900	3200	5050	5500	17650
Total fixed overhead costs	1500	1500	1500	1500	6000
Total overhead costs	5400	4700	6550	7000	23650
6. Selling and administrative expense budget
	Quarter
	1	2	3	4	YEAR
Variable selling and administrative expenses
Delivery expenses ($.01)	$400	$320	$505	$550	$1,765
Sales commisions ($.02)	$800	640.00	1,010.00	1,100.00	3,530.00
Accounting ($.01)	$400	$320	$505	$550	$1,765
Other administrative expenses ($.01)	$400	$320	$505	$550	$1,765
Total variable selling and administrative expenses	$2,000	$1,600	$2,525	$2,750	$8,825
Total fixed selling and administrative expenses	$5,000	$5,000	$5,000	$5,000	$5,000
Total selling and administrative expenses	$7,000	$6,600	$7,525	$7,750	$13,825
7. Cost of goods manufactured budget
	Quarter
	1	2	3	4	YEAR
Direct Materials Used
direct materials inventory, beginning
purchases
cost of direct materials available for use
less direct materials incentory ending
cost of direct materials used
direct labor costs
overhead scosts
total manufacturing costs					0
work in process inventory, beginnning					0
less work in process inventory, ending
costs of goods manufactured
Manufactured Cost per Unit = cost of goods manufactured / Units Produduced
* It is the companies policy to have no units in process at the end of the year
8. Budgeted income statement
Sales		?
cost of goods sold
finished goods inventory, beginning	?
cost of goods manufactured	?
costs of finished goods available for sale	?
less finished goods incentory, ending	?
cost of goods sold		?
Gross Margin		?
Selling and administrative expenses		?
Income from operations		?
Incomes taxes expense (30%)		?
Net income		?

Answer & Explanation Solved by verified expert

See Answer