Criteria B - Developing ideas

Research Question: How can a Business or an Organization modernize its records using Information Technology?

Design Specification:

My aim for this product is to create a spreadsheet on excel which will hold all necessary and important data for my client, and will also make it easier to make large number calculations. For my product I will be creating different designs to fit my client's needs to help make the best design most suitable for their company. I will create more than one design to make variety of options for the client to choose from. I will make sure that when creating my designs I will specifically mention any required data analyzing skills that are required or any formulas that can be used to calculate same amounts more easily. Some formulas that might be used for the designs might be: Addition and subtraction to calculate salary, profits, loss. Another one could be count if, this will help the company find the exact data they are looking for without having to go through heap amounts of data. There are many other formulas that can be used on excel such as: percentages, sums and counts, dates and times, text manipularion, conditional formatting, lookup and more, although I will not be using all of them. I will carefully decide on which formulas to use depending on the clients need and the type of data that is required to be inputed on the spreadsheet. 

The database I am creating for my client will have two tables to store employee and product data and they will be linked using a one to many relationship. It will have a form to help with add, delete and edit the records, making the product user friendly and convenient for the client. The spreadsheet will consist of various different features like combo boxes, option boxes and buttons to make it easy for the user (user friendly). I will go through all the data required and try to minimize the amount to make it easier and more usable for the company. (Only data which is unnecessary will be removed, keeping in mind the needs of the client). The employees and managers will be able to access various different options through a menu driven interface that will have many buttons to select the options on the spreadsheet. This will give the user a easy to use interface so that all the features of the program can be used easily and efficiently. 

The product that will be created will include the following design specifications to provide a successful and efficient solution: 

Aesthetics:	Every spreadsheet will have a simple layout making it user friendly. Each important detail will be in bold letters to emphasize points. All sections will be allocated different colors to make the spreadsheet aesthetically pleasing to the user, and will also help segregate different topics. The font will be chosen suitable for the context to make it easier to read the content.
Cost:	The only cost that will be necessary in this product will be the cost of the Microsoft excel document, everything else would be self made.
Customer:	This product is for an electronic company, who sell electronics for all ages. From children to adults.
Function:	The function of creating a spreadsheet is to create one spreadsheet that will obtain multiple areas of information, such as: name of employee, financial pricing, electronics sold, market value of these electronics, etc.
Manufacturing:	The resources that are there are the functions that are available on excel. Some limitations are there in creating this product as it will be difficult to manage to create a very effective and efficient product in the time span provided. I think to create the design more thought has to be put in, as well as more designs to create many options to choose from. Also, to create the best product for the client, I could let the client know about different designs and what their preference is, to suit their use in the company.
Security:	This spreadsheet which holds all the accounts can be easily secured by putting on a security password on the sheet while saving, this will help the company keep confidential information safe on their devices.

Plan of the client's table 1 (Employee Data): 

Staff ID	Name	Age	Gender	Nationality	Years Worked	Position	Qualification	Details	Salary
1277	Ben	28	Male	British	4yrs	Marketing	Masters	472113	20,000
3460	Dave	32	Male	British	1yr	Sales	MBA	234823	12,000
7224	Sam	34	Male	American	10yrs	GM	Software Engineer	109277	100,000
4141	Tina	40	Female	Indian	3yrs	HR	MA degree	887987	33,000

This table has all the data I will be portraying on my designs for showing employee data records. This will make it easier for the company to keep track and record of each employee working in the company, they will exactly know which employee has how much salary, what their position is, what is their qualification in their specialized jobs, etc. This will help the company maintain and keep same data for different employees on the same tab, this will allow the company to compare how different employees are receiving different salaries depending on their job/role in the company.

Plan of the client's table 2 (Product Details):

Stock availability	Supplier	Warranty of product	Product demand  (People in Millions)	Types of products
Phones - 200,000,000	Apple and Samsung	15 days	10M	Electronics
Laptops - 100,000,000	Apple, Sony, HP and Lenovo	2 - 3 years	8M	Electronics
TV - 500,000,000	Samsung, Sony, LG and Panasonic	1 month	7M	Electronics
Camera - 50,000,000	Nikon, Fujifilm and Canon	1 month	1M	Electronics
Printers - 70,000,000	HP, Epson, Canon and Samsung	1 month	3M	Electronics
Laptop and phone cover - 10,000,000	Apple and other distributers	No warranty	8M	Accessories

