1.0 Introduction
Flipkart is an Indian online retailer with headquarters in Bangalore, Karnataka. It was created by Sachin and Binny Bansal and is India's biggest e-commerce start-up. The company offers a wide range of products, including electronics such as laptops, tablets, smartphones, and mobile accessories, as well as in-vogue fashion staples such as shoes, clothing, and lifestyle accessories; modern furniture such as sofa sets, dining tables, and wardrobes, as well as appliances that make life easier such as washing machines, TVs, air conditioners, mixer grinder juicers, and other time-saving kitchen and small appliances; and home furnishings such as cushion covers, mattresses, and bedsheets, as well as to toys and musical instruments.
The dataset chosen is the Flipkart Mobile Dataset. This dataset comes from Kaggle.com. The dataset contains descriptions of top 5 most popular mobile brands in India. There are 16 columns each having a title which is self-explanatory. Including information about brand, model, colour, processor, screen size, ROM, RAM, display size, number rear camera, number front camera, battery capacity, ratings, number of ratings, sales price, discount percent and sales in crore rupees. There are 430 rows each having a mobile with at least a distinct feature. The dataset did not provide a direct sales record that shows how many units of a certain mobile model were sold. To solve this, the new column “Unit Sold” was created with the formula [=[@[sales (crore rupees)]]*10000000/[@[sales_price]]]. All the data related to the currency in this report and dataset is the official currency of India (Indian rupee / ₹ / INR).
The problem statement is that a new seller is difficult to enter the Flipkart mobile phones market because it already has a lot of mobile phone brand sellers, which has become a huge competition. The objective of this report is to compare the products, brands and specifications of different mobile brands. The second objective is to determine the purchase point of consumers. The third objective is to predict phone sales price with specification with multiple regression models.
2.0 Methodology and Results
2.1 Logical function
Logical function is used in spreadsheets to design two different queries which are the “Rating Review” and “Price range”. Furthermore, logical functions also help us on counting frequency and sum the cell with criteria we wanted. We will look at the IF function.
Firstly, for a better review for ratings, the column “Rating review” was created. We assume that rating greater or equal to 4.6 is considered Excellent, greater or equal to 4.4 considered Good, greater or equal to 4.2 considered Not Bad and those lower than 4.2 is considered Normal. The formula is [=IF([@ratings]>=4.6,"Excellent",IF([@ratings]>=4.4,"Good",IF([@ratings]>=4.2,"Not Bad","Normal"))) ] (Figure 1).
Moreover, The column 'Price range' is created to assume the price greater or equal to 60000 considered luxury, greater or equal to 40000 considered medium, greater or equal to 20000 considered affordable, lower than 20000 considered cheap. The formula is [=IF([@[sales_price]]>=60000,"Luxury",IF([@[sales_price]]>=40000,"Medium",IF([@[sale s_price]]>=20000,"Affordable","Cheap")))] (Figure 2).
Figure 1 & 2: The Rating review and Price range
2.1.1 Result of the Logical Function
By using the if function, we can know that 236 out of 430 ratings are not bad, which is between 4.2 to 4.4, and 134 of them are good (4.4 - 4.6). Total 56, Apple gained 35 excellent and 21 good, while most of the phones of other brands are rated at not bad (below 4.2). Moreover, we know that most of the phones in India are cheap (below 20000). Realme and Samsung contribute the most cheap phone, while almost all of the 56 Apple phones are luxury (28) and medium (23).
2.2 Retrieval Application Table (INDEX and MATCH function)
Data retrieval is the process of identifying and extracting data from a database. There are three types of retrieval applications which are combination VLOOKUP and IF functions, VLOOKUP and MATCH function and INDEX and MATCH function. We will use the combination INDEX and MATCH functions as well as Define name, data validation, and IFERROR function. At the beginning, we created the new column “device name” to concentrate the model name & colour & ROM with formula [=A2&" "&B2&" "&C2&" "&G2&" "&"GB"] to better differentiate between different specifications of phones.
We define the name of column “Device name” to their brand name. Then we use data validation to create a list of brand names at cell X4. At cell Y4, we also create the list with data validation and fill in the formula [=INDIRECT($X$4)] at its source. After doing these steps, when we list out the brand at cell X4, the Y4 will also list out the device name of the particular brand name (Figure 3). Next we start to do INDEX and MATCH functions.
The INDEX function returns a value or the reference to a value from within a table or range. I will use it to return the value of position given by match function.
The MATCH function finds for a given item in a range of cells, and then returns the relative position of that item in the range. I used this to find the relative position of Y4 to column A1:A431 and match X6 - X14 & Z6 - Z14 to the row A1:D1 and used the INDEX function to return the value for their interaction cell. I also used the IFERROR function to avoid error when the Y4 is blank. The formula is: [=IFERROR(INDEX($A$1:$R$431,MATCH($Y$4,$D$1:$D$431,0),MATCH(X6,$A$1:$R
$1,0)),"")] (Figure 4 & 5)
● $A$1:$R$431 : The table array
● $Y$4 : The list cell of device names
● $D$1:$D$431: Look up array of column “Device names”
● X6 : Look up value of row
● $A$1:$R$1 : Look up array of row
● 0 : measure the value exactly equal to the lookup value.
In addition, I also create a retrieval applications of Count of distinct model with formula [=DCOUNTA($U$1:$V$121,V1,$X$3:$X$4)], $U$1:$V$121 is the database, V1 is the field, $X$3:$X$4 is the criteria. This function can tell us how many models for the particular brand.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
