Can somebody help me with this problem set? The information is included in the attached documents.

Excel Assignment #2 - Master Budget Project Congratulations (or maybe condolences). As the newbie in the office, you've been assigned the task of preparing the company's 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, here's 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 don't both to see how our printed Excel reports look when printed. Don't 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. Page 1 of 3 Pages Excel Assignment #2 - Master Budget Project 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 you're 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 don't 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, don't go overboard on your use of color. Remember, your final product may be printed on a black and white Page 2 of 3 Pages Excel Assignment #2 - Master Budget Project 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 Submit your assignment through the Canvas Assignments section with the file BIGBUD_Last Name_First Name where First Name and Last Name are your first and last names. Page 3 of 3 Pages A B C D 1 Assumptions Page 2 3 4 Crane Corp. Balance Sheet, December 31 5 6 7 8 9 10 11 12 13 14 15 Cash Accounts receivable Raw materials Finished goods Land Plant and equipment Less: accumulated depreciation Total assets $41,700 192,000 102,240 64,960 50,000 $500,000 112,000 388,000 $838,900 16 Accounts payable to suppliers 18 Common Stock 19 Retained Earnings 20 Total liabilities and equity $40,000 17 $100,000 698,900 21 22 23 24 25 26 27 28 Budgeted Widgets sales in units January February March April May 7,000 8,000 10,000 8,000 7,000 29 30 Selling price for each Widget $60 31 Collections from customers (All sales made on account) Collected in month of sale 33 32 40% 798,900 $838,900 E A 34 Collected in the month following the sale B C D 60% 35 Desired finished goods inventory (% of next month's unit sales) 37 36 20% 38 Desired raw material inventory of Gizmos and Doodads (% of next month's production needs) 40 39 25% 41 Raw material units needed to produce one Widget Gizmos 43 Doodads 44 42 5 3 45 Beginning inventory units & cost per unit Widgets 47 Gizmos 48 Doodads 49 46 1,400 18,000 10,800 $46.40 $4.00 $2.80 50 Purchases Paid in the month of purchase 52 Paid in the month following the purchase 53 51 70% 30% 54 Direct labor time to produce one Widget Cost of direct labor (All labor costs are paid in the 56 month incurred) 55 0.5 hours $24 per hour 57 Factory overhead (for simplicity, both fixed and variable overhead is included in one rate) Plant and equipment depreciation (already included in 59 overhead allocation rate above) 58 $12 per direct labor hr. $12,000 per year 60 Selling and administrative expenses per month * Selling and administrative expenses are paid in 62 the month incurred. 61 $10,000 fixed 10% of sales E A 63 *There is no depreciation expense included in the selling and administrative expenses for this quarter B C D E A B C D 1 Assumptions Page 2 3 4 5 6 7 8 9 10 11 12 13 14 Crane Corp. Balance Sheet, December 31 Cash Accounts receivable Raw materials Finished goods Land Plant and equipment Less: accumulated depreciation 15 Total assets $41,700 192,000 102,240 64,960 50,000 $500,000 112,000 388,000 $838,900 16 Accounts payable to suppliers Common Stock Retained Earnings 20 Total liabilities and equity 17 18 19 $40,000 $100,000 698,900 21 22 23 24 25 26 27 28 29 Budgeted Widgets sales in units January February March April May 30 Selling price for each Widget 7,000 8,000 10,000 8,000 7,000 $60 31 Collections from customers (All sales made on account) Collected in month of sale 33 Collected in the month following the sale 34 32 40% 60% 798,900 $838,900 E A 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 B C D 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 Doodads Beginning inventory units & cost per unit Widgets Gizmos Doodads Purchases Paid in the month of purchase Paid in the month following the purchase Direct labor time to produce one Widget Cost of direct labor (All labor costs are paid in the 56 month incurred) 5 3 1,400 18,000 10,800 $46.40 $4.00 $2.80 70% 30% 0.5 hours $24 per hour 57 Factory overhead (for simplicity, both fixed and variable overhead is included in one rate) Plant and equipment depreciation (already included in 59 overhead allocation rate above) $12,000 per year 60 61 $10,000 fixed 58 Selling and administrative expenses per month * Selling and administrative expenses are paid in 62 the month incurred. *There is no depreciation expense included in the 63 selling and administrative expenses for this quarter $12 per direct labor hr. 10% of sales E Crane Corporation Sales Budget For the first quarter January February March Budgeted sales (unit) 7,000 8,000 10,000 Selling price $60 $60 $60 Budgeted sales $420,000 $480,000 $600,000 January Forecasted unit sales Crane Corporation Production Budget For the First Quarter February March Total April May 7,000 8,000 10,000 25,000 8,000 Planned finished goods inventory Production required 1,600 2,000 1,600 5,200 1400 8,600 10,000 11,600 30,200 9,400 Beginning finished goods inventory 1,400 1,600 2,000 5,000 1600 Products to be manufactured (Widgets) Gizmos 7,200 36,000 8,400 42,000 9,600 48,000 25,200 126,000 7,800 39,000 Doodads 21,600 25,200 28,800 75,600 23,400 7,000 Crane Corporation Raw Materials Purchase Budget For the first quarter January February March Raw materials required for production Gizmos Doodads Planned ending inventory units Gizmos Doodads Total raw materials required Gizmos Doodads Beginning raw materials inventory Gizmos Doodads Raw materials to be purchased ($) (Units) Cost per unit Gizmos Total April 36000 21600 42000 25200 48000 28800 126000 75600 10500 6300 12000 7200 9750 5850 32250 19350 46500 27900 54000 32400 57750 34650 158250 94950 18,000 10,800 10500 6300 12000 7200 40,500 24,300 28,500 $ 4.00 $114,000.00 43,500 $ 4.00 $174,000.00 45,750 $ 4.00 $183,000.00 117,750 $ 4.00 $471,000.00 17,100 $2.80 $47,880.00 26,100 $2.80 $73,080.00 27,450 $2.80 $76,860.00 70,650 $2.80 $197,820.00 $161,880.00 $247,080.00 $259,860.00 $668,820.00 Doodads (Units) Cost Total cost for direct material purchases 39000 23400 Crane Corporation Direct labor budget, First quarter January February March Number of units to be produced Direct labor time to produce one Widget (hours) Cost per direct labor hour $ Cost for direct labor $ 7,200 0.5 24 $ 86,400 $ Total 8,400 9,600 25,200 0.5 24 $ 100,800 $ 0.5 24 $ 115,200 $ 0.5 24 302,400 Crane Corporation Manufacturing Overhead Budget, First Quarter January Number of direct labor hours 3,600 Overhead rate $12 Total manufacturing overhead $ 43,200 February 4,200 $12 $ 50,400 March 4,800 $12 $ 57,600 Total 12,600 $12 $ 151,200 Crane Corporation Selling and Administration, First quarter January February March Total Fixed selling and adminstration expenses $ 10,000 $ 10,000 $ 10,000 $ 30,000 Variable selling and adminstration expenses $42,000 $48,000 $60,000 $150,000 Total selling and adminstration budget $ 52,000 $ 58,000 $ 70,000 $180,000 Crane Corporation Cash Budget, First quarter January Cash receipts Sales (on account) Receipts from month of sale Receipts after a month of sale Total receipts Cash payments Payment for raw material purchases Paid in month of purchase Paid one month after purchase Total payment for raw materials $ 420,000 February $ 480,000 March $ Total 600,000 $ 1,500,000 168,000 192,000 240,000 600,000 192,000 360,000 252,000 444,000 288,000 528,000 732,000 1,332,000 113,316 172,956 181,902 468,174 40,000 48,564 74,124 162,688 153,316 221,520 256,026 630,862 Payment for labor 86,400 100,800 115,200 302,400 overheads Less depreciation expense Payment for manufacturing overheads 43,200 50,400 57,600 151,200 12,000 12,000 12,000 36,000 31,200 38,400 45,600 115,200 52,000 322,916 $37,084 58,000 418,720 $25,280 70,000 486,826 $41,174 180,000 1,228,462 $103,538 Payment for salaries and administration expense Total cash payments Cash balance Crane Corporation Finished goods inventory budget, First quarter January February March Total Beginning finished goods inventory $64,960 $74,240 $92,800 $232,000 Produced finished goods 334,080 389,760 445,440 1,169,280 Units available for sale 399,040 464,000 538,240 1,401,280 Units sold 324,800 371,200 464,000 1,160,000 Finished goods $74,240 $92,800 $74,240 $241,280 Crane Corporation Income statement, First quarter January February March Total Sales $420,000 $480,000 $600,000 $1,500,000 Cost of goods sold (raw materials) 161,880 247,080 259,860 668,820 Direct labor 86400 100800 115200 302,400 Gross profit 171,720 132,120 224,940 528,780 Expenses Selling and adminstration expense 52,000 58,000 70,000 180,000 Manufacturing overheads 43,200 50,400 57,600 151,200 Total expenses 95,200 108,400 127,600 331,200 Net Income $ 76,520 $ 23,720 $ 97,340 $197,580 Crane Corp. Balance Sheet, First Quarter January February March Cash $37,084 $25,280 $41,174 Accounts receivable 252,000 288,000 360,000 Raw materials 59,640 68,160 55,380 Finished goods 74,240 92,800 74,240 Land 50,000 50,000 50,000 Plant and equipment 500,000 500,000 500,000 Less: accumulated depreciation 113,000 114,000 115,000 Total assets $859,964 $910,240 $965,794 Accounts payable to suppliers Common Stock Retained Earnings Total liabilities and equity $ 48,564.00 100,000 775,420 $ 923,984 $ 74,124.00 100,000 799,140 $ 973,264 $ $77,958 100,000 896,480 1,074,438