please help! need formulas! will give thumbs up!
The purpose of this assignment is to revisit the data used to create a contribution format income statement from a previous exercise. The goal for this exercise is to summarize the data for specific quarters in a given year. Once the data is summarized in the contribution format income statement, sales revenue targets will be set based on desired levels of net operating income. The Sales and Cost Data worksheet contains three years of data (2019, 2020, and 2021). The total sales revenue for each of these years is as follows: 2019 = $420,245; 2020 = $408,125; 2021 = $401,750. It is expected that several years of data will be added to the worksheet in the future and additional columns may be added as well The Contribution Format Income Statement exercise should be completed first before proceeding to this exercise. Begin this exercise by opening the file named Chapter 7 ADAS Exercise 3. Complete each of the requirements listed below. Questions that require a written response should be entered on the AnswerSheet worksheet in the workbook. 1. Based on the information provided in the description of this exercise, conduct a data audit by creating a Pivottable. The Pivot Table should appear in a separate worksheet. The item column in the Sales and Cost Data worksheet should be used for the Row labels. The Year column in the same worksheet should be used for the Column labels. Apply a filter to the Item column in the Pivot Table so only the Sales Revenue data is displayed. Remove all grand totals and format values using the Accounting format 2. Based on the description provided with this exercise, and your knowledge from completing the Contribution Format Income Statement exercise, define each of the four components of the DATA Analytics Framework. Space is provided for each component on the AnswerSheet worksheet 3. In the merged cell beginning with cell A4 on the Contribution worksheet, use the CONCATENATE function to create a subheading for the income statement. The subheading should show the fiscal year that is entered into cell B1 and the quarter in cell D1. The data on the income statement will include the quarter entered into cell D1 and all previous quarters. 1 ener Therefore, if quarter 3 is entered into cell D1, the subheading should show the words Through Quarter" and then number 3. 4. Complete the contribution format income statement in the Contribution worksheet in good form. The data pulled from the Sales and Cost Data worksheet should be based on the year entered in cell B1 and for all quarters less than or equal to the quarter entered into cell D1. The income statement should capture any data added to the Sales and Cost Data worksheet through Row 125. HINT: To check the output for the Sales Revenue, change the quarter number to 4 and compare it to the annual sales revenue on the Pivot Table created for the data audit. This Pivot Table can be used to audit other components of the income statement as well. 5. In the range E5:F15 on the Contribution worksheet, complete all calculations to determine the target sales dollars and target sales units needed to achieve a net operating income of zero (break-even) as of the 2nd quarter in the year 2020. 6. Add a data internal control to the merged cell beginning with cell A2 on the Contribution worksheet. There are two data internal control warnings that should be displayed if needed. The first is if the year entered into cell B1 is less than the oldest year or greater than the most recent year on the Sales and Cost Data worksheet. The MIN function can be used to identify the oldest year and the MAX function can be used to identify the most recent year on the Sales and Cost Data worksheet. If the year entered into B1 does not exist in the dataset, the following message should be displayed: "Data for this year is not available!". The second data internal control should evaluate if data has been entered beyond Row 125 on the Sales and Cost Data worksheet. If cell A126 on the Sales and Cost Data worksheet is not blank, the following message should be displayed: "New Data Exceeds Report Capacity!" Use the CONCATENATE function so both data internal control warnings can be displayed if needed. Add space between the two messages in the event they both need to be displayed. HINT: you will need to use IF functions in the CONCATENATE function to display any combination of data internal control warnings that are needed. Add the appropriate entries to test your data internal control. 7. Assess the data design choices that were made for this project. Identify the rationale for 2 data desion features and briefly explain how they.connect to the DATA Analytics Framework JAC real D 1 Year 2021 Quarter 3 2 Contribution Format Income Statement Fiscal Year: 2021 Through Quarter: 3 Target Profit Scenarios Sales Revenue Variable Cost Product SG&A 9 Total Variable Cost Sales Units Sales Target in Dollars Sales Target in Units Variable Cost Ratio Contribution Margin Ratio Contribution Margin per Unit 10 Contribution Margin 11 Fixed Cost 12 Product 13 SG&A 14 Total Fixed Cost 15 Net Operating Income 16 Target Profit D E G H Value A B 1 Year Quarter Item 2 2019 1 Sales Revenue 3 2019 2 Sales Revenue 4 2019 3 Sales Revenue 5 2019 4 Sales Revenue 6 2020 1 Sales Revenue 7 2020 2 Sales Revenue 8 2020 3 Sales Revenue 9 2020 4 Sales Revenue 10 2021 1 Sales Revenue 11 2021 2 Sales Revenue 12 2021 3 Sales Revenue 13 2021 4 Sales Revenue 14 2019 1 Sales Units 15 2019 2 Sales Units 16 2019 3 Sales Units 17 2019 4 Sales Units 18 2020 1 Sales Units 2020 2 Sales Units 20 2020 3 Sales Units 21 2020 4 Sales Units 22 2021 1 Sales Units 23 2021 2 Sales Units 24 2021 3 Sales Units 25 2021 4 Sales Units 26 2019 1 SG&A 27 2019 2 SG&A 28 2019 3 SG&A 29 2019 4 SG&A 30 2020 1 SG&A 31 2020 2 SG&A 32 2020 3 SG&A 33 2020 4 SG&A 34 2021 1 SG&A SCRA ALI Behavior Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Sales Variable Cost Variable Cost Variable Cost Variable Cost Variable Cost Variable Cost Variable Cost Variable Cost Variable Cost Varinha Cart 50050 70200 124995 175000 65000 70125 120000 153000 45000 65000 126000 165750 770 1170 1923 2500 1000 1275 2400 3400 1000 1300 2100 2550 3657.5 5557.5 9134.25 11875 4750 6056.25 11400 16150 4750 L2R 19
