excel assignment help

Management of Information Tech

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

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%

e. 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

Excel Notes:

You will need to use the following formulas:

1. Nested IF

2. Sumif

3. Year

4. Left/right

5. Vlookup

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.