ICT
ASSIGNMENT: CRITERION B (DEVELOPING IDEAS)
DESIGN
SPECIFICATION:
My product will have certain features which will allow the
user to easily view and manage the stock inventory. Here are the following:
Features
|
Justification
|
Interactive 3D charts
|
They
are used to find out optimum combinations between different sets of data.
They will display a variety of data such as rating of the product, number of
days till expiry date, etc. You can select any chart type like pie, column,
radar, line, scatter, bubble, etc. E.g.
In sheets “Stock Control System”, “Rating” and “Stock Control System
(Pivot)”, there is a 3D line chart, 3D pie chart and a 3D Bar chart
respectively. Select the entire table/ data, go to Insert tab and select
either “Recommended charts” or a type of chart (bar, column, radar, etc.) at
the right of it.
|
Data Labels
|
Static point in the chart.
Marks the information in each point of the chart. Without a data label, the
user will not know the significance of the data presented. E.g. 3D line chart in “Stock Control
System” sheet has data labels. Each data label shows a point in the graph and
expresses the data. Right-click the current data label and select “Change
Data Label Shapes” to choose which design you want.
|
Filter
|
Used to locate specific
rows in a spreadsheet, while concealing the other rows. For each heading,
there will be a dropdown menu, from which you can select a certain data. E.g. In the SCS sheet, the table is
filtered. This means that you can select a specific product and its
information by filtering product code, product, etc. You can do this by
selecting all the headers of the table and click on “Filter” in the Data tab.
|
Slicer
|
Similar
to filters. Only used for pivot charts and pivot tables. E.g. In SCS and SCS (Pivot) sheet, two slicers are used. You can
select data from the product code and stock quantity respectively. You can
setup slicer by selecting the table, click on “Table Tools Design” Ribbon
tab. Then, select “Insert Slicer” and choose which column you want to find a
certain product.
|
Instar charts
|
Charts
that are embedded in tables. Can be in different forms (stars, signals,
funnels, etc.). Gives an appealing look to the client. E.g. In the rating sheet, an instar chart was used to display the
rating of each product. First, we setup 5 blank columns for the star chart. Then, add the if
statement, Roundup and mod functions in a single formula: (IF(D$2<=$C4,1,IF(ROUNDUP($C4,0)=D$2,MOD($C4,1),0)).
This formula signifies that if rating (C4) of the product is less than number
in D2 else, if the rating which is rounded up (ROUNDUP) is equal to the
number in D2, the decimal part of the rating else, 0. After this, use
conditional formatting to the 5 column grid.
|
Main Menu (Macros, Hyperlinks)
|
Starting sheet of excel.
Will display buttons which are hyperlinked (linked to another sheet). This
will make it easier for the client to navigate. E.g. In the main menu sheet, There will be a header and 4 buttons.
A Macro will be assigned to each button. Thus, when a specific button is
clicked, it will go to a certain sheet.
|
Pivot tables and charts
|
Pivot
tables allow the user to summarize the data present in the table in the form
of a report. Pivot charts are used to present data from the pivot table. E.g. In the SCS (Pivot) sheet, a
pivot table with headings “Product” and “Sum of No. of days till expiry” and
a pivot chart expressing the data. For a pivot chart, first select the normal
data/ table then, click recommended pivot tables or pivot table in the
“Insert” tab and select the required one. For pivot chart, just select the
pivot table/ data and click on “Pivot Chart” in the “Insert” tab.
|
FUNCTIONS:
- SUBSTITUTE:
Replaces a set of characters with another set. Formula: SUBSTITUTE (text,
old_text, new_text). E.g. Suppose
you want to replace Classic with Cl. Input formula: SUBSTITUTE (CLASSIC, CLASSIC, Cl).
- IF Statement,
ROUNDUP and MOD: Conditional statement that states that if it is true,
then it will display data or perform a function. Formula: IF (logical_test,
Value_if_true, Value_if_false). E.g. In
the rating sheet, making an instar chart. Suppose you want to display the
rating data in the form of a star chart. Input Formula: (IF(D$2<=$C4,1,IF(ROUNDUP($C4,0)=D$2,MOD($C4,1),0)).
This formula signifies that if rating (C4) of the product is less than number
in D2 else, if the rating which is rounded up (ROUNDUP) is equal to the number
in D2, the decimal part of the rating else, 0. After this, use conditional
formatting to the 5 column grid.
- COUNTIF: Used to
count cells within a range to meet a certain criteria or condition. Formula:
COUNTIF (Range, Criteria). E.g. Suppose
you want to find out how many items are cooked in the “Rating” sheet. Input
formula: COUNTIF (I4:I16,"C").
- SUMPRODUCT:
Used to multiply corresponding data in the given ranges and returns the
sum of the products. Formula: SUMPRODUCT (array 1, array 2, array3, …..). E.g. Suppose you want to find out the
total value of the opening stock at cost price in the “Marketing Information”
sheet. Input formula: SUMPRODUCT ($E5:$E17, $I5:$I17). $E5:$E17 is the range
for Cost (AED) and $I5:$I17 is the range for the Opening Stock.
- VLOOKUP: Conducts a
vertical lookup by looking for a value in the first column of the table and
returning the data in the same row in the index_number position. Formula: VLOOKUP (Lookup_value,
Table_array, Col_index_num, Range_lookup). E.g.
Suppose you want to find the day sales for LAY'S BARBECUE FLAVOURED POTATO
CHIPS. Input formula: VLOOKUP (C5, Table1, 7). C5 = the product, Table1 = Table
from SCS sheet, 7 = Column number.
- Goal Seek: Used to
calculate the output by performing what-if analysis on a set of inputs. Go to
the Data tab and click on “What-if Analysis”, a dropdown menu will appear.
Click on “Goal Seek”. E.g. Suppose
you want to find out at what markup, can he make a daily profit of AED 12,000.
Input: When Goal Seek wizard comes, Set cell to K20 which is profit made from
the “Day Sales” column. Then, change the value to 12000 by changing cell: D22
(which is the Markup cost). Then, it will give you the markup cost from which
the person earned a daily profit of 12000
DESIGN
SKETCHES FOR THE OUTCOME:
Design pair
(1 & 2) for the Main Menu:
Design 1:
Design 2:
Preferred
design for the Main Menu:
I think that the 2nd design would be more
appropriate than the 1st design due to the following:
- Buttons are organized in a funnel pattern
- There are icons next to the buttons which are
hyperlinks. In case the macros, embedded in the buttons do not work then the
hyperlinks will.
- A logo to certify the name of the company.
- An exit button which will close the excel file.
- The buttons come in a variety of colors, word art,
etc. and there is a background image, making it look more appealing to the
client/user.
Design pair
(3 & 4) for the Stock Control System sheet:
Design 3:
Design 4:
Preferred
design for the Stock Control System sheet:
I think that I would suggest the 3rd design rather
than the 4th design due to the following reasons:
- Filters to locate a specific product while concealing
other products.
- A blue colored header signifying that the application
is trustworthy and dependable.
- A slicer whose function is similar to that of a
filter. You can select data here to display information about the product. Also
linked to the table.
- A 3D line chart which has data labels.
- The table is also colored with yellow and white.
- There is also a wide range of data in the table.
Design pair
(5 & 6) for rating sheet:
Design 5:
Design 6:
Preferred
design for the rating sheet:
According to me, I believe that design 6 is better than
design 5 as a result of the following:
- Has an instar chart. It is made by using the IF
statement, ROUNDUP and MOD formulas as well as the numbers above the heading “5
STAR CHART”.
- Also has a blue colored header “RATING”.
- Added extra columns/rows/tables where the COUNTIF
formula can be used.
- Added an extra column with heading “Cooked, Flavored,
Classic or Original”.
- A 3D pie chart with rectangle shaped data labels as
well as a legend/key.
Design pair
(7 & 8) for Marketing Information sheet:
Design 7:
Design 8:
Preferred
design for the Marketing Information sheet:
I think that design 7 would be much more suitable than design
8 due to the following reasons:
- ·
Has more rows and headings which means that more
calculations are being done and therefore, there is a wide range of data.
- ·
Variety of functions and formulas are used such as
VLOOKUP, DAYS, SUMPRODUCT and Goal Seek.
- ·
Has a blue colored header.
Improvements:
- ·
I would like to
add filters to design 7 so it will be easier for the user to find the
information for a specific product.
- ·
Compare the data between the two months. This will be
more accurate and will provide a greater understanding to the retailer.
Design pair
(9 & 10) for the Stock Control System (Pivot Sheet):
Design 9:
Design 10:
Preferred
design for the Stock Control Statement (Pivot) sheet:
I think the 9th design is more superior to the 10th
design. This is because it has:
- ·
Slicer and filter to organize data and to find a
specific product’s information.
- ·
Dark green colored header to grasp attention of the
user.
- ·
Wide range of data for the client to analyze.
- ·
3D Pivot charts which come in a variety of designs and
colors.
Disadvantages:
- ·
No formulas/ calculations are used in this sheet.
Gantt chart:
Part 1
Part 2
