ACCG 211

Excel project 3 instruction:

Note: all calculation in worksheet should be formulated. Please DO NOT hard enter numbers when it should be a calculated or linked cell.

Part 1: Use the consolidated balance sheet from Microsoft (2013 through 2018) provided, see excel worksheet attached to the assignment. Required outcome:

• Set up a separate sheet, named it as chart. Manipulating the original data into the accounts group required by the charting.
• Provide a column chart that Y = $ amount, and X include each account group within the year. Account group required: total current assets, total non-current assets, total assets, total current liabilities, total liabilities, and total stockholder equity. Please see example below.
• Make sure updating chart title and other default info into appropriate report information.
• Based on your first chart data, reorganize data that shows each account category for all 6 years. See example below. Depending on your version of excel, you may consider excel function transpose. For details, see tutorial posted or google you tube video on charting function.
• Make sure update the chart title and all necessary information before submission.

Part 2: CVP worksheet and charting. Please use the following data for Simpson Co., who manufactures Product T. Unit price is $2000, unit variable cost is $1000, and total fixed cost is $750,000.

• Please set up a worksheet sheet name CVP1. Please set up a contribution margin income statement to forecast Simpson Cos income before tax based on the following sales. The forecast should be one report with multi-sales column for comparison purpose. Please DO NOT set up 5 individual income statement.
  • $500,000
  • $1,000,000
  • $1,500.000
  • $2,000,000
  • $2,500,000
• Please show the Breakeven for dollars and sales unit calculation on your worksheet.
• Use line chart function in excel to chart break-even point.
• Please insert your breakeven calculation into the chart as the linked picture.
• Adding the net income line at the bottom of the chart.
• Make sure update the chart title to appropriate names. Your final product should look like example below: