Solved by verified expert :Budgeting SpreadsheetThis assignment asks you to set up an Excel budget spreadsheet file that automatically preparesthe master budget for a company, given sales projections and information on beginningbalances, production requirements, desired ending inventories, etc. Information on developingthe budgets appears in Chapter 7 of your text, and examples of budget worksheets appear in theschedules throughout the chapter.DataNiagara Manufacturing Company produces and sells one product, Metro End Table. Below isinformation on its activities for the next few months.1. Sales projections for the coming months are as follows:Metro end tablesEstimated Sales (in units)JulyAugust10,00012,000September9,000October8,000Actual sales in May were 10,000 units; actual sales in June were 14,000 units.2. Metro end tables selling price is $500/unit. Estimated cash collections from sales ofeach month (including June) of Metro end tables to customers are as follows: 65%collected in the month of sale, 30% collected in the month following sale, and 5% cannot be collected.3. Desired ending inventory of Metro end table is 20% of the next months projected sales.There are 4,000 units of end tables in inventory on June 30.4. Two materials are used in the production of Metro end table: Red Oak and Glass.Materials requirements per unit of end table are as follows:Direct MaterialRed Oak:Glass:Units of direct materialsper unit of Metro end table.45 pounds9 square feetCost per unit$6.00/pound$1.00/square footDesired ending inventory of Red Oak is 30% of the following months production needbecause Red Oak is sometimes in short supply; desired ending inventory of Glass is 5%of the following months production need because Glass is easy to get. Inventories ofmaterials as of June 30 are 26,000 pounds of Red Oak and 4,900 square feet of Glass.Practice Set 1 Page 15.The company pays for materials purchases as follows: 60% in the month of purchase,and 40% in the month following purchase. The accounts payable balance (due tomaterials purchases) on June 30 was $1,100,000, which is all payable in July.6.Metro end tables have two departments to pass before it is completed.DepartmentDirect labor hours per unit of end tableCost per direct laborhourShaping:0.75 hour$24Finishing:1.70 hours$167. Direct labor costs are paid in cash as incurred.8. Total variable manufacturing overhead is estimated at $2.5/direct labor hour incurred.Total fixed manufacturing overhead is estimated at $500,000/month, of which $5,000 isdepreciation on factory buildings and equipment.9. Total variable selling and administrative costs are $2/unit of Metro end table sold.Total fixed selling and administrative costs are estimated at $300,000/month, of which$65,000 is depreciation on administrative buildings and equipment.10. Cash payments for overhead and selling and administrative costs are paid as the costsare incurred.Preparation of Spreadsheet FileCreate one Excel spreadsheet file consisting of the following five separate worksheets:Sheet 1:DataThis worksheet contains the data necessary to do all the other worksheets. Liston this worksheet all of the data shown above, clearly labeled. None of the otherworksheets should contain any numbers; they should contain ONLY FORMULASall cells on the other worksheets should be linked to cells in the dataworksheet, cells within the same worksheet, or cells in the other worksheets.For example, the cell for direct labor cost for July should contain a formula thatmultiplies the production in units for July (from the production schedule) timesthe labor hours per unit (from the data worksheet) times the hourly wage rate(from the data worksheet). So if the sales estimate for end tables changes, youshould be able to make the change only on the data worksheet; all the otherworksheets should automatically adjust to the changes. The production units willchange; the manufacturing costs will change, etc. The data worksheet can be inany format; just be sure to label each data item clearly.Practice Set 1 Page 2Sheet 2:Include the following two budgets on the second worksheet, clearly labeled:Sales Budget:Prepare a schedule of salesrevenue and cash receipts from sales for each of themonths of July, August and September. List cashcollections separately on lines as follows: cashcollections from sales one month ago and cashcollections from sales in the current month. Also,list total cash collections in July, August andSeptember.Production Budget:Sheet 3:Include the following one budget on the third worksheet, clearly labeled:Direct Materials Budget:Sheet 4:Prepare a direct materials purchases budget, inunits and in total dollars, for July and August. ListRed Oak purchase costs, Glass purchase costs, andtotal material purchase costs separately. Listpayments for current month purchase and for priormonth purchase on separate lines. Also, list totalpayments in July and August.Include the following one budget on the fourth worksheet, clearly labeled:Direct Labor Budget:Sheet 5:Prepare a production budget for Metro endtables, in units, for each of the months of July,August and September.Prepare the budget for costs of direct labor used forJuly and August, in units and in total dollars. Listshaping labor cost, finishing labor cost, and totallabor cost separately. Also, list total payments foreach month.Include the following two budgets on the fifth worksheet, clearly labeled:Manufacturing Overhead Budget:Prepare a manufacturing overhead budget for Julyand August. Show variable manufacturingoverhead, fixed manufacturing overhead and totalmanufacturing overhead costs separately for eachmonth. Also, list total payments for each month.Selling & Administrative Expense Budget: Prepare a selling and administrative expensebudget for July and August. Show variable sellingand administrative costs, fixed selling andadministrative costs and total selling andadministrative costs separately for each month. ListPractice Set 1 Page 3total cost payments for each month.Project SubmissionWhen you have completed your file, please submit it in the following format:1.Print out each of the five worksheets. Put your NAME, ID NUMBER, ANDRECITATION SECTION NUMBER at the top of each page. Print each page withgridlines. On sheets 2-5, label each budget clearly and include Estimate #1" in eachlabel. For example, Production Budget, Estimate #1" and Direct Labor Budget,Estimate #1.2.Now, on your data worksheet, change the following items:Estimated sales of Metro end tables for July: 14,000 units.Cost per square foot of Glass: $1.5Dont make any other changes in the worksheets, except for changing the budgetlabels to Estimate #2. Print out worksheets 2-5 again, with gridlines.3.Print out worksheets 2-5 showing the cell formulas instead of numbers. Print withgridlines.Staple all pages together in the order presented above.To summarize, your completed project consists of the following pages:Data worksheet (one page)Budget worksheets 2, 3, 4 and 5 for Estimate #1 (4 pages)Budget worksheets 2, 3, 4 and 5 for Estimate #2 (4 pages)Budget worksheets 2, 3, 4 and 5 showing cell formulas (4 pages)for a total of 13 pages.Please follow these instructions. If you submit your practice set in a different format, youwill lose points.Other information1. You should have been exposed to basic Excel in other courses. You should alreadyknow how to link worksheets and enter cell formulas. For example, if you name yourdata worksheet as data and you are currently working on a different worksheet,entering a formula Data!B5 into a cell will allow you to get the number contained inCell B5 of the data worksheet to your current worksheet. If for some reason you havenot received formal class instruction in Excel, you can use the help menus (as youwould with any new software).Practice Set 1 Page 4Here are some hints for printing this assignment: you can adjust printing formats (pageorientation, gridlines) by selecting Page Layout from the top menu, and then click onorientation to select orientation, and gridlines to select gridlines. To print cellformulas, select the Ctrl tab and ~. tab simultaneously (the one at the left corner ofyour key board). Your formulas should appear and you are ready to print.2.Academic integrity: This is an individual project, meaning each student is expected todo it independently. You are expected to input your own data, develop your ownspreadsheet formulas, and set up your own worksheets within your file. You may notuse someone elses file or jointly share a file. You have great flexibility in how you setup your spreadsheet. It is extremely unlikely that the many choices you make indeveloping your worksheet will be identical to those of any other student. We willcheck for evidence of shared files. If academic dishonesty is found, it will be dealt withseverely. Be sure to retain the file you created (plus a backup file) until the end of thesemester, in case there is a question.3.The due date for the Practice Set is April 12, during your recitation class . Please turnyour practice set in directly to your TA during recitation, or make arrangements todeliver it at some other time. Do not put it under any office door or leave it on a desk inyour TAs office. If you dont hand it in during recitation, be sure the person you handit to records the time and date of your submission. This practice set is worth 40 pointstoward your total grade. If you do not hand it in on time (prior to 4 PM on April 12),you will lose 5 points for every day it is late, including weekend days. You arewelcome to hand in your practice set early (during lecture or office hours). You haveabout a month to prepare this assignment . If you decide to wait until a few days beforeit is due to begin work on it, you also accept the risk that unplanned events may preventyou from completing it on time. It is up to you to organize your work schedule so yourpractice set is completed within the deadline. To minimize potential loss of your file,always back up your work in case your disk goes bad.4.Here are some key figures to help you: For Estimate #1, the cash payments for totaldirect materials purchase for July are $2,966,738. For Estimate #2, the cash paymentsfor total direct materials purchase for July are $3,669,887. If your numbers do not comeout right, and you cant find your mistake, do not try to change the numbers so the totalcomes out correctly. Each individual balance is graded, not the total. You may bechanging numbers that are correct.5. You are welcome to obtain help from your TAs or Professor Xu as you do this project.Questions should be limited to the accounting issues, and questions on the specificprinting requirements of the practice set.
