Spring Semester 2019 ABI-WE #2 Name: (INSERT YOUR NAME HERE) PID: (INSERT YOUR PID HERE) CHAPTER 6-7 Company Profile Spartan Furniture: New from Old, Inc. is a furniture manufacturing company founded in 2010 by Spartan entrepreneurial students to make new furniture out of old trees that have either fallen down or had to be cut down from campus. The company has a working relationship with MSU's Forestry Department and Surplus Store to mill wood from trees that are often 100-year old hardwoods. Spartan Furniture designs the furniture and employs local woodworkers in constructing high quality, reasonably priced furniture. A loyal customer base of Spartan alumni who want to enjoy a piece of MSU history in their homes has helped the company grow tremendously over the last seven years. (Note: Spartan Furniture: New from Old, Inc. is a fictitious company as are the data provided; however, it is based on the real MSU's Shadows Collection available through the MSU Surplus Store.) Spartan Furniture has three product lines: 1) Dining room tables 2) Chairs 3) Benches Spartan Furniture is headquartered in Lansing MI where it has a production facility and support services for accounting, marketing, human resource, legal, and other administrative tasks. The company has another production facilities in Escanaba in the Upper Peninsula which specializes in the chairs product line and particular types of wood. Scenario Your boss seems pleased with your work in the first month of your internship, and you are very happy you are more comfortable with data and Excel. You just received the following memo with some new tasks for this month. Task Hi [Your Name]: Thanks for your help so far. You did a great job on the income statement! I need your help for two different tasks this month. First, please run a break-even analysis to determine how many units we must sell to achieve our investors’ goal. Although we had a great profit margin in 2017, our investors want us to stretch to achieve a profit of $3,645,710 next year. Our production and sales team want to know what they need to produce and sell to reach this ambitious target. For this analysis, we’ll assume items in each of our three product lines cost and sell for the same amount. To simplify things, assume our product mix remains constant for any number of units produced, and our existing cost structure remains the same. I included our expense report and a summary of our company’s accounting policies. I also created a template to guide you through your analysis. Second, can you help me analyze the difference between variable and absorption costing? I read a great article about how useful variable costing can be for internal decision making, and I’m confused about how it affects the income statement. Create an absorption and variable costing income statement so we can compare both methods. Use the cost data from the target profit analysis. I included some templates to guide you. Let me know if you have any questions. Thank you! Haidee Brady Controller- Spartan Furniture: New from Old, Inc. Grading Rubric Use of ALL functions VLOOKUP RENAME GOALSEEK PIVOT TABLE IF COMMENT Accuracy (all or nothing) Unit sales to reach target Average selling price to reach target Quality of comment's recommendation Absorption cost of goods sold Variable cost of goods sold Selling expense (absorption costing) Administrative expense (absorption costing) Total fixed costs (variable costing) Reconciliation in cell D31 $ value of fixed MOH in ending inventory (cell D34) Predetermined MOH rate MOH Over (Under) allocated $ amount (cell B45) Selecting over/under allocated (cell C45) Presentation and Communication All numbers formatted correctly TOTAL # Points 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 20 ACC 202 EXCEL DIRECTORY NOTES We expect you know the basics of creating, using, and saving MS-Excel worksheets in workbooks from CSE101. Need an Excel refresher? Email trinkle4@msu.edu and ask to be added to the "BEEP" (Broad Excel Enhancement Program), a D2L community with no fee and available for one year. You will find excellent short videos prepared by the Goodwill Community Foundation on the Excel functions we will use in the ABI-WEs. There are different ways to accomplish a task in Excel, for example: 1. Use the ribbon at the top of the worksheet. Choose a category, then look at each of the folders and click the icon in the bottom right corner of that folder for more options. 2. Use keyboard shortcuts, such as Alt (don't need to hold down), Ctrl (hold down), Shift, and Function keys. 3. Right click to pull up options. 4. Look at Quick Icons (frequently used commands) at the top of the worksheet. EXCEL COMMAND AND FUNCTIONS The following Excel commands below are those we'll use in the ABI-WEs are presented with the path through the ribbon of the most current version of MS-Excel for a PC. Instructions for a MAC are similar but not identical. If confused, use the HELP command. COMMAND/FUNCTION ADD/REMOVE DECIMALS COMMENT INFORMATION Purpose: To increase or decrease the number of decimal places of a number. HOME-->NUMBER-->icons with right and left arrows Purpose: To create a pop up box with the author's name that provides a short message. Indicated by a red triangle in the top, right of the cell. REVIEW-->COMMENTS-->NEW COMMENT GOAL SEEK Goal Seek is a feature that allows you to perform a 'what-if analysis' by having Excel set one cell to a particular value by changing another cell that is referenced by the On the top ribbon in Excel go "Data", then "Data Tools", click "What if Analysis" then Where it says "Set Cell:" reference or click on the cell corresponding to net income in the "Company's Cost Formula" area of the "CVP Analysis" tab. Where it says, "To Value:" enter 6,550,170. Where it says, "By Changing:" reference or click on the cell corresponding to unit sales in the "Company's Cost Formula" area of the "CVP Analysis" tab. For Scenario Click ok. You should see that the unit sales (or selling price in scenario # 2) cell changed in order to make the net income cell equal to $6,550,170. COPY CELLS Purpose: To copy contents of a cell to another cell, either "relatively" (so that formulas adjust) or "absolutely" (as the same value) and paste it to the new cell. Either, drag the bottom right corner of a cell to other cells or enter =B4 to copy "relatively" of =$B$4 to copy "absolutely" FORMAT CELLS FORMAT NUMBERS GRIDLINES Purpose: To change font type and size, color, and alignment of a cell. HOME-->then FONT, ALIGNMENT Purpose: To label numbers so they present in an understandable way. HOME-->NUMBER then drop down menu to choose currency, dates, percentages… Purpose: To remove gridlines so the worksheet is cleaner to look at. VIEW-->SHOW-->GRIDLINES-->check or uncheck IF Purpose: To set up a condition that has to be met; that is, IF X is true, then do this, otherwise, do this. FORMULAS-->FUNCTION LIBRARY-->LOGICAL-->IF e.g., =IF(B6>10,"true","false") PAGE BREAK PREVIEW Purpose: To ensure the worksheet will properly print on 8 1/2"x11" paper. VIEW-->WORKBOOK VIEWS-->PAGE BREAK PREVIEW Note: Drag the blue lines to adjust breaks. Return to Normal. View in FILE -->PRINT before printing. REFERENCING Purpose: To link cells together with reference to formulas within a worksheet or across worksheets, so a change in the original data will flow through to cells that reference that cell. Enter, for example, =B4 when you want to copy the formula in B4 to another cell. SUM Purpose: To add up values in two or more cells. FORMULAS-->FUNCTION LIBRARY-->MATH&TRIG-->SUM e.g., =SUM(B4:B11) SUMIF Purpose: To add up cells that meet the condition described by the IF clause. FORMULAS-->FUNCTION LIBRARY-->MATH&TRIG-->SUMIF TEXT TO COLUMNS VLOOKUP Purpose: To separate data in one cell that is separated by semicolons into separate columns. DATA-->DATA TOOLS -->TEXT TO COLUMNS-->DELIMITED-->SEMICOLON Purpose: To link to data in another worksheet or area of the same worksheet. FORMULAS-->FUNCTION LIBRARY-->LOOKUP & REFERENCE-->VLOOKUP Y heets in workbooks from CSE101. Need EP" (Broad Excel Enhancement ind excellent short videos prepared by ABI-WEs. at each of the folders and click the icon own), Shift, and Function keys. et. presented with the path through the AC are similar but not identical. If TION of decimal places of a number. arrows or's name that provides a short p, right of the cell. rm a 'what-if analysis' by having Excel nother cell that is referenced by the ata Tools", click "What if Analysis" then the cell corresponding to net income in VP Analysis" tab. ck on the cell corresponding to unit the "CVP Analysis" tab. For Scenario rice in scenario # 2) cell changed in ,550,170. cell, either "relatively" (so that value) and paste it to the new cell. o other cells or enter =B4 to copy and alignment of a cell. an understandable way. hoose currency, dates, percentages… t is cleaner to look at. ck met; that is, IF X is true, then do this, ->IF e.g., =IF(B6>10,"true","false") rly print on 8 1/2"x11" paper. EVIEW turn to Normal. View in FILE -->PRINT to formulas within a worksheet or data will flow through to cells that py the formula in B4 to another cell. s. RIG-->SUM e.g., =SUM(B4:B11) on described by the IF clause. RIG-->SUMIF eparated by semicolons into separate >DELIMITED-->SEMICOLON or area of the same worksheet. & REFERENCE-->VLOOKUP TASKS Step 1: Determine the variable and fixed portions of each expense. You have been provided worksheets with an "Expense Report" template and "Accounting Policies" showing how the company classifies expenses. In the "Expense Report" use VLOOKUP to fill in the Type, Classification, and Product/Period columns for each expense with information from the "Accounting Policies" worksheet. Complete the Cost per Unit column in the "Expense Report" worksheet. Use the production data (in blue) at the top right corner of the worksheet. Wh finding cost per unit use an IF statement so that Total Units Produced is used for product costs and Total Units Sold is used for period costs. TIP: You only have to enter a formula once in the top row. REFERENCE (Absolutely) the appropriate cells and COPY (or drag) the formula down to inclu all rows in that column. Step 2: Turn the data into a Pivot Table. Select the data from the "Expense Report" starting with the left column titled, "Acct. #", moving over to include all the columns and down to include ev expense on the trial balance. TIP: It is important to include the row that includes the column titles. Insert a PIVOT TABLE on a new tab. RENAME the tab, "Pivot Table". TIP: You can right click on the name of the worksheet to change its name. Step 3: Pivot the table appropriately to obtain relevant information. In the FIELD LIST next to the Pivot Table, drag Classification and Type into the filter. In the FIELD LIST next to the Pivot Table, drag Account Description to the rows and Total Cost to the sum of values. Be sure the VALUE FIELD SETTINGS set to SUM not COUNT. TIP: right click anywhere in the Pivot Table to open up the Field List choices on the right-hand side. Filter the Pivot Table to list Fixed, MOH costs. Highlight the expense titles and total cost in the Pivot Table and copy and paste them to the "Cost Structure" worksheet in the section for Fixed Overhe (MOH). Next, filter the Pivot Table to list Fixed, selling expenses and copy and paste the expense titles and amounts into the "Cost Structure" tab in the section Fixed Selling. Follow the same steps above to fill in the Fixed Administrative section. On the FIELD LIST next to the Pivot Table, drag Total Cost out of the Sum of Values and drag Cost Per Unit into the Sum of Values. THIS IS AN IMPORTAN STEP! Variable costs should be shown as per unit amounts, NOT total amounts. With the Type filter set to variable, use the Classification filter to find variable cost per unit for Selling, MOH, direct labor, and direct materials. Copy the expense titles and costs per unit for these items from the "Pivot Table" to the "Cost Structure" worksheet and place within the appropriate section. B sure the VALUE FIELD SETTINGS are set to SUM not COUNT. Step 4: Perform a CVP Analysis to determine how to reach a profit of $3,645,710. REFERENCING the information you just copied from the Pivot Table, complete the CVP analysis section in the top right portion of the "Cost Structure" ta For the units sold and average selling price items only, hard-code the amounts into your table or your goal seek function will not work. In the "Find Unit Sales" portion of the "Cost Structure" worksheet, use GOAL SEEK to determine the units sold required to attain a profit of $3,645,710. The average selling price per unit should be $4,900. TIP: You have to have formulas entered before you can use GOAL SEEK. Under the "Find Selling Price" table use GOAL SEEK to determine the per unit selling price required to attain a profit of $3,645,710 assuming unit sales d not increase. Enter the units sold from the top right portion of the "Expense Report". Enter a COMMENT in the cell labeled Comment on the "Cost Structure" worksheet explaining to your boss (Your GTA) two potential ways of reaching t targeted profit based on your GOAL SEEK analysis and which one you recommend. Your comment should be addressed to your boss (Your GTA) and include your name. Be professional in tone and be sure to use PROPER SPELLING. See the example to the right. ompany classifies expenses. with information from the ght corner of the worksheet. When used for period costs. r drag) the formula down to include columns and down to include every eet to change its name. sure the VALUE FIELD SETTINGS are side. et in the section for Fixed Overhead Cost Structure" tab in the section m of Values. THIS IS AN IMPORTANT bor, and direct materials. Copy the ithin the appropriate section. Be portion of the "Cost Structure" tab. on will not work. d to attain a profit of $3,645,710. $3,645,710 assuming unit sales do two potential ways of reaching the d to your boss (Your GTA) and Spartan Furniture: New from Old, Inc. Expense Report Year ended December 31, 2018 Acct. # 3599 3393 3396 3930 3922 3685 3786 3659 3231 3719 3280 3425 3616 3887 3680 3704 3292 3522 3570 3723 3971 3784 3342 3734 3334 3955 Account Description Advertising Expense Employee Benefits-Admin Employee Benefits-Sales Commissions expense Maintenance- Factory Equip Maintenance-Office Equipment Insurance- Property/Equip Insurance-Headquarters Maintenance Maintenance-Headquarters Marketing Expense Packaging Expenses Professional Fees-Legal Services Property Taxes-Factories Property Taxes- Headquarters Rent-Administrative Rent-Factory Equipment Rent-Sales Vehicles Repairs-Factory Repairs-Headquarters Salary-Administrative Staff Salary-Sales Supplies Expense- Admin Supplies Expense- Factory Supplies Expense-Sales Offices Travel-CEO $ Total Cost 50.750 6.500 5.600 483.803 1.460.800 48.000 200.000 72.000 97.000 10.000 507.600 265.000 45.000 29.000 10.000 22.000 100.000 35.000 125.000 5.000 550.000 720.000 14.109 90.000 46.582 6.611 Type Fixed Fixed Fixed Variable Fixed Fixed Fixed Fixed Variable Fixed Fixed Variable Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed Fixed Fixed Fixed Variable Fixed Fixed Classification Selling Admin Selling Selling MOH Admin MOH Admin MOH Admin Selling Selling Admin MOH Admin Admin MOH Selling MOH Admin Admin Selling Admin MOH Selling Admin 3325 3915 3871 3611 3999 3049 3001 Travel-Sales Utilities-Factories Utilities-Headquarters Wages- Line Workers Salaries- Designers Salaries- Factory Supervisors Direct Materials $ 45.000 1.750.350 80.000 400.000 875.000 500.000 5.500.340 Fixed Variable Fixed Variable Fixed Fixed Variable Selling MOH Admin DL MOH MOH DM Product/Period Period Period Period Period Product Period Product Period Product Period Period Period Period Product Period Period Product Period Product Period Period Period Period Product Period Period Cost Per Unit $ 16,94 $ 2,17 $ 1,87 $ 161,54 $ 434,76 $ 16,03 $ 59,52 $ 24,04 $ 28,87 $ 3,34 $ 169,48 $ 88,48 $ 15,03 $ 8,63 $ 3,34 $ 7,35 $ 29,76 $ 11,69 $ 37,20 $ 1,67 $ 183,64 $ 240,40 $ 4,71 $ 26,79 $ 15,55 $ 2,21 Total Units Produced: 3.360 Total Units Sold: 2.995 Period Product Period Product Product Product Product $ $ $ $ $ $ $ 15,03 520,94 26,71 119,05 260,42 148,81 1.637,01 Acct. # 3001 3049 3231 3280 3292 3325 3334 3342 3393 3396 3425 3522 3570 3599 3611 3616 3659 3680 3685 3704 3719 3723 3734 3784 3786 3871 3887 3915 3922 3930 Type Variable Fixed Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed Variable Fixed Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed Fixed Fixed Fixed Variable Fixed Variable Classification DM MOH MOH Selling MOH Selling Selling Admin Admin Selling Selling Selling MOH Selling DL Admin Admin Admin Admin Admin Admin Admin MOH Selling MOH Admin MOH MOH MOH Selling Product/Period Product Product Product Period Product Period Period Period Period Period Period Period Product Period Product Period Period Period Period Period Period Period Product Period Product Period Product Product Product Period 3955 3971 3999 Fixed Fixed Fixed Admin Admin MOH Period Period Product Per Unit Variable Selling Fixed Administrative Grand Total Check: 250,02 Variable Overhead (MOH) Grand Total Check: 613,79 Grand Total Check: Direct Labor Grand Total Check: 119,05 Fixed Selling Direct Materials Grand Total Check: 1637,01 Grand Total Check: Fixed Overhead (MOH) Grand Total Check: Find Unit Sales Total Dollars dministrative d Selling Average selling price Units sold Sales Variable costs Fixed costs Net operating income* Find Selling Price Average selling price Units sold Sales Variable costs Fixed costs Net operating income* 869.220 Comment *For this task only, assume units sold=units produced. 1.410.532 erhead (MOH) 3.164.800 TASKS Step 1: Prepare traditional and variable costing income statements. The "Income Statements" worksheet contains outlines for traditional and variable costing income statements. REFERENCE data from the "Cost Structure" worksheet to complete each income statement. You do not need to fill in the per unit amounts for the fixed costs on the variable costing income statement. Use $4,900 as your selling price per unit and the units sold and units produced at the top of your "Expense Report" tab. TIP: When finding the per unit cost, or going from the per unit cost to total cost, REFERENCE the units sold and units produced at the top of the "Income Statements" worksheet. For example, per unit sales will equal the average selling price as given on the "Cost Structure" worksheet. Total sales will equal the per unit sales multiplied by the units sold, as given on the "Income Statements" worksheet. DO NOT USE THE UNITS SOLD ON THE "Cost Structure" worksheet that resulted from GOAL SEEK. It may be easiest to complete the per unit column first with data from the "Cost Structure" worksheet. Step 2: Reconcile the net operating incomes under traditional and variable costing. Fill out the cells in the reconciliation box to reconcile absorption costing net operating income to variable costing net operating income. HINT: The difference between absorption costing net operating income and variable costing net operating income should be equal to the fixed MOH expensed as a period cost under variable costing, but maintained in ending inventory under absoprtion costing as a product cost. Step 3: Find out how much manufacturing overhead was either over or under allocated. Use the information from Step 1 as well as the information that is given to calculate whether the MOH was over- or under-stated. The company uses direct labor hours to allocate overhead. Step 1 Production Information 2018 Units produced Units sold Spartan Furniture: New From Old, Inc. Absorption Costing Income Statement for the year ended December 31, 2018 Per Unit Total Sales Less: Cost of goods sold Direct materials Direct labor Manufacturing overhead Total cost of goods sold Gross margin Less: Operating expenses Selling expense Administrative expense Net operating income Step 2 Reconciliation of Absorption Costing NOI and Variable Costing NOI Difference between absorption NOI and variable NOI Difference between units produced and units sold Fixed manufacturing overhead per unit produced The dollar value of fixed manufacturing overhead remaining in ending inventory under absorption costing, but expensed under variable costing as a period cost: Step 3 Budgeted direct labor hours Actual direct labor hours Budgeted total MOH Actual total MOH Predetermined MOH rate Overhead allocated MOH over (under) allocated 275.500 262.500 4.100.500 - $ $ Another view (not required): MOH Actual $ Allocated - $ - Spartan Furniture: New From Old, Inc. Variable Costing Income Statement for the year ended December 31, 2018 Per Unit Sales Less: Variable Cost of Goods Sold Direct materials Direct labor Variable overhead Total variable cost of goods sold Less: Variable selling and administrative expenses: Variable selling expense Total variable expenses Contribution margin Less: Fixed costs Fixed manufacturing overhead Fixed selling Fixed administrative Total fixed costs Net operating income Total FOR QUALITY ASSURANCE PURPOSES, BEFORE SUBMITTING YOUR ASSIGNMENT DO ALL OF THE FOLLOWING 1 Enter your name and PID on the top right of the Scenario worksheet. Save your file. Keep it secure, it is your intellectual property. 2 FORMAT all numbers appropriately, include commas where appropriate, etc. 3 Round each total number to the nearest dollar. Round each per unit amount (i.e. prices) to the nearest cent. Round percentages to two decimal places. Use the ADD/REMOVE decimals button (this will change the format from "Accounting" to "Custom", THIS IS FINE). DO NOT use the ROUND function. 4 Enter a COMMENT to alert your teaching assistant anytime you made any significant assumptions. 5 Remember! Check to be sure your file was submitted properly in D2L. If a file cannot be seen or read by your TA, it cannot be graded. REMEMBER: Do not submit this file if it was started on someone else's file. The syllabus clearly defines this as an example of academic dishonesty, and there are consequences. IMPORTANT!!! IMPORTANT!!!
Purchase answer to see full attachment