Could someone help with this assignment please? Info is in the documents attached

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 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