Excel Assignment #2 Master Budget Project Page 1 of 3 Pages Congratulations (or maybe condolences). As the newbie in the office, youve been assigned the task of preparing the companys master budget for the next quarter. The company, Crane Corp, only produces one product, a widget. This widget is made up of 5 gizmos and 3 doodads. Your job is to prepare all the schedules necessary to complete the master budget including: the Sales, Production, Direct Materials, Direct Labor, Manufacturing Overhead, Selling General & Administration, and Cash budgets. In addition, you will need to prepare a schedule showing the Finished Goods Inventory costs, a budgeted Income Statement, and a Balance Sheet for the quarter. To help you, there is an Excel template titled BIGBUD Assumptions Sheet. The first worksheet in this file is titled Assumptions and contains all the information you need to complete the project. The Excel file has worksheets titled for all the schedules you will need. You will complete this project by following the example schedules in Chapter 8 of the text. Note that you are preparing the budget for the 1st quarter of 2017 (January, February, and March) so you should have a column for each month as well as a column for the totals for the quarter. On the production and direct materials budget, you will need to include a column for April as well. This column should be added to the right of your total column which should include only the totals for the first quarter. When you complete your work make sure your Balance Sheet balances. If your balance sheet does not balance, you have done something wrong and you will need to go back and find your mistake before submitting your work. Your total assets at the end of March should be $1,036,858 (rounded). To further assist you, heres a couple of hints: the unit product cost of the Widget that you calculate in the Finished Goods Inventory Costs Schedule should be exactly the same as the beginning unit cost of the widgets in the Assumptions worksheet. No financing activities are required on the Cash Budget for this quarter This assignment is designed not only to test your knowledge of preparing a master budget, but also to test your Excel skills. Here are the Excel items that will be graded: 1. Each worksheet must print on one page in portrait orientation with no scaling. Many times we get lazy and dont both to see how our printed Excel reports look when printed. Dont fall into this trap. I recommend you use the Print Preview feature in Excel to make sure your report will print appropriately. 2. All worksheets must have a proper title centered across the top of the page. A proper title consists of: Company Name, Report Title, Period (or Date). You can either put the report title in the worksheet header or on the first three lines of the worksheet. Either way, the titles must be centered on the page. Again, check the Print Preview feature to verify correct orientation and formatting of your title. Excel Assignment #2 Master Budget Project Page 2 of 3 Pages 3. Each worksheet must print centered horizontally on the page. To make this happen go into margins, select custom margins, and then check the box that says Center on Page: Horizontally. 4. ALL schedules must use cell referencing to the numbers in the Assumptions worksheet or to a previous budget worksheet. This is extremely important. DO NOT enter the numbers themselves from the Assumptions worksheet into the other worksheets. Use an appropriate cell reference instead. 5. All dollar amounts should be formatted using the Accounting format with zero decimal places with the following exceptions: o The cost per gizmo and cost per doodad in the Direct Materials worksheet should be formatted using the Accounting format with two decimal places. o The predetermined overhead rate in the Manufacturing Overhead worksheet should be formatted using the Accounting format with two decimal places. o The Totals in the Finished Goods Inventory Costs worksheet should be formatted using the Accounting format with two decimal places. 6. All worksheets must use proper formulas and functions. (I.e. if youre adding a column of numbers use the SUM function. DO NOT add the numbers on a calculator and then enter them into Excel manually.) 7. Spelling. What would your boss think if you gave him a report with spelling mistakes? You instructor feels much the same. Spelling mistakes will be penalized. With the Excel spell checker, there are no excuses for spelling errors. 8. Consistency. As you work in Excel, you will develop your own style. Part of this style is consistency. You want your reports to look like they were created by the same person. This means you dont want to change the font, type size, etc. on each separate worksheet. Whatever you select (my recommendation is to leave the default font and font size as is. This will make it easier to fit the worksheets on one page). It also means proper underlining, use of the dollar sign (dollar signs should be used at the first instance of a dollar amount in a column and in the total (see below) $23.00 24.00 25.00 $72.00 Proper underlining is important also. Single underline on the line before you total a column of numbers and double underline the total. Although you want your Excel reports to be visually appealing, dont go overboard on your use of color. Remember, your final product may be printed on a black and white Excel Assignment #2 Master Budget Project Page 3 of 3 Pages printer. Frankly, in the real word, color is not often used in Excel spreadsheets (shading is used more frequently). Excel skills needed to successfully complete this assignment: Cell referencing Margin setting Cell formatting Merging cells Centering work on printed page Spell checker Underlining

Crane Corp.
Balance Sheet, December 31
Cash			$41,700
Accounts receivable			192,000
Raw materials			102,240
Finished goods			64,960
Land			50,000
Plant and equipment		$500,000
Less: accumulated depreciation		112,000	388,000
Total assets			$838,900
Accounts payable to suppliers			$40,000
Common Stock		$100,000
Retained Earnings		698,900	798,900
Total liabilities and equity			$838,900
Budgeted Widgets sales in units
January		7,000
February		8,000
March		10,000
April		8,000
May		7,000
Selling price for each Widget		$60
Collections from customers (All sales made on account)
Collected in month of sale		40%
Collected in the month following the sale		60%
Desired finished goods inventory
(% of next month's unit sales)		20%
Desired raw material inventory of Gizmos and Doodads
(% of next month's production needs)		25%
Raw material units needed to produce one Widget
Gizmos		5
Doodads		3
Beginning inventory units & cost per unit
Widgets		1,400	$46.40
Gizmos		18,000	$4.00
Doodads		10,800	$2.80
Purchases
Paid in the month of purchase		70%
Paid in the month following the purchase		30%
Direct labor time to produce one Widget		0.5	hours
Cost of direct labor (All labor costs are paid in the month incurred)		$24	per hour
Factory overhead (for simplicity, both fixed and variable overhead is included in one rate)		$12	per direct labor hr.
Plant and equipment depreciation (already included in overhead allocation rate above)		$12,000	per year
Selling and administrative expenses per month		$10,000	fixed
* Selling and administrative expenses are paid in the month incurred.		10%	of sales
*There is no depreciation expense included in the selling and administrative expenses for this quarter Sales Budget , Production Budget , Direct Material Purch. Budget , Direct Labor Budget , Manufacturing Overhead Budget , Selling Admin. Budget , Cash Budget , Finished Goods Inverntory , Income Statement , Balance Sheet. These are all the the seprate worksheets ^