Skills building

canada2016

WRDFinMan14e_PR7-3A1.xlsx

Home >Business & Finance homework help >Accounting homework help >Skills building

Pr. 21(7)-3A

Problem 21(7)-3A

Name:

Section:

# N-box Incorrects due to blanks COUNTIF(B15:AT24," ")

138

Score:

# N-box +B-box corrects COUNTIF(B15:AT24," ")

Key Code:

[Key code here]

Total SUM(AD13:AD15)

Instructions

138

Answers are entered in the cells with gray backgrounds.

Percentage =(AD16-AD13-AD14)/AD16

Cells with non-gray backgrounds are protected and cannot be edited.

An asterisk (*) will appear to the right of an incorrect entry.

Notes:

If number-entry box is blank (this would be an incorrect answer for N-boxes), error check returns two spaces, " "

If number-entry or blank-entry box is incorrect, returns "*"

If number-entry or blank-entry box is correct, returns single space, " "

Feathered Friends Inc.

Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct.

Sales Budget

Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color.

For the Month Ending December 31

Unit Sales

Unit Selling

Total

Product

Volume

Price

Sales

Bird house

Bird feeder

Total revenue from sales

Feathered Friends Inc.

Production Budget

For the Month Ending December 31

Units

Bird

House

Feeder

Expected units to be sold

Estimated inventory, Dec. 31

Estimated inventory, Dec. 1

Total

Desired inventory, Dec. 31

Desired inventory, Dec. 1

Total units to be produced

Feathered Friends Inc.

Direct Materials Purchases Budget

For the Month Ending December 31

Wood

Plastic

(feet)

(lbs.)

Total

Required units for production:

Bird house

Bird feeder

Total

Total units to be purchased

Unit price

Total direct materials to be purchased

Feathered Friends Inc.

Direct Labor Cost Budget

For the Month Ending December 31

Fabrication

Assembly

Department

Total

Hours required for production:

Bird house

Bird feeder

Total

Hourly rate

Total direct labor cost

Feathered Friends Inc.

Factory Overhead Cost Budget

For the Month Ending December 31

Indirect factory wages

Depreciation of plant and equipment

Power and light

Insurance and property tax

Total

Finished goods inventory, Dec. 1

Finished goods inventory, Dec. 31

Work in process, Dec. 1

Feathered Friends Inc.

Work in process, Dec. 31

Cost of Goods Sold Budget

Direct materials inventory, Dec. 1

For the Month Ending December 31

Direct materials inventory, Dec. 31

Direct materials:

Mark Sears: Complete supporting calculations below and enter appropriate amount here.

Direct materials purchases

Cost of direct materials available for use

Cost of direct materials placed in production

Direct labor

Factory overhead

Total manufacturing costs

Total work in process during period

Cost of goods manufactured

Cost of finished goods available for sale

Mark Sears: Complete supporting calculations below and enter appropriate amount here as a negative value.

Mark Sears: Complete supporting calculations below and enter appropriate amount here.

Mark Sears: Complete supporting calculations below and enter appropriate amount here as a negative value.

Mark Sears: Enter a formula of units to be produced from production budget x amount of materials.

Mark Sears: Enter a formula of units from production budget x hours per unit.

Cost of goods sold

Supporting calculations:

Units

Cost

Total

Finished goods inventory, Dec. 1:

Bird house

Bird feeder

Total

Finished goods inventory, Dec. 31:

Bird house

Bird feeder

Total

Direct materials inventory, Dec. 1:

Wood

Plastic

Total

Direct materials inventory, Dec. 31

Wood

Plastic

Total

Feathered Friends Inc.

Selling and Administrative Expenses Budget

For the Month Ending December 31

Selling expenses:

Mark Sears: List selling expenses in descending order by amount.

Advertising expense

Depr. expense - office equip.

Miscellaneous admin. expense

Office salaries expense

Total selling expenses

Office supplies expense

Administrative expenses:

Sales salaries expense

Mark Sears: List adminstrative expenses in descending order by amount, except miscellaneous, which should always appear last.

Telephone expense - administrative

Telephone expense - selling

Travel expense - selling

Total administrative expenses

Total operating expenses

Feathered Friends Inc.

Budgeted Income Statement

For the Month Ending December 31

Revenue from sales

Cost of goods sold

Gross profit

Operating expenses:

Selling expenses

Administrative expenses

Total operating expenses

Income from operations

Other revenue and expense:

Interest revenue

Interest expense

Income before income tax

Income tax expense

Net income

Sol

Problem 21(7)-3A
Name:		Solution
Section:
Score:		ON
Instructions
Answers are entered in the cells with gray backgrounds.
Cells with non-gray backgrounds are protected and cannot be edited.
An asterisk (*) will appear to the right of an incorrect entry.
1.
		Feathered Friends Inc.
		Sales Budget
		For the Month Ending December 31
				Unit Sales	Unit Selling	Total
	Product			Volume	Price	Sales
	Bird house			3,200	$50.00	$ 160,000
	Bird feeder			3,000	$70.00	210,000
	Total revenue from sales					$ 370,000
