Do not submit an answer like this:

PLEASE DO THIS THE RIGHT WAY, SHOW EXCEL FORMULAS FOR ALL 3 QUESTIONS

1. Enter a CONCATENATE function into the merged cell beginning with cell A4 on the Contribution worksheet. The function should combine the words Fiscal Year: and the year that is entered into cell B1. Note that there are two spaces that should be added after the colon following the words Fiscal Year.
2. Enter a SUMIFS function into cell C5 on the Contribution worksheet that sums the Sales Revenue on the Sales and Cost Data worksheet based on the year that is entered into cell B1 on the Contribution worksheet. The data to be summed is in Column E on the Sales and Cost Data worksheet. The function should find a match in the Year column on the Sales and Cost Data worksheet to the year entered in cell B. The function should also find a match to the item in cell A5 (Sales Revenue) in Column C on the Sales and Cost Data worksheet. Add cell capacity to include Row 100 on the Sales and Cost Data worksheet.
3. Enter a SUMIFS function into cell B7 on the Contribution worksheet that sums the variable product costs on the Sales and Cost Data worksheet. The setup of this function is identical to the SUMIFS function in step 3 with the following changes: This function should find a match to the item in cell A6 (Variable Cost) in Column C on the Sales and Cost Data worksheet. This function should also find a match to the item in cell A7 (Product) in Column D on the Sales and Cost Data worksheet. Add cell capacity to include Row 100 on the Sales and Cost Data worksheet.

