Required information [The following information applies to the questions displayed below.] Crispin Company's sales from last year were $8 million. The company maintains no beginning or ending inventories. It has assembled all of its costs from last year and would like your assistance in sorting these costs into various categories, depicting them graphically, and preparing traditional and contribution format income statements. Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on PivotCharts in Excel. Click here for a brief tutorial on SUMIF in Excel. 4. Assume the company wants to adapt the information in the "Cost Summary" tab to prepare a contribution format income statement. Within the "Income Statements" tab of your template, and using the Sum IFS function to quantify all variable and fixed expenses: a. Prepare a contribution format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, and variable manufacturing overhead. The fixed manufacturing overhead costs should be included within the "Fixed expenses" portion of the income statement.) b. Based on the contribution format income statement, what is the contribution margin? c. Based on the contribution format income statement, what is the net operating income? Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 4C Prepare a contribution format income statement. (Hint: The cost of goods sold should include all direct materials, direct la and variable manufacturing overhead. The fixed manufacturing overhead costs should be included within the "Fixed exper portion of the income statement.) The cost of goods sold in this income statement is Req 4A Req 4B > Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 4C Based on the contribution format income statement, what is the contribution margin? The contribution margin is < Req 4A Req 4C > Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 4C Based on the contribution format income statement, what is the net operating income? The net operating income is < Req 4B Req 4C > Charts are a way to easily visualize your data sets in order to present findings or to gain further insights. There are a wide variety of chart types that you can utilize in Microsoft Excel. The most common types used are bar/column charts to show comparisons of values and line charts to show trends. Below is an example of how to set up a chart: 1. The first step is to select the data you would like to chart out. In general, you want to format your data in a way that you have column headers like days and then row headers such as your cost or company. See the below as an example: 2. Day 1 Day 2 Day 3 Cost A 55 65 50 Cost B 88 80 85 Cost C 60 70 65 Highlight the data you what to create a chart of: Day 1 Day 2 Day 3 Cost A 55 65 50 Cost B 88 80 85 3. Go to the "Ins Cost C 60 70 65 4. Click on "Recommended Charts, which will generate an misert chart" window a. b. Within this window you have two tabs: "Recommended Charts" and "All Charts" 1. The "Recommended Charts" tab will give you several charts that are recommended for the specific data set you have chosen 2. The "All Charts" tab will let you dictate the type of chart you want. You will see that on this tab, the left-hand side shows all the different chart types and as you click on them you will see the right-hand pane generate the different subtypes of charts available for that group. On the "All Charts" tab, choose the "Line" option from the left-hand pane 1. On the right-hand side look through the various options. If we want to show a trend line with the data points marked, we would choose the option titled "Line with Markers". Chose this option and click "OK" 2. The below chart will generate Chart Title 100 90 80 70 60 50 30 20 10 5. You will see Day 1 Day 2 Day 3 uch as the following: Cost A Cast B Cost C a. Doub b. You can right click on either the X (here Day 1, Day 2,....) or Y (here 0-100) axes and click "Format Axis" pane will generate giving you a plethora of options to modify the axes C. Another set of easy tools is to click on the chart and within the "Chart Design" ribbon click on "Add Chart Element" within the "Chart Layouts" section 1. Within this section you can add titles to the axes, add other labels, create legends, etc. SumIF Function: SumIF is a function (formula) within Microsoft Excel that will add together cells within a range of defined cells that meet a specified criteria. This can be used if you want to add up all sales for a specific state. Below is a description of the Excel formula and an example of how to use it: 1. The SUMIF function has the following arguments: a. =SUMIF(range, criteria, sum_range) where: 1. 1. The "range" is the selection of data (cells) that contains the criteria that you are interested in meeting. These values should correspond (be in the same row) as the data you are interested in summing 2. The "criteria" is the specific logic or value that you want to be met in the "range" in order to consider summing the corresponding value a) If this is a number you can just enter the number b) If this is text you must put it in quotations"""" c) You can also reference another cell such as B5 here to be the result to be shown if true 1. The "sum_range" is the range data (cells) that correspond with the "range" that you want to total (sum) if they meet the criteria set forth in "criteria" The following is an example of a situation and the SumIF function. Using the below table, you want to sum the "Values" only where "VA" is the "State": a. A B 1 State Value 2 VA 2 3 NC 5 4 VA 5 NC 7 8 6 Total VA 7 In Cell B6 you would enter in the following formula: 1. =SUMIF(A2:A5, "VA",B2:B5) a) The "range" is the list of states (A2:A5) b) The "criteria" is "VA" indicating that we only want to sum the items in the "sum_range" if they equal "VA" c) The "sum_range" is the values (B2:B5) corresponding to each state in each row of the "range" A B D A B 1 State Value 1 State Value 2 VA 2 2 VA 2 3 NC 5 3 NC 5 4 VA 7 4 VA 7 5 NC 8 5 NC 8 6 Total VA |=SUMIF(A2:A5,"VA",B2:B5)| 6 Total VA 9 Product/ Manufacturing/ Account Equipment depreciation: Molding Amount Period Nonmanufacturing Fixed/Variable $ 400,000 Product MOH Fixed Equipment depreciation: Fabrication $ 300,000 Product MOH Fixed Equipment depreciation: Assembly $ 50,000 Product MOH Fixed Equipment depreciation: Materials Handling $ 15,000 Product MOH Fixed Equipment depreciation: Other $ 10,000 Product MOH Fixed Production suprvision: Molding $ 60,000 Product MOH Fixed Production supervision: Fabrication $ 65,000 Product MOH Fixed Production supervision: Assembly $ 58,000 Product MOH Fixed Indirect labor: Maintenance & Repairs $ 72,000 Product MOH Fixed Indirect labor: Materials Handling $ 68,000 Product MOH Fixed Indirect labor: Engineering $ 170,000 Product MOH Fixed Indirect labor: Raw Materials Purchasing $ 93,000 Product MOH Fixed Indirect labor: Quality Control $ 38,000 Product MOH Fixed Indirect labor: Packaging & Shipping $ 99,000 Product MOH Fixed Indirect materials: Molding $ 18,000 Product MOH Variable Indirect materials: Fabrication $ 13,000 Product MOH Variable Indirect materials: Assembly $ 8,000 Product MOH Variable Utilities: Molding $ 24,000 Product MOH Variable Utilities: Fabrication $ 20,000 Product MOH Variable Utilities: Assembly $ 6,000 Product MOH Variable Utiltiies: Plant lighting and other equipment $ 12,000 Product MOH Fixed Utilities: Heating and cooling $ 24,000 Product MOH Fixed Property insurance $ 26,000 Product MOH Fixed Liability insurance $ 18,000 Product MOH Fixed Medical insurance $ 45,000 Product MOH Fixed Workers' compensation insurance $ 13,000 Product MOH Fixed Other salaries: Plant manager $ 165,000 Product MOH Fixed Other salaries: Accounting $ 89,000 Product MOH Fixed Other salaries: Administrative support $ 56,000 Product MOH Fixed Other salaries: Janitorial $ 48,000 Product MOH Fixed Other salaries: Security and grounds maintenance $ 44,000 Product MOH Fixed Property taxes $ 18,000 Product MOH Fixed Payroll taxes: Direct labor $ 79,000 Product MOH Variable Payroll taxes: Other $ 165,000 Product MOH Fixed Direct materials Direct labor $ 2,220,000 Product Direct materials Variable $ 1,365,000 Product Direct labor Variable Accounting manager's salary $ 88,000 Period Administrative Fixed Administrative office insurance Advertising Depreciation on office equipment $ 2,000 Period Administrative Fixed $ 400,000 Period Selling Fixed $ 15,000 Period Administrative Fixed Heat & electricity for the general office $ 4,000 Period Administrative Fixed Janitorial services for administrative offices $ 26,000 Period Administrative Fixed Salaries of accounts receivable/payable clerks $ 78,000 Period Administrative Fixed Sales commissions $ 480,000 Period Selling Variable Sales manager's salary $ 120,000 Period Selling Fixed Supplies for administrative office $ 1,000 Period Administrative Fixed Customer billing supplies $ 2,000 Period Administrative Variable PivotTable Charts SumlF Cost Summary P Income Statements + Sales Crispin Company Traditional Format Income Statement Cost of goods sold Gross margin Selling and administrative expenses: Selling Administrative Net operating income Crispin Company $ 8,000,000 $ 8,000,000 S sll $ $ 8,000,000 $ 8,000,000 Sales Contribution Format Income Statement Variable expenses: Cost of goods sold Variable selling Variable administrative $ Contribution margin Fixed expenses: Fixed selling Fixed administrative Net operating income $ 8,000,000 Fixed manufacturnig overhead As es $ 8,000,000 Fill In Auto-Calc