Reference no: EM132040411

Using the information below, please prepare 1. Sales Budget including a schedule of expected cash collection. 2. Production Budget 3. Direct Materials budget including a schedule of expected cash disbursements. 4. Direct Labor Budget 5. MOH Budget including cash disbursements for overhead 6. S&A budget including cash disbursements for S&A 7. Budgeted Income Statement. a. When calculating COGS please illustrate how you computed the COGs per unit. b. Must calculate a COST Per Unit for each product line. 8. Cash Budget For Budgets 1-6: There are two product lines, Master & Backyard, prepare only 1 of each budget above that includes the data for both products. Both Product Lines should appear on the Budgets and not be combined. This will require some creative thinking in regards to the layout of the budgets. For Budgets 7 & 8: The financial data will NOT BE SEPARATED BY PRODUCT LINE and all numbers will be combined. For example, on the Income Statement, there will be one Revenue number that includes the revenue from both Backyard and Master. FOR BUDGETS 1-8: IF ROUNDING IS NECESSARY, PLEASE ROUND TO TWO DECIMALS. Excel Requirements: 50 points The budgets are to be completed in Excel. Please use the Excel File uploaded on Blackboard. The Excel Requirements are as follows: 1. Each Budget will appear in an individual worksheet with each Tab labeled with appropriate Budget’s Name. 2. All calculations must be completed in excel using formulas. The formulas must reference other cells (within the Excel workbook) in the calculations. In other words, you will not be manually inputting numbers in the formulas but instead referencing other cells in the workbook. 3. The following are the minimum Formula requirements for each budget. Sales Budget – minimum of 9 cells that contain formulas. Production Budget – minimum 6 cells that contain formulas DM Budget – minimum 20 cells that contain formulas DL Budget – minimum of 8 cells contain formulas MOH Budget – minimum 5 cells that contain formulas S&A Budget – minimum 3 cells that contain formulas COGS calculations: All numbers (DM, DL, VMOH, FMOH) should be cell referenced from appropriate budget worksheets within the Excel Workbook. Minimum 6 cells that contain formulas. Cell Referencing is not included in the minimum 6 formula count. Income Statement: Minimum 3 cells that contain formulas. All other line items that do not include a formula, should be entered by cell referencing from another worksheet within the Workbook. Cash Budget: Minimum 4 cells that contain formulas. 6 cells should contain information from cell referencing other worksheets within the workbook. Presentation Requirements: 30 points Each Budget must have a layout that is “reader friendly.” The audience for these budgets are “managers” who are not accountants and want to find the information easily. When creating the layout of the budget, please consider the message you are communicating to management. Please feel free to use various fonts, sizes, and color schemes to help emphasize areas of the budget. Helpful Hints Please refer to the budget examples in your textbook for additional information. Also, Excel offers budgets templates and remember the Internet is at your fingertips too! Selected information concerning sales and production for October 2018 is summarized as follows: a. The sales budget must be divided up by product (Backyard and Master). Estimated sales for October by sales territory: Maine: Backyard 280 units at $750 per unit Master 250 units at $1,500 per unit Vermont: Backyard 210 units at $800 per unit Master 160 units at $1,600 per unit New Hampshire: Backyard 305 units at $850 per unit Master 275 units at $1,700 per unit Estimated sales for November for all territories: Backyard Grills 620 grills Master Grills 1,150 grills Outdoor Chef Grill has a beginning accounts receivable balance of $550,000 in the month of October and expects to collect 30% of that balance. In addition, their accounting department has estimated that 80% of sales for the Master Grill will be paid in cash and 70% of the Backyard grill will be paid in cash. b. Inventory of Grills Beginning Inventory of Grills: Backyard 36 units Master 18 units Outdoor Chef Grill has established a new ending inventory policy to take effect the month of October. From October and going forward, the ending inventory should be: Ending Inventory: Backyard 5% of November’s sales Master 2% of November’s sales. c. Estimated direct material inventories at October 1: Direct materials (available for the Master and Backyard Grill): Grates 300 units Stainless steel 1,800 lbs. Burner subassemblies 150 units Shelves 300 units Desired inventories at October 31: Direct materials (available for the Master and Backyard Grill): Grates 320 units Stainless steel 2,100 lbs. Burner subassemblies 135 units Shelves 285 units d. Direct materials used in production: Direct Materials required to produce one Backyard Chef Grill: Grates 3 units per unit of product Stainless steel 24 lbs. per unit of product Burner subassemblies 2 units per unit of product Shelves 4 units per unit of product Direct Materials required to produce one Master Chef grill: Grates 6 units per unit of product Stainless steel 42 lbs. per unit of product Burner subassemblies 4 units per unit of product Shelves 5 units per unit of product Anticipated purchase price for direct materials: Grates $16 per unit Stainless steel $5 per lb. Burner subassemblies $125 per unit Shelves $8 per unit Because of their excellent relationships with the vendors, they pay the following percentages when placing their order: Grates: 10% Stainless Steel: 25% Burner subassemblies 5% Shelves 2%, if pay the entire balance within 35 days. e. Direct labor requirements for one grill: Backyard Chef: Stamping Department 0.60 hr. at $17 per hr. Forming Department 0.80 hr. at $14 per hr. Assembly Department 2.0 hrs. at $12 per hr. Master Chef: Stamping Department .80 hr. at $17 per hr. Forming Department 1.50 hrs. at $14 per hr. Assembly Department 2.50 hrs. at $12 per hr. f. Outdoor Chef Grill Company budgeted the following costs for anticipated production in October. The list below includes fixed MOH expenses and Fixed S&A expenses Advertising Expenses $24,000 Sales monthly salary (not commission) $7,500 Factory Insurance $5,000 Production Supervisor Salary $4,900 Executive officer salaries $25,000 Indirect Labor Salaries for Quality Control $2,800 Factory Depreciation $4,000 Corporate Office Building Depreciation, $3,000 Total estimated VMOH expenses are $41,000 for the month of October. VMOH expenses are allocated to the products based on a single PDOR rate using the allocation base, Direct Material Cost. FMOH is assigned to the products based on DL hours. Note: You will have two PDOR’s for this assignment. Variable S&A expenses are allocated at rate of $3 per grill. g. Other Information: a. Income Tax Rate is 35% b. Minimum Cash Balance $500,000 c. Beginning Cash Balance $750,000