Home Insert Draw Page Layout Formulas Data Review View Developer Tell me X Verdana Conditional Formatting 10 Ai % Format as Table Paste B I U v A Alignment Number Cell Styles A9 fx evaluate each of the top five product lines' profitability, as well as the product line manager's pri A B D E F H I 6 Points will be deducted for not following the instructions. Turn in the entire workbook on Blackboard. See Projects 7 8 Background: Mars Inc. is an American multinational corporation that is one of the world's largest suppliers of candy, nutritious meals, and other treats. Prior to the beginning of each year, one of the product line managers create a projection for both sales and cost of goods sold for their assigned products. Mars' VP of Product Management has asked you to prepare a summary document that will allow 9 her to evaluate each of the top five product lines' profitability, as well as the product line manager's projection accuracy. 10 Instructions 1) Important: enter your name, student ID, and class time in the Budget Summary worksheet, cells B1 - B3! Your 11 answers are based on your student number. 2) Please prepare a summary of Mars Inc.'s 2020 sales, cost of goods sold, and gross profits for each of the five product lines (the data needed are provided in the 'Transactions' worksheet, the template for the summary is provided in the 'Budget Summary' 12 worksheet). a. Include the 2020 performance, account numbers, projected values(provided), an indication of whether each product line was 13 over the projected amounts, under the projected amounts, or the equal to the projected amounts. 14 b. Do not enter number or letters into the Budget Summary worksheet - use ONLY formulas. C. Do the calculations in the following order: a) account number, b) 2020 actual, and 3) over/under/equal to budget. Identify 15 what formula to use for each item. d. You must use a nested IF statement with the correct syntax [i.e., IF(logical test, value if true, value if false)]. The formula will work with three IFs, however, that is poor syntax and it is inefficient, especially when building more complex 16 formulas. 3) Additionally, please provide a brief paragraph describing the accuracy of the projections and the overall performance of the 3Musketeers. Be sure to include any applicable ratio analysis to support your answer. A portion of your grade will be based on your ability to concisely communicate this. DO Not just reinterate the numbers on the Budget Summary tab. Remember you are 17 send this to the VP of Product Management. 18 19 Example: (The shaded data is provided, the other cells require formulas) 20 Note: Your numbers will not match the numbers below. All financial information provide is hypothetical data. 21 22 23 Mars, Inc. Revenues and COGS Budget 12/31/20 2020 Actual 2020 Budget Over/Under Budget Account Number Revenues Twix Snickers 3Musketeers Milky Way M&Ms Total Revenues $489,552 273,563 222,221 159, 151 152,626 $1,297,114 $445,492 $273,563 $204,444 $136,870 $125,153 $1,185,523 Over Equal Over Over Over Over 100100 100500 100200 100400 100300 Cost of Goods Sold Twix Snickers 3 Musketeers Milky Way M&Ms Total COGS $397,751 $184,640 $232,364 $55,745 $44,468 $914,969 $361,954 $178,917 13,775 $63,761 $36,464 $854,870 Over Over Over Under Over Over 200100 200500 200200 200400 200300 Gross Profit Twix Snickers 3Musketeers Milky Way M&Ms Total Gross Profit $91,801 88,923 (10,143) 103,407 108,157 $382,145 $83,539 94,647 (9,332) 73,110 88,689 $330,653 Over Over Under Over Over Over D DI Cell Styles SUMIFS 4x fx ='Transactions 2019 - 2020'! A B D Formula Bar G H I Revenue Account Number 100100 100200 100300 100400 100500 COGS Account Number 200100 200200 200300 200400 200500 1 Trans Date 2 1 12/9/19 3 1 12/9/19 4 2 12/9/19 5 2 12/9/19 6 3 12/10/19 7 3 12/10/19 8 4 12/10/19 9 4 12/10/19 10 5 12/11/19 11 5 12/11/19 12 6 12/12/19 13 6 12/12/19 14 7 12/13/19 15 7 12/13/19 16 8 12/13/19 17 8 12/13/19 18 9 12/13/19 19 9 12/13/19 20 10 12/13/19 21 10/12/13/19 22 11 12/13/19 23 11 12/13/19 Account Number 100100 200100 100200 200200 100200 200200 100300 200300 100300 200300 100400 200400 100300 200300 100500 200500 100100 200100 100200 200200 100100 200100 Revenue COGS Product $9,791.04 Twix $7,911.16 M&Ms $3,950.60 Snickers $4,148.13 Milky Way $1,975.30 3Musketeers $2,074.07 $6,439.90 $2,073.65 $2,575.96 $713.54 $1,408.42 $470.41 $6,439.90 $1,957.73 $2,486.94 $1,611.54 $4,895.52 $3,852.77 $9,876.50 $10,370.33 $24,477.60 $19,949.24 12112112110 1nnnn 1 Ona Mars, Inc. Revenues and Expenses Budget 12/31/20 2020 Actual 2020 Budget Over/Under Budget Account Number E27 Revenues Twix 3Musketeers M&Ms Milky Way Snickers Total Revenues $1,748,680 $1,122,853 $706,565 $481,553 $625,935 $4,685,586 Cost of Goods Sold Twix 3Musketeers M&Ms Milky Way Snickers Total COGS $1,511,208 $760,281 $739,028 $168,481 $136,936 $3,315,935 Gross Profit Twix 3Musketeers M&Ms Milky Way Snickers Total Gross Profit $237,472 $362,572 -$32,463 $313,072 $488,999 $1,369,651 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me X Verdana Conditional Formatting 10 Ai % Format as Table Paste B I U v A Alignment Number Cell Styles A9 fx evaluate each of the top five product lines' profitability, as well as the product line manager's pri A B D E F H I 6 Points will be deducted for not following the instructions. Turn in the entire workbook on Blackboard. See Projects 7 8 Background: Mars Inc. is an American multinational corporation that is one of the world's largest suppliers of candy, nutritious meals, and other treats. Prior to the beginning of each year, one of the product line managers create a projection for both sales and cost of goods sold for their assigned products. Mars' VP of Product Management has asked you to prepare a summary document that will allow 9 her to evaluate each of the top five product lines' profitability, as well as the product line manager's projection accuracy. 10 Instructions 1) Important: enter your name, student ID, and class time in the Budget Summary worksheet, cells B1 - B3! Your 11 answers are based on your student number. 2) Please prepare a summary of Mars Inc.'s 2020 sales, cost of goods sold, and gross profits for each of the five product lines (the data needed are provided in the 'Transactions' worksheet, the template for the summary is provided in the 'Budget Summary' 12 worksheet). a. Include the 2020 performance, account numbers, projected values(provided), an indication of whether each product line was 13 over the projected amounts, under the projected amounts, or the equal to the projected amounts. 14 b. Do not enter number or letters into the Budget Summary worksheet - use ONLY formulas. C. Do the calculations in the following order: a) account number, b) 2020 actual, and 3) over/under/equal to budget. Identify 15 what formula to use for each item. d. You must use a nested IF statement with the correct syntax [i.e., IF(logical test, value if true, value if false)]. The formula will work with three IFs, however, that is poor syntax and it is inefficient, especially when building more complex 16 formulas. 3) Additionally, please provide a brief paragraph describing the accuracy of the projections and the overall performance of the 3Musketeers. Be sure to include any applicable ratio analysis to support your answer. A portion of your grade will be based on your ability to concisely communicate this. DO Not just reinterate the numbers on the Budget Summary tab. Remember you are 17 send this to the VP of Product Management. 18 19 Example: (The shaded data is provided, the other cells require formulas) 20 Note: Your numbers will not match the numbers below. All financial information provide is hypothetical data. 21 22 23 Mars, Inc. Revenues and COGS Budget 12/31/20 2020 Actual 2020 Budget Over/Under Budget Account Number Revenues Twix Snickers 3Musketeers Milky Way M&Ms Total Revenues $489,552 273,563 222,221 159, 151 152,626 $1,297,114 $445,492 $273,563 $204,444 $136,870 $125,153 $1,185,523 Over Equal Over Over Over Over 100100 100500 100200 100400 100300 Cost of Goods Sold Twix Snickers 3 Musketeers Milky Way M&Ms Total COGS $397,751 $184,640 $232,364 $55,745 $44,468 $914,969 $361,954 $178,917 13,775 $63,761 $36,464 $854,870 Over Over Over Under Over Over 200100 200500 200200 200400 200300 Gross Profit Twix Snickers 3Musketeers Milky Way M&Ms Total Gross Profit $91,801 88,923 (10,143) 103,407 108,157 $382,145 $83,539 94,647 (9,332) 73,110 88,689 $330,653 Over Over Under Over Over Over D DI Cell Styles SUMIFS 4x fx ='Transactions 2019 - 2020'! A B D Formula Bar G H I Revenue Account Number 100100 100200 100300 100400 100500 COGS Account Number 200100 200200 200300 200400 200500 1 Trans Date 2 1 12/9/19 3 1 12/9/19 4 2 12/9/19 5 2 12/9/19 6 3 12/10/19 7 3 12/10/19 8 4 12/10/19 9 4 12/10/19 10 5 12/11/19 11 5 12/11/19 12 6 12/12/19 13 6 12/12/19 14 7 12/13/19 15 7 12/13/19 16 8 12/13/19 17 8 12/13/19 18 9 12/13/19 19 9 12/13/19 20 10 12/13/19 21 10/12/13/19 22 11 12/13/19 23 11 12/13/19 Account Number 100100 200100 100200 200200 100200 200200 100300 200300 100300 200300 100400 200400 100300 200300 100500 200500 100100 200100 100200 200200 100100 200100 Revenue COGS Product $9,791.04 Twix $7,911.16 M&Ms $3,950.60 Snickers $4,148.13 Milky Way $1,975.30 3Musketeers $2,074.07 $6,439.90 $2,073.65 $2,575.96 $713.54 $1,408.42 $470.41 $6,439.90 $1,957.73 $2,486.94 $1,611.54 $4,895.52 $3,852.77 $9,876.50 $10,370.33 $24,477.60 $19,949.24 12112112110 1nnnn 1 Ona Mars, Inc. Revenues and Expenses Budget 12/31/20 2020 Actual 2020 Budget Over/Under Budget Account Number E27 Revenues Twix 3Musketeers M&Ms Milky Way Snickers Total Revenues $1,748,680 $1,122,853 $706,565 $481,553 $625,935 $4,685,586 Cost of Goods Sold Twix 3Musketeers M&Ms Milky Way Snickers Total COGS $1,511,208 $760,281 $739,028 $168,481 $136,936 $3,315,935 Gross Profit Twix 3Musketeers M&Ms Milky Way Snickers Total Gross Profit $237,472 $362,572 -$32,463 $313,072 $488,999 $1,369,651