This table shows the data of the product of the company, this will be useful for the client to quickly look at the stocks they have left, or different suppliers they are working with, and demand and supply of these products. In the table above I have shorten down the data to have a clear view on few of the electronics.

Design 1 and 2 for employee data spreadsheet:

The first design I have made looking at the company and client's requirements, I have decided to make a spreadsheet for Employee data. This will help the company keep track of what each employees position is, or salary, or qualification, and since all of the employee names will be together it will be much easier for the company to compare employees when deciding to change, position, salary, etc. On the top left-hand corner you can see a logo has been sketched, this will help authenticate the sheet. In the middle there will be the title in a beautiful, eye-catching font.

On the bottom of the tab there will be a cell with a formula that will help the user calculate annual salary. 

In the second design for employee data the categories are still the same (Name, position, salary, etc). In the second one I have made significant changes to make this product more user friendly, to be easily accessible by clients/the company. For starters the font of the heading which is in the middle, is a more appropriate font, which is easily readable. To make the title stand out more, I decided to underline it making it stand out from the whole spreadsheet. I also decided to make the sheet more aesthetically pleasing by adding colors where necessary. I decided to give each different category a different color, and there will be a color legend on the bottom of the page. I will do some more research and try to find out whether colors can be used in formulas, this will make it much easier for the client or the company to calculate data effectively. I have also shown in the sketch above the different formulas I will be using to make my product more efficient and user friendly for my client. 

For calculating the salary, I have given the formula: = cell + other cell. On the left-hand side you will see the company logo, I did not make any changes to this. 

Evaluation:

Design 2 is preferred as design 1 lacks sophistication in controls, formulas, and user accessibility. Design 2 includes several options to the user and also has an option to look at categories with ease, because of the color coordinated scheme. This design looks well-structured and aesthetically pleasing, hence this would be preferred. In the design specification I have mentioned the requirements of my client, and they would like the product to be easily accessible and data to be clear and easy to find. Design 2 serves this purpose, as the title is in a professional font along with a dark underline to give further importance. The whole spreadsheet is based on the requirements, as the colors make it much easier for the user to quickly find what they are looking for. 

Design 1 and 2 for product and stocks data spreadsheet:

For product details I found that there was much more thought required, and since there were many calculations that would take place, I created a small legend on the bottom with all the different formulas. Again I have kept the logo on the left side. The title "Products", is written in an eye-catching font which looks aesthetically pleasing. Since, there are so many cells that may be hard to identify it made sense to make each cell a different color (Color code). To understand how to best make a product design, I had to think like the user's perspective to understand the flaws and make sure that everything on the sheet made sense and was user friendly most importantly. This is the 2nd tab of the spreadsheet and will have a similar layout to the first one to make sure they match. The different formulas I decided to add to this product to make it more useful to my client are: Count if, this will help the client (or company) find a specific category of a product they are looking for. For example, since I am doing an electronic company, they would frequently check how much stock might be available for a certain product, and for them to skim through all the data would be difficult, as it would be time consuming and not as efficient. There would be addition and subtraction to calculate prices of products together, easily. 

For the second design I did exactly the same as the first one but the only change I had made was making the title bold and underlined giving it more importance. I also decided to keep the font professional and readable for the client. This is much more effective and is good for office uses as it serves the purpose well. 

Evaluation:

Design 2 is preferred as design 1 lacks sophistication in controls, formulas, user accessibility, and variety of formulas to help navigate the system efficiently. Design 2 includes several options to the user and also has an option to look at categories with ease, because of the color coordinated scheme. This design looks well-structured and aesthetically pleasing, hence this would be preferred. In the design specification I have mentioned the requirements of my client, and they would like the product to be easily accessible and data to be clear and easy to find. Design 2 serves this purpose, as the title is in a professional font along with a dark underline to give further importance. The whole spreadsheet is based on the requirements, as the colors make it much easier for the user to quickly find what they are looking for. There are many more formulas in the sheet, making the product more user friendly and less time consuming. 