Anonymous answered this 496 answers Was this answer helpful? Bo : =SUMIFS('Sales and Cost data'!$E$2:$E$100,'Sales and Cost data'!$C$2:$C$100,Contribution!$A$7,'Sales and Cost data'!$D$2:$D$100,Contribution!$A$6) This is the correct formula for problem 4. Explanation: 'Sales and Cost data'!$E$2:$E$100 : This module selects the column E 'Value from the Sales and cost data worksheet to add it against the requirement. 'Sales and Cost data'!$C$2:$C$100 : This module checks the column C 'Item' from the sales and cost data worksheet to hit match with the stated required cell mentioned below. Contribution!$A$7: This module states the requirement to be checked against the search mentioned above. Column C will be checked against Product (Cell AZ in the contribution worksheet). Sales and Cost data'ISD$2:$D$100. This module A1 fx Year B D E F H 1 Year 2 Contribution Format Income Statement 3 4 Target Profit Scenarios 5 Sales Revenue 6 Variable Cost 7 Product 8 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 Target Profit 15 Net Operating Income 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Contribution Sales and Cost Data + H3 fx E F A B C D 1 Year Quarter Behavior Item Value 2 2019 1 Sales Sales Revenue 3 2019 2 Sales Sales Revenue 4 2019 3 Sales Sales Revenue 5 2019 4 Sales Sales Revenue 6 2020 1 Sales Sales Revenue 7 2020 2 Sales Sales Revenue 8 2020 3 Sales Sales Revenue 9 2020 4 Sales Sales Revenue 10 2021 1 Sales Sales Revenue 11 2021 2 Sales Sales Revenue 12 2021 3 Sales Sales Revenue 13 2021 4 Sales Sales Revenue 14 2019 1 Sales Sales Units 15 2019 2 Sales Sales Units 16 2019 3 Sales Sales Units 17 2019 4 Sales Sales Units 18 2020 1 Sales Sales Units 19 2020 2 Sales Sales Units 20 2020 3 Sales Sales Units 21 2020 4 Sales Sales Units 22 2021 1 Sales Sales Units 23 2021 2 Sales Sales Units 24 2021 3 Sales Sales Units 25 2021 4 Sales Sales Units 26 2019 1 Variable Cost SG&A 27 2019 2 Variable Cost SG&A 28 2019 3 Variable Cost SG&A 29 2019 4 Variable Cost SG&A 30 2020 1 Variable Cost SG&A 31 2020 2 Variable Cost SG&A 32 2020 3 Variable Cost SG&A 33 2020 4 Variable Cost SG&A 34 2021 1 Variable Cost SG&A 35 2021 2 Variable Cost SG&A 36 2021 3 Variable Cost SG&A 37 2021 4 Variable Cost SG&A 38 2019 1 Fixed Cost SG&A 39 2019 2 Fixed Cost SG&A 40 2019 3 Fixed Cost SG&A 41 2019 4 Fixed Cost SG&A 42 2020 1 Fixed Cost SG&A 43 2020 2 Fixed Cost SG&A 44 2020 3 Fixed Cost SG&A 45 2020 4 Fixed Cost SG&A Contribution Sales and Cost Data 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 6175 9975 12112.5 30000 30000 30000 30000 30000 30000 30000 30000 + Anonymous answered this 496 answers Was this answer helpful? Bo : =SUMIFS('Sales and Cost data'!$E$2:$E$100,'Sales and Cost data'!$C$2:$C$100,Contribution!$A$7,'Sales and Cost data'!$D$2:$D$100,Contribution!$A$6) This is the correct formula for problem 4. Explanation: 'Sales and Cost data'!$E$2:$E$100 : This module selects the column E 'Value from the Sales and cost data worksheet to add it against the requirement. 'Sales and Cost data'!$C$2:$C$100 : This module checks the column C 'Item' from the sales and cost data worksheet to hit match with the stated required cell mentioned below. Contribution!$A$7: This module states the requirement to be checked against the search mentioned above. Column C will be checked against Product (Cell AZ in the contribution worksheet). Sales and Cost data'ISD$2:$D$100. This module A1 fx Year B D E F H 1 Year 2 Contribution Format Income Statement 3 4 Target Profit Scenarios 5 Sales Revenue 6 Variable Cost 7 Product 8 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 Target Profit 15 Net Operating Income 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Contribution Sales and Cost Data + H3 fx E F A B C D 1 Year Quarter Behavior Item Value 2 2019 1 Sales Sales Revenue 3 2019 2 Sales Sales Revenue 4 2019 3 Sales Sales Revenue 5 2019 4 Sales Sales Revenue 6 2020 1 Sales Sales Revenue 7 2020 2 Sales Sales Revenue 8 2020 3 Sales Sales Revenue 9 2020 4 Sales Sales Revenue 10 2021 1 Sales Sales Revenue 11 2021 2 Sales Sales Revenue 12 2021 3 Sales Sales Revenue 13 2021 4 Sales Sales Revenue 14 2019 1 Sales Sales Units 15 2019 2 Sales Sales Units 16 2019 3 Sales Sales Units 17 2019 4 Sales Sales Units 18 2020 1 Sales Sales Units 19 2020 2 Sales Sales Units 20 2020 3 Sales Sales Units 21 2020 4 Sales Sales Units 22 2021 1 Sales Sales Units 23 2021 2 Sales Sales Units 24 2021 3 Sales Sales Units 25 2021 4 Sales Sales Units 26 2019 1 Variable Cost SG&A 27 2019 2 Variable Cost SG&A 28 2019 3 Variable Cost SG&A 29 2019 4 Variable Cost SG&A 30 2020 1 Variable Cost SG&A 31 2020 2 Variable Cost SG&A 32 2020 3 Variable Cost SG&A 33 2020 4 Variable Cost SG&A 34 2021 1 Variable Cost SG&A 35 2021 2 Variable Cost SG&A 36 2021 3 Variable Cost SG&A 37 2021 4 Variable Cost SG&A 38 2019 1 Fixed Cost SG&A 39 2019 2 Fixed Cost SG&A 40 2019 3 Fixed Cost SG&A 41 2019 4 Fixed Cost SG&A 42 2020 1 Fixed Cost SG&A 43 2020 2 Fixed Cost SG&A 44 2020 3 Fixed Cost SG&A 45 2020 4 Fixed Cost SG&A Contribution Sales and Cost Data 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 6175 9975 12112.5 30000 30000 30000 30000 30000 30000 30000 30000 +