Mod 5 project & assignment- FOR ACCOUNTING GENIUS ONLY

mod_05_assignment_template_revised_2.xlsx

Sales Budget

Coopie Awards produces brass plaques. They operates in 3 regions of the country. The western region sales manager estimates sales of 20,000 plaques for the region.
The central regional manager estimates sales of 10,000 plaques for the region and the eastern sales manager estimates slaes of 15,000 plaques for the region. Each plaque sells for $35.
Requirement: Prepare the sales budget for Coopie Awards
Coopie Awards
Sales Budget
For the current Year
Region	Sales Volume	Selling Price	Total Sales
Western	$35
Central	10,000
Eastern	$525,000
Total Units	$1,575,000

Coopie Awards projected sales of 45,000 brass plaques for the year. The estimated January 1 inventory is 3,000 plaques, and the desired December 31 inventory is 5,000 units.
Requirement: Prepare the production budget in units for Coopie Awards.
Coopie Awards
Production Budget
For the current year
Sales
Plus desired ending inventory
Total
Less estimated beginning inventory
Total Production	47,000

Coopie Awards budgeted production of 47,000 brass plaques for the year. 96 square inches are required for each brass plaque. The estimated January 1 brass sheet inventory is 240,000 square inches.
The desired December 31 brass sheet inventory is 200,000 square inches. Brash sheet costs $0.12 per square inch.
Requirement: Prepare the direct materials purchases budget for Coopie Awards
Coopie Awards
Direct Materials Purchases Budget
For the current Year
Units Required for Production (47,000X96)	sq. in.
Plus desired ending inventory
Total Needed	sq. in.
Less estimated beginning inventory
Total units to be purchased	sq. in.
Unit price
Total direct materials purchases	$536,640.00

Coopie Awards budgeted proction of 47,000 brass plaques. Each plaque requires 12 minutes of engraving. Engraving labor costs $11.00 per hour.
Required: Prepare the direct labor cost budget for Coopie Awards
Coopie Awards
Cost of Direct Labor Budget
For the Current Year
Hours required for production (47,000X12/60)
Hourly rate
Total Direct Labor cost	$103,400.00

Assume the estimated January 1 inventory for finished goods and work in process were $54,000 and $47,000 respectively. Also assume the desired December 31 inventories for
finished goods and work in process were $50,000 and $49,000 respectively. Factory overhead was budgeted for $126,000.
Using the information on the previous tabs, prepare a cost of goods sold budget for Coopie Awards for the current year.
Coopie Awards
Budgeted Cost of Goods Sold
For the current year
Work in process Inventory January 1
Direct Materials:
Direct materials inventory January 1 (Note a)
Direct materials purchases
Cost of direct materials available for sale
Less direct materials December 31 (note b)
Cost of direct materials used in production
Direct labor
Factory Overhead
Cost of goods added to production
Cost of goods available
Less work in process December 31 inventory
Cost of goods manufactured
Plus Finished goods January 1
Less finished goods December 31
Cost of goods sold	$ 772,840
Note a: 240000 X $0.12
Note b: 200000X.12

The following are date for the sales and administrative budget for Coopie Awards:
Sales Commissions	7% of sales
Advertising Expense	18% of sales
Misc. selling expense	$1,750 plus 4% of sales
Office salaries expense	$12,000 per month
office supplies expense	5% of sales
Misc administrative expense	$1,400 per month plus 2% of sales
Requirement: Prepare the selling and admin budget for Coopie Awards
Coopie Awards
Selling and Administrative Budget
For the current Year
Selling Expenses:
Sales commissions
Advertising expenses
Miscellaneous selling expenses	64750
Total selling expenses
Administrative expenses:
Office salaries
Office supplies
Miscellaneous administrative expenses
Total administrative expenses
Total selling and administrative expenses	$729,550

Coopie Awards pays 40% in income taxes.
Requirement: Prepare Coopie Awards' budgeted income statement. Note: Provided you achieved all of the check figures along the way, this statement has formulas written to auto-populate each of these, meaning there is no work to be completed on this tab.
Coopie Awards
Budgeted Income Statement
For the current year
Sales Revenue	$1,575,000
Cost of Goods sold	772840
Gross Profit	$802,160
Selling and administrative expenses	729550
Income from operations	$72,610
Income Tax	29044
Net Income	$43,566

Coopie Awards sold 43,000 brass plaques for the current year with the following results. In the discussion forum comment on Coopie Awards results.
Hint: Preparing a flexible budget will help in analyzing the results. I have created a flexible budget here to aid you in addressing M5 Discussion.
There is no additional work necessary on this tab for your M5 Written Assignment. This is informational only for your M5 Discussion.
Actual Results	Budget based on 45000 unis	Variance (Actual at 43,000 sales but budget at 45,000 units	Flexible Budget based on 43,000 units	Variance (Actual at 43,000 sales and budget at 43,000 sales)
Sales Revenue	$ 1,548,000	$ 1,575,000	$27,000	Unfavorable	$ 1,505,000	$ (43,000)	Favorable
Direct Materials costs	498000	518400	$20,400	Favorable	490,560	$ (7,440)	Unfavorable
Direct Labor Costs	94800	99000	$4,200	Favorable	94,600	$ (200)	Unfavorable
Overhead Costs	126000	126,000	$0	126,000	$ - 0
total dierct costs	$ 718,800	$ 743,400	$24,600	Favorable	$ 711,160	$ (7,640)	Unfavorable
Selling and administrative cost	700000	729550	$29,550	Favorable	704,350	$ 4,350	Favorable
operating income	$ 129,200	$ 102,050	($27,150)	Favorable	$ 89,490	$ (39,710)	Favorable