Midterm Data Q Search Sheet Home Page Layout Formulas Data Review View + Share A Insert & cut 11 Calibri (Body) AutoSum , A4 A- Wrap Text General H 49 H Copy Paste B1 u A SEE Merge & Center $ % %) 1.0 .00 .00 >.0 Insert Delete Format Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. fx 10 D H L M N O P E - Cash Discount - 1 2 3 1 j -t Blocked One Time Customer 0 0 0 0 0 4 5 6 7 8 9 0 0 0 0 0 0 0 10 11 12 13 14 15 16 17 18 19 20 21 0 o 0 B Account Number Account Name Customer Group Conan Rudd Individual 10.Darius O'Quinn Individual 100 Rylee Dalton Business 1000 Hester Prince Business 1001 OJ Mayo Individual 101 Joan Irvine Individual 102 Monet Santos Business 103 Chaim Carter Business 105 Eva Goddard Business 106 Keely Durham Individual 107 Romana Simons Individual 108 Cody Munoz Individual 109 Lucie Taylor Business 11 Faith Velez Individual 110 Mathew Esquivel Business 111 Alisa Lister Business 112 Muskaan Rudd Individual 113 Kory Torres Business 114 Katrina Maxwell Individual 115 Elowen Munro Individual 116 Mackenzie Collins Business 117 Nikkita Jacobson Business 118 Eloisa Felix Individual 119 Taryn Snow Individual 12 Myah Hackett Business 120 Jeremiah England Individual 121 Krista Boyle Individual 122 Ava Bassett Business 123 Tyreke Whitaker Individual 124 Eddie Atkins Individual 125 Jade Howarth Individual 126 Scarlette Fry Individual 177 anstradell Indisidan Customer Master Product Master Sales -Payment Terms Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 F G Currency Sales Manager USD Craig Battle USD Musab Bond USD Aadil Chen USD Finlay Bourne USD Mujtaba Kidd USD Lilli Humphries USD Finlay Bourne USD Tolga Spooner USD Aadil Chen USD Miyah Lambert USD Clement Howarth USD Syeda Sheppard USD Hisham Graves USD Magnus Dean USD Tolga Spooner USD Syeda Sheppard USD Reiss Finley USD Danni Cameron USD Meadow Rivas USD Tolga Spooner USD Saul Talbot USD Hisham Graves USD Anais Morton USD Saul Talbot USD Tolga Spooner USD Hendrix Strickland USD Saul Talbot USD Natan Coulson USD Sara Sandoval USD Magnus Dean USD Saul Talbot USD Finlay Bourne Cn larameliuinartan laram Address ID AD20-1 AD20-2 AD20-3 AD20-4 AD20-5 AD20-6 AD20-7 AD20-8 AD20-9 AD20-10 AD20-11 AD20-12 AD20-13 AD20-14 AD20-15 AD20-16 AD20-17 AD20-18 AD20-19 AD20-20 AD20-21 AD20-22 AD20-23 AD20-24 AD20-25 AD20-26 AD20-27 AD20-28 AD20-29 AD20-30 AD20-31 AD20-32 0 0 0 0 0 0 PAYMMODE 1 NULL 1 Check 0 Check 1 Credit Card O Check 1 Check 1 Check 0 Check 1 Check 1 Check 0 Check 1 Check 1 Check O NULL 1 Check 0 Check 0 Check 1 Check 1 Credit Card 1 Check 1 Check 0 Check 0 Check 1 Check 1 NULL 1 Check O Check 1 Check 1 Check 1 Check 1 Check O Check n chart 0 22 23 24 25 26 27 28 29 30 31 32 0 o 0 0 0 0 0 0 0 0 0 33 21 Maan LA Customer Address + Ready PH + 100% w Midterm Q Search in Document Home Insert Design Layout References Mailings Review View + Share A Calibri (Body) A *** AU T AaBbCcDdEe 11 -A- A A abe X2 X? A AaBbCcDdEe . AaBbCcDc AaBbCcDdEe AaBb AaBb CcDd Ee AaBbCcDdEE Paste Normal No Spacing Heading 1 Heading 2 Title Subtitle Subtle Emph... Styles Pane Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates Excel Notes: You are a knowledge worker at a national company that manufactures and sells lawn machinery (lawn mowers, leaf blowers, etc.). Business is going well but profits were down for 2019 and the CEO is concerned. The CEO has a hunch and wants to see profit by region, she has tasked you with putting that information together in a dashboard format so she can take it to the senior leaders. From working with her in the past you know that she is not a 'numbers' person and wants to see the information in a chart or graph to make trends more apparent. As a knowledge worker you understand data manipulation and you have business experience to know what data is appropriate. Your gut instinct tells you that profit by region is just the starting point. Make sure to give the CEO what she wants but also find out the real reason for the dip in profits. You have extracted a few tables from your ERP system: Customer Master, Product Master, Sales, and Customer Address. You think that everything you need is contained in these tables but you can always go back to the CEO if you have questions about the request; your challenge is to bring the right information together to find the reason behind the 2019 poor performance. As you work through this problem remember the steps of business intelligence: 1. Business understanding: Understand the question 2. Data understanding: Determine what data you are looking for, where it is, and how to get it 3. Data preparation: Look for data inconsistencies that need scrubbed 4. Data modeling: Identify trends and patterns 5. Evaluation: Analyze to assess potential for solving problem 6. Deployment: Deploy the discoveries into everyday business You will need to use the following formulas: 1. Nested IF 2. Sumit 3. Year 4. Left/right 5. Vlaskur All work should be done in Excel, but the final product should be in Power Point. You should submit your working Excel file and your final Power Point file. You do not need a large number of slides, but you need to display what the CEO is asking for the reason behind the decrease in profits for 2019, and recommendations to remedy the situation. Company Notes: 1. All your products have a different profit margin a. Lawnmowers: 20% b. Tractors: 40% c. Leaf blowers: 25% d. Snow blowers: 50% Weed wackers: 25% 2. Category in the Product Master table) relates to source a. 1.Core business b. 2 Acquisition c. 3 Outsourced 3. Country Code (in the Product Master table) relates to manufacturing location a. 0.US b. 1 Mexico e. Page 2 of 2 445 Words LE English (US) Focus + 94% Midterm Data Q Search Sheet Home Page Layout Formulas Data Review View + Share A Insert & cut 11 Calibri (Body) AutoSum , A4 A- Wrap Text General H 49 H Copy Paste B1 u A SEE Merge & Center $ % %) 1.0 .00 .00 >.0 Insert Delete Format Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. fx 10 D H L M N O P E - Cash Discount - 1 2 3 1 j -t Blocked One Time Customer 0 0 0 0 0 4 5 6 7 8 9 0 0 0 0 0 0 0 10 11 12 13 14 15 16 17 18 19 20 21 0 o 0 B Account Number Account Name Customer Group Conan Rudd Individual 10.Darius O'Quinn Individual 100 Rylee Dalton Business 1000 Hester Prince Business 1001 OJ Mayo Individual 101 Joan Irvine Individual 102 Monet Santos Business 103 Chaim Carter Business 105 Eva Goddard Business 106 Keely Durham Individual 107 Romana Simons Individual 108 Cody Munoz Individual 109 Lucie Taylor Business 11 Faith Velez Individual 110 Mathew Esquivel Business 111 Alisa Lister Business 112 Muskaan Rudd Individual 113 Kory Torres Business 114 Katrina Maxwell Individual 115 Elowen Munro Individual 116 Mackenzie Collins Business 117 Nikkita Jacobson Business 118 Eloisa Felix Individual 119 Taryn Snow Individual 12 Myah Hackett Business 120 Jeremiah England Individual 121 Krista Boyle Individual 122 Ava Bassett Business 123 Tyreke Whitaker Individual 124 Eddie Atkins Individual 125 Jade Howarth Individual 126 Scarlette Fry Individual 177 anstradell Indisidan Customer Master Product Master Sales -Payment Terms Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 Net 30 F G Currency Sales Manager USD Craig Battle USD Musab Bond USD Aadil Chen USD Finlay Bourne USD Mujtaba Kidd USD Lilli Humphries USD Finlay Bourne USD Tolga Spooner USD Aadil Chen USD Miyah Lambert USD Clement Howarth USD Syeda Sheppard USD Hisham Graves USD Magnus Dean USD Tolga Spooner USD Syeda Sheppard USD Reiss Finley USD Danni Cameron USD Meadow Rivas USD Tolga Spooner USD Saul Talbot USD Hisham Graves USD Anais Morton USD Saul Talbot USD Tolga Spooner USD Hendrix Strickland USD Saul Talbot USD Natan Coulson USD Sara Sandoval USD Magnus Dean USD Saul Talbot USD Finlay Bourne Cn larameliuinartan laram Address ID AD20-1 AD20-2 AD20-3 AD20-4 AD20-5 AD20-6 AD20-7 AD20-8 AD20-9 AD20-10 AD20-11 AD20-12 AD20-13 AD20-14 AD20-15 AD20-16 AD20-17 AD20-18 AD20-19 AD20-20 AD20-21 AD20-22 AD20-23 AD20-24 AD20-25 AD20-26 AD20-27 AD20-28 AD20-29 AD20-30 AD20-31 AD20-32 0 0 0 0 0 0 PAYMMODE 1 NULL 1 Check 0 Check 1 Credit Card O Check 1 Check 1 Check 0 Check 1 Check 1 Check 0 Check 1 Check 1 Check O NULL 1 Check 0 Check 0 Check 1 Check 1 Credit Card 1 Check 1 Check 0 Check 0 Check 1 Check 1 NULL 1 Check O Check 1 Check 1 Check 1 Check 1 Check O Check n chart 0 22 23 24 25 26 27 28 29 30 31 32 0 o 0 0 0 0 0 0 0 0 0 33 21 Maan LA Customer Address + Ready PH + 100% w Midterm Q Search in Document Home Insert Design Layout References Mailings Review View + Share A Calibri (Body) A *** AU T AaBbCcDdEe 11 -A- A A abe X2 X? A AaBbCcDdEe . AaBbCcDc AaBbCcDdEe AaBb AaBb CcDd Ee AaBbCcDdEE Paste Normal No Spacing Heading 1 Heading 2 Title Subtitle Subtle Emph... Styles Pane Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates Excel Notes: You are a knowledge worker at a national company that manufactures and sells lawn machinery (lawn mowers, leaf blowers, etc.). Business is going well but profits were down for 2019 and the CEO is concerned. The CEO has a hunch and wants to see profit by region, she has tasked you with putting that information together in a dashboard format so she can take it to the senior leaders. From working with her in the past you know that she is not a 'numbers' person and wants to see the information in a chart or graph to make trends more apparent. As a knowledge worker you understand data manipulation and you have business experience to know what data is appropriate. Your gut instinct tells you that profit by region is just the starting point. Make sure to give the CEO what she wants but also find out the real reason for the dip in profits. You have extracted a few tables from your ERP system: Customer Master, Product Master, Sales, and Customer Address. You think that everything you need is contained in these tables but you can always go back to the CEO if you have questions about the request; your challenge is to bring the right information together to find the reason behind the 2019 poor performance. As you work through this problem remember the steps of business intelligence: 1. Business understanding: Understand the question 2. Data understanding: Determine what data you are looking for, where it is, and how to get it 3. Data preparation: Look for data inconsistencies that need scrubbed 4. Data modeling: Identify trends and patterns 5. Evaluation: Analyze to assess potential for solving problem 6. Deployment: Deploy the discoveries into everyday business You will need to use the following formulas: 1. Nested IF 2. Sumit 3. Year 4. Left/right 5. Vlaskur All work should be done in Excel, but the final product should be in Power Point. You should submit your working Excel file and your final Power Point file. You do not need a large number of slides, but you need to display what the CEO is asking for the reason behind the decrease in profits for 2019, and recommendations to remedy the situation. Company Notes: 1. All your products have a different profit margin a. Lawnmowers: 20% b. Tractors: 40% c. Leaf blowers: 25% d. Snow blowers: 50% Weed wackers: 25% 2. Category in the Product Master table) relates to source a. 1.Core business b. 2 Acquisition c. 3 Outsourced 3. Country Code (in the Product Master table) relates to manufacturing location a. 0.US b. 1 Mexico e. Page 2 of 2 445 Words LE English (US) Focus + 94%