EarlSparrowPerson615
Instructions Step 1 (5 points): On D2L, post the following on our…
Instructions
Step 1 (5 points): On D2L, post the following on our ‘Critical Thinking Assignment’ discussion board (D2L ? Communications ? Discussions ? Firm / Industry Selection) Click ‘Start a new thread’
? Under ‘Subject’: Your Name, Industry, Specialization, Fictitious Firm Name (example: Bob Smith, Retail, Athletic Sporting Goods Store, Smith’s Athletic Emporium) (You will be completing this assignment for a fictitious firm and not a firm that already exists)
? Please recall that the firm that has hired you has only asked you to analyze data for 5 products/services. Because you will be creating a ‘how to guide’ alongside your workbook, the firm that has contracted your services will use the ‘how to guide’ to train and task their staff with completing data analysis on the rest of their product catalog. Please view the Critical Thinking
? Within the contents box: Provide insight on why you selected the industry/specialization for your fictitious firm. List the 5 products and/or services that your workbook will analyze. Please select only 5 products/services (and not broad categories).
For example: if you type “shoes” ? there are many types of shoes. Depending on the type of shoes, your sales will vary
Step 2 (10 points): Complete section 2 of this assignment (company profile). Details on what must be included can be found within the assignment submission template found on D2L.
Step 3 (10 points): Data Entry
? Create one tab on your submission and title this tab “Raw Data”.
? Your data must be organized and include sales information – Sales data for each month for a one-year timespan (Jan – Dec). Include the ‘year’. – Your raw data sheet should breakdown the amount of sales for each of the 5 products and/or services that this firm offers. The quantities sold (and prices) of each product/service should be organized in a fashion that is easy for the owner to identify.
– Your raw data must also include a discount section. This will be useful when analyzing your data given that some products may be seasonal and thus require a discounted price clear off inventory. Learners may also incorporate discounts for bulk orders or promotions. – Include one more metric. Examples of additional metrics include but are not limited to: costs, profit…etc
Critical Thinking & Research:
? Within step 2 of your submission, you provided (researched) data on the products/services sold by your fictitious firm. The sales figures should make sense given the demand of each product for each month.
? It is common for firms to offer discounts throughout the year. This should be represented within your excel workbook. The timing of discounts should make sense (i.e. boxing week sales).
Time Period
Product 1
(Price per unit)
Product 2
(Price per unit)
Product 3
(Price per unit)
Product 4
(Price per unit)
Product 5
(Price per unit)
Discount
(%)
Revenue
($)
Additional Metric of your choice
Specify date range here
i.e. Jan 1 – 5 (M – F)
? A minimum of 104 rows are required for your data input. One way around this could be to separate the year in weekdays and weekends 52 weeks (Mon – Fri) represent 52 rows. 52 weekends (Sat & Sun) represent 52 rows 104 rows total. or If your firm has a few key clients, each row can represent the sales made by each key client throughout the year or You can organize your sales data in another way that you see fit (critical thinking) so long as at least 104 rows of data are includes
Additional Notes i. You are free to include more than 104 rows. The minimum for this assignment is 104 (additional points will not be awarded if you input more than 104 rows of data).
ii. There may be time periods where no discounts are offered.
iii. Ensure your data input contains absolute cell references (covered within Chapter 1 of this course). This will ensure that data stays the same throughout the analysis stage of this assignment
vi. Some learners may feel that the data entry component of this assignment is a time-consuming process. Please note that completing this step is critical in order to analyze the data inputted and complete this critical thinking assignment. For this reason, this section of your submission is worth 10 points.
? Format your table (select a theme/style (module 1)).
?
Step 4 (5 points): Sparklines
? Create a new worksheet.
? Within this worksheet, utilize sparklines (module 1). For your word document, you should be able to communicate what the sparklines illustrate.
? Create a relevant label for this worksheet tab and ensure that the color for this worksheet tab is different than the colors for any other worksheet tabs created.
? Complete section 4 of your word document.
Step 5 (5 points): Conditional Formatting & IF (or IFs) Function
? Create a new worksheet
? Within this worksheet, using the raw data for the firm that you created, incorporate conditional formatting (module 2).
? Within this worksheet, incorporate either the IF or IFs function.
? Critical Analysis and academic integrity component. Choose which way this information can be best utilized for this firm using these tools.
? Create a relevant label for this worksheet tab and ensure that the color for this worksheet tab is different than the colors for any other worksheet tabs created.
? Complete section 5 of your word document.
Step 6 (10 points): Pie Chart & Goal Seek
? Create a new worksheet
? Within this worksheet, using the raw data for the firm that you created, create a pie chart (module 3).
? Create another worksheet and incorporate the Goal Seek function.
? Two worksheets should be completed for this step. Create a relevant labels for these 2 worksheet tabs, and ensure that the color for this worksheet tab is different than the colors for any other worksheet tabs created.
? Complete section 6 of your word document.
Step 7 (5 points): Pivot Table
? Create and label a new worksheet (you may create more than one worksheet for this step if you like)
? Using the raw data for the firm you are working on, create a pivot table. Critical Thinking Task: You have a lot of flexibility here. How can a pivot table be useful for this firm?
? Create a relevant label for this worksheet tab(s) and ensure that the color for this worksheet tab is different than the colors for any other worksheet tabs created.
? Complete section 7 of your word document.
Step 8 (5 points): Process Smart Graphic or Funnel Chart
? Create and label a new worksheet
? Using the raw data for the firm you are working on, create either a Process Smart Graphic or Funnel Chart (module 6). Please only select one. If you include both, only the first tool included within this assignment will be graded.
? Create a relevant label for this worksheet tab and ensure that the color for this worksheet tab is different than the colors for any other worksheet tabs created.
? Complete section 8 of your word document.
Step 9 (5 points): Moving Average (module 8)
? Create and label a new worksheet
? Using the raw data for the firm that you are working for, utilize the moving average function.
? Create a relevant label for this worksheet tab and ensure that the color for this worksheet tab is different than the colors for any other worksheet tabs created.
? Complete section 8 of your word document.
Step 10 (10 points): Recommendations
? Complete section 10 of your Word document