Gantt Chart - The chart below shows the timeline that will be used as a guide to aid me with completing the tasks: (This gantt chart was too large to fit clearly on one shot, I will be emailing the document as well)

Test Plan:

Even after evaluating the different designs to pick out the best it is not good enough to give to the client yet. The product has to go through a test plan to make sure it will be suitable for the client, and will work effectively without any issues. Firstly, I will be testing the buttons on the menu page to make sure, any coding work that has been done is working or not. The options that have been given in this product are useful, although if they do not work when necessary the client will have many issues with the system. All the features provided to the client by the product will be tested to make sure it performs as planned for example, when data is added the fields will be checked to see if the correct data is entered and stored, this needs to be checked to avoid errors in calculations when client is actually beginning to use the product. I will make sure different features and options are working as required, for example the add and delete button. If there is an issue with this there will be a problem in calculating data, and the user will be unable to use to product to its potential use. I will make sure formulas and options given on the page work to ensure a good product. 

Next feature I will check is the integrity of data on the spreadsheet using the validation rules. The tests will be carried out using 3 Types of Data. These data types are: 

1. Normal Data - This data is the correct data on the sheet, and when entered the system should recognize and accept it. It is also important to look at the display of the output, to ensure all features are mentioned and clearly to the user, for easy access. To test this more effectively I will type in a variety of data to make sure the system can cope with different calculations that may be required by the client, to fit their needs. 
2. Abnormal Data - Unlike the normal data, abnormal data will have many errors/mistakes, so that the can be denied by the software. This will test how well the software will work when incorrect data has been inputed into the spreadsheet, this will ensure the user doesn't make any errors. The system needs to tell the user that there is some error, to make the user aware of the error before putting crucial data or details about the company. There should be some message or indication from the system to help the user achieve their expected target. I will be entering several invalid or data with mistakes, to ensure the system works to its full potential/requirement. 
3. Extreme Data - The extreme data will test the rules used within the software. This data is far more easier than normal data and it should be immediately accepted by the software. 

Below is a test plan table  which will be used when actually testing the product for errors: 

Number of Test	Feature Tested	Input Data	Expected Result	Successful?
1	(In this box I will write the name of the feature I am testing)	(What I have received after trying this feature)	(What was this feature meant to do for the client?)	(Did the feature work as you planned it to? Yes or no?)
2	-	-	-	-
3	-	-	-	-

Diagram: This diagram represents the layout of the program and how the menus will be linked.

Employee data diagram:

(Unfortunately the resolution of my diagram is unclear, below I will write down the points mentioned in the diagram above).

• Add employee ID number, name (First and Last), gender, and age.
• Add years worked to spreadsheet. 
• Add position and qualification of Employee.
• Add salary or annual salary of employee. 
• Delete employee data. 
• Save or send employee details.
• Print employee details.

Product details diagram:

(Unfortunately the resolution of my diagram is unclear, below I will write down the points mentioned in the diagram above).

• Add product name
• Add product category (color coordinate) 
• In stock availability of product.
• Demand and supply of product.
• Supplier and warranty of product.
• Delete product
• Save and Print product details. 

Utilities diagram:

(Unfortunately the resolution of my diagram is unclear, below I will write down the points mentioned in the diagram above).

• Password - To secure confidential files/data.
• Microsoft Excel
• Microsoft Word
• Graphics - For creating graphs to represent different data in the company. 
• Save data - This function will allow the user to save all the data they have entered to the product.
• Delete data - Any errors that the company would like to change can be made by deleting irrelevant data.
• Print data - To have a hard copy which can be given to multiple people. 

Flow Chart - This will show the order the instructions will come in:

This flowchart shows how the product will start out, creating this flowchart will help me look at any design flaws or any important data that needs to be added.

Reference:

• Exceljet. (2012). 300 Excel Formula Examples. Available: https://exceljet.net/formulas. Last accessed 20th Oct 2016.
• Jumbo Electronics Company. (2016). Jumbo Products. Available: http://www.jumbo.ae/. Last accessed 20th Oct 2016.