2.
		Feathered Friends Inc.
		Production Budget
		For the Month Ending December 31
					Units
					Bird	Bird
					House	Feeder
		Expected units to be sold			3,200	3,000		Estimated inventory, Dec. 31
		Desired inventory, Dec. 31			290	250		Estimated inventory, Dec. 1
			Total		3,490	3,250		Desired inventory, Dec. 31
		Estimated inventory, Dec. 1			(320)	(270)		Desired inventory, Dec. 1
		Total units to be produced			3,170	2,980
3.
		Feathered Friends Inc.
		Direct Materials Purchases Budget
		For the Month Ending December 31
					Wood	Plastic
					(feet)	(lbs.)	Total
		Required units for production:
			Bird house		2,536 Mark Sears: Enter a formula of units to be produced from production budget x amount of materials.	1,585
			Bird feeder		3,576	2,235
		Desired inventory, Dec. 31			220	200
		Total			6,332	4,020
		Estimated inventory, Dec. 1			(200)	(240)
		Total units to be purchased			6,132	3,780
		Unit price			$7.00	$1.00
		Total direct materials to be purchased			$ 42,924	$ 3,780	$ 46,704
4.
		Feathered Friends Inc.
		Direct Labor Cost Budget
		For the Month Ending December 31
					Fabrication	Assembly
					Department	Department	Total
		Hours required for production:
			Bird house		634 Mark Sears: Enter a formula of units from production budget x hours per unit.	951
			Bird feeder		1,192	1,043
			Total		1,826	1,994
		Hourly rate			$16.00	$12.00
		Total direct labor cost			$ 29,216	$ 23,928	$ 53,144
5.
		Feathered Friends Inc.
		Factory Overhead Cost Budget
		For the Month Ending December 31
		Indirect factory wages				$ 75,000
		Depreciation of plant and equipment				23,000
		Power and light				6,000
		Insurance and property tax				5,000
		Total				$ 109,000
								Finished goods inventory, Dec. 1
								Finished goods inventory, Dec. 31
6.								Work in process, Dec. 1
		Feathered Friends Inc.						Work in process, Dec. 31
		Cost of Goods Sold Budget						Direct materials inventory, Dec. 1
		For the Month Ending December 31						Direct materials inventory, Dec. 31
		Finished goods inventory, Dec. 1					$ 19,440
		Work in process, Dec. 1				$ 29,000
		Direct materials:
		Direct materials inventory, Dec. 1			$ 1,640
		Direct materials purchases			46,704
		Cost of direct materials available for use			$ 48,344
		Direct materials inventory, Dec. 31			(1,740)
		Cost of direct materials placed in production			$ 46,604
		Direct labor			53,144
		Factory overhead			109,000
		Total manufacturing costs				208,748
		Total work in process during period				$ 237,748
		Work in process, Dec. 31				(35,400)
		Cost of goods manufactured					202,348
		Cost of finished goods available for sale					$ 221,788
		Finished goods inventory, Dec. 31					(18,080)
		Cost of goods sold					$ 203,708
		Supporting calculations:
					Units	Cost	Total
		Finished goods inventory, Dec. 1:
			Bird house		320	$27.00	$ 8,640
			Bird feeder		270	$40.00	10,800
			Total				$ 19,440
		Finished goods inventory, Dec. 31:
			Bird house		290	$27.00	$ 7,830
			Bird feeder		250	$41.00	10,250
			Total				$ 18,080
		Direct materials inventory, Dec. 1:
			Wood		200	$7.00	$ 1,400
			Plastic		240	$1.00	240
			Total				$ 1,640
		Direct materials inventory, Dec. 31
			Wood		220	$7.00	$ 1,540
			Plastic		200	$1.00	200
			Total				$ 1,740
7.
		Feathered Friends Inc.
		Selling and Administrative Expenses Budget
		For the Month Ending December 31
		Selling expenses:
		Sales salaries expense Mark Sears: List selling expenses in descending order by amount.			$ 70,000			Advertising expense
		Advertising expense			18,000			Depr. expense - office equip.
		Travel expense - selling			4,000			Miscellaneous admin. expense
		Telephone expense - selling			550			Office salaries expense
			Total selling expenses			$ 92,550		Office supplies expense
		Administrative expenses:						Sales salaries expense
		Office salaries expense Mark Sears: List adminstrative expenses in descending order by amount, except miscellaneous, which should always appear last.			$ 21,000			Telephone expense - administrative
		Depr. expense - office equip.			600			Telephone expense - selling
		Telephone expense - administrative			250			Travel expense - selling
		Office supplies expense			200
		Miscellaneous admin. expense			400
			Total administrative expenses			22,450
		Total operating expenses				$ 115,000
8.
		Feathered Friends Inc.
		Budgeted Income Statement
		For the Month Ending December 31
		Revenue from sales				$ 370,000
		Cost of goods sold				203,708
		Gross profit				$ 166,292
		Operating expenses:
			Selling expenses		$ 92,550
			Administrative expenses		22,450
			Total operating expenses			115,000
		Income from operations				$ 51,292
		Other revenue and expense:
			Interest revenue		$ 200
			Interest expense		(122)	78
		Income before income tax				$ 51,370
		Income tax expense				15,411
		Net income				$ 35,959