Lab 1 S5 T Year Customer Number Of Orders Per Year Sales Per

Question

Lab 1 S5 Tyearcustomer Number Of Orders Per Year Sales Per Yearcoupo The Excel table shows years 2018 and 2019 data related to the number of orders and dollar sales per customer for a certain company. The manager asked you to perform the following analysis related to sending discount coupons: Develop a pseudo code that will offer the following discounts: $159, % coupon for those customers whose $ Sales per year were less than $200,000. $147, % coupon for those customers whose $ Sales per year were between $200,000 and $300,000. $203, % coupon for those customers whose $ Sales per year exceeded $300,000. Develop your if statements in column E so that E2 … E49 will show the coupon value based on the criteria above. Format the $ Sales per year column with a graded color scale (do not manually highlight). Edit your work so that random number generations stop in both the Number of Orders and $ Sales per year columns. Rearrange your spreadsheet so that listings are organized based on coupon value. Make sure you keep saving your file and submit to Blackboard before time is up. You may email your file as a last resort; however, it won't be considered submitted on time if modifications took place past the due date.

Dr. Jack HW Helper · Accepted Answer

The purpose of this study is to design a systematic approach via pseudo code and Excel functionalities to analyze customer sales data for implementing targeted discount coupons. The goal is to utilize customer purchase history, specifically annual sales data, to determine appropriate coupon values that incentivize continued patronage while maintaining profitability. Furthermore, the study aims to enhance data visualization through conditional formatting and organize data efficiently for strategic decision-making. Introduction In modern retail management, personalized discounts are essential tools for fostering customer loyalty and increasing sales. Companies often rely on detailed customer data to craft tailored incentives. This research outlines a step-by-step methodology involving pseudo code development, Excel formulas, conditional formatting, and data organization to execute targeted discounting strategies based on annual sales figures. Methodology Developing Pseudo Code for Discount Logic The initial step involves creating pseudo code to categorize customers based on their total sales per year. Pseudo code is a simplified, language-agnostic outline that facilitates translating logic into Excel formulas. The required conditions are: If sales are less than $200,000, assign a $159 coupon. If sales are between $200,000 and $300,000, assign a $147 coupon. If sales exceed $300,000, assign a $203 coupon. Example pseudo code: FOR each customer in dataset: IF sales coupon_value = 159 ELSE IF sales >= 200000 AND sales coupon_value = 147 ELSE coupon_value = 203 END FOR Implementing IF Statements in Excel The above logic can be translated into nested IF statements within Excel. Assuming the $ Sales per year are in column C starting from C2, the formula in column E (e.g., E2) would be: =IF(C2 Dragging this formula down from E2 to E49 applies the logic to all relevant rows. Conditional Formatting with Graded Color Scale To visually interpret sales performance, apply condi

Lab 1 S5 T Year Customer Number Of Orders Per Year Sales Per ✓ Solved

Lab 1 S5 Tyearcustomer Number Of Orders Per Year Sales Per Yearcoupo

Sample Paper For Above instruction

Introduction

Methodology

Developing Pseudo Code for Discount Logic

Implementing IF Statements in Excel

Conditional Formatting with Graded Color Scale

Filtering Random Number Generation

Rearranging Data by Coupon Value

Conclusion

References