Question;1.The GSO Company manufactures and sells a high quality garden ornament (theGuilford Gnome). The Gnome has one direct material Plaster of Paris. Othermaterials, such as paint and lacquer are treated as indirect materials. GSO is preparingbudgets for the fourth quarter ending December 31, 2013. For each requirementbelow prepare budgets by month for October, November and December and a totalbudget for the quarter. Last year's sales (2012) for the corresponding period (plusJanuary and February 2013) were:OctoberNovemberDecemberJanuaryFebruary5,000 gnomes8,000 gnomes15,000 gnomes10,000 gnomes5,000 gnomesThe company expects the above number of gnome sales to increase by 10% for theperiod Oct. 2013 Feb. 2014. The budgeted selling price for 2013/14 is $149.00 pergnome. The company expects 30% of its sales to be cash (COD) sales. Theremaining 70% of sales will be made on credit. Prepare a Sales Budget for GSO.2.The company desires to have finished goods inventory on hand at the end of eachmonth equal to 5 percent of the following month's budgeted unit sales. On September30, 2013, there were 275 gnomes on hand. Prepare a Production budget.3.Ten pounds of material are required per gnome. Management desires to havematerials on hand at the end of each month equal to 20 percent of the followingmonth's production needs. The beginning materials inventory, October 2013, was11,330 pounds. The material costs $4.00 per pound. Prepare a Direct Materialsbudget.4.Each gnome produced requires 0.75 hours of direct labor. Each hour of direct laborcosts the company $18. Prepare a Direct Labor budget.5.Variable manufacturing overhead (MOH) is allocated at the rate of $12 per directlabor hour. That is, for each budgeted direct labor hour, allocate $12 of variableMOH. Fixed manufacturing overhead is $240,000 per month. Prepare aManufacturing Overhead budget.6.Variable selling and administrative expenses are $8.00 per gnome sold. Fixed sellingand administrative expenses are $120,000 per month. Prepare an OperatingExpenses budget.7.Prepare a Budgeted Manufacturing Cost per unit budget. The total productionvolume for the year is budgeted as 96,000 gnomes. HINT: refer back to step 5. Tocalculate the VMOH/unit cost you should use the variable overhead rate times theallocation base. To calculate FMOH/unit calculate total FMOH for the year anddivide this by budgeted production for the year: refer to footnote d on page 527 ofthe textbook.8.Prepare a Budgeted Income Statement for the quarter for GSO. Assume interestexpense of $0, and income tax expense of 35% of income before taxes.Directions:Refer to Chapter 9 (The Master Budget) for guidance in setting up your budgets andschedules. Adapt your schedules for the specific details outlined in the requirementsabove. Prepare your budgets using Excel. Use formulas and cell references so that anychange you make in one budget is carried through to all the budgets (there should beno hard keyed numbers in your formulas). For example, if you change the sales volumeincrease from 10% to 20% you should see effects of that change throughout the otherbudgets. Likewise, if the budgeted selling price changes from $149 to $145 yourspreadsheet model should be able to quickly and easily accommodate this change, i.e.,change the input cell for budgeted selling price and see the effect on income.The spreadsheet will be graded on presentation, completeness, correctness, and quality ofyour spreadsheet model (i.e., does it update correctly for changes in input variables). Youshould approach this assignment as if you are the Cost Accountant at the GSO Companyand you are going to present these budgets in a meeting to the CEO, CFO, and othermanagement personnel.Some general principles to follow in constructing your excel spreadsheet model:1. Prepare an input area in which you enter all input variables e.g., selling price,budgeted volume increase, pounds per gnome, ending inventory percentage, etc2. Each schedule should refer to the input area to source data (see samplespreadsheet file). If possible, keep all constant values together in one area of theworksheet. An important principle of good spreadsheet design is to keep just onecopy of each constant value. That is, enter a constant value in only one location inthe worksheet. Then if you use the value in another cell, use a cell reference thatrefers to the constant value's unique location.Example: You enter the constant value of 6% for sales tax in cell E5.When you write a formula in your worksheet that requires sales tax,reference E5 in the formula instead of "hard coding" in the 6% value.Do: =subtotal*E5Don't: =subtotal*6%There should be no hard-keyed numbers in your formulas e.g., the formula todetermine current period sales in units should reference last years sales volumeand a cell with the volume percentage increase.4. Label and format appropriately e.g., use $ to format dollar amounts, format cellsfor decimal places, etc3.Submit your Excel spreadsheet to me by email. Include your name in the spreadsheet andin the filename.Email me if you have any questions. This is to be your OWN work. If I consider fileshave been copied and you are submitting the work of another student I will assign zerofor the assignment. Late work will not be accepted.
Paper#39273 | Written in 18-Jul-2015Price : $35