CRITERION A (Inquiring and Analysing)

ICT Assignment: Criterion A (Inquiring and Analyzing)

Stock inventory problem small retailers face in developing countries

Stock control is one of the most time consuming tasks a business owner faces in operating a retail store. Retailers usually purchase goods in wholesale quantities from manufacturers for sale to consumers.  Retail markets in developing countries are highly unorganized.  Due to high costs of services, they are not able to keep a proper inventory system in control.  Keeping track of many items with different packing, product codes, etc. can be time consuming.  Without an inventory system it is impossible to know of thefts by customers or employees.   If a retailer runs short of stocks, he loses the opportunity to make more profits on the sales. While, if he has excess stocks, his working capital is blocked.  This results in loss of investments in fast moving items as well as storage space is not utilized efficiently. Thus, he should neither have excess stocks nor shortage to make his operations very profitable. My system will help him achieve higher profitability. It will also at a glance give the prices, costs and margins on each product, name of manufacturers, and total value of stocks.   Tracking of specific items which are not fetching good sales can help in planning promotions and reducing the purchase.

Design Brief

The interactive system in Microsoft Excel will allow the user to operate it easily. Excel will include:

·      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.

·      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.

·      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.

·      Slicer: Similar to filters. Only used for pivot charts and pivot tables.

·      Instar charts: Charts that are embedded in tables. Can be in different forms (stars, signals, funnels, etc.). Gives an appealing look to the client.

·      Main Menu: Starting sheet of excel. Will display buttons which are hyperlinked (linked to another sheet). This will make it easier for the client to navigate.

·      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.

I have also noted some functions and formulas which could be used in Excel according to this subject:

·      SUBSTITUTE

·      IF statement

·      ROUNDUP

·      MOD

·      COUNTIF

·      SUMPRODUCT

·      VLOOKUP

·      Goal Seek

Research plan (Initial Stage):

Subject of research	Primary/ Secondary Research	Reason	Submission date
Functions and Formulas	Research a variety of functions, formulas and how to use it using secondary source (Internet) or from primary sources such as books, software engineers, etc.	For my project, it is necessary to use formulas in order to automate calculations based on values in cells of spreadsheets and to do display the result.	25th September
Retail Stores	Collected information from a departmental store (stock control system).	After obtaining the information, I will have a clear overview of the store’s performance and will simplify it to my terms.	27th September
Spreadsheet	Start organizing my spreadsheet and looking for various, suitable templates online.	Once I find the perfect template, it will be easier for me to input and organize my data in the spreadsheet.	28th September

  

Progression stage:

Sheet	Plan	Submission
1st: Main Menu	·      Assign a macro ·      Create buttons as well as hyperlinks ·      Headers must be frozen (freeze panes).	30th September
2nd: Stock Control System	·      Create 8 headings (product, product code, manufacturing date, stock quantity, expiry date, min stock level, estimated daily sales, no. of days till expiry). ·      A 3D graph about stock quantity with data labels. ·      Use formula DAYS in last column	1st October
3rd: Rating	·      Create 4 headings (product, rating, 5star chart,  cooked, flavored, classic or original) ·      Create an instar chart with formulas: if statement, ROUNDUP and MOD. ·      Use SUBSTITUTE function for column 4. ·      Use COUNTIF to find out the no. of items that are cooked, flavored, classic or original especially the ones that are excluded. ·      Create a 3D pie chart which talks about the rating of each product.	4th October
4th: Marketing Information	·      Create 14 headings (product, product code, Cost (AED), Selling price (AED), Min order quantity, reorder level, opening stock, receipts, Day Sales, Closing stock, Receipts 2, Day Sales 2, Closing stock 2, Stock cover (Days). ·      Use VLOOKUP to find out the Day Sales for each product. ·      Use SUMPRODUCT to find out the total value at cost and at selling price. ·      Use Goal Seek to find out at what Markup, can he make a daily profit of 12,000 and 15,000.	7th October
5th: Stock Control System (Pivot)	·      Create a pivot table with only 2 columns (Product and No. of Days till Expiry) and a 3D Pivot Chart. ·      Include a Slicer so that you are able to select and view a certain product and its information.	9th October

SWOT (Strength, Weaknesses, Opportunities and Threats) Analysis:

Strengths:

·      Easily access each product’s information through the use of a slicer

·      Even though a change is made in the data, the formulas remain fixed and will display the result.

·      Interactive 3D charts are used to display the data in an appealing way.

·      The main menu consists of macros, hyperlinks, etc. when clicked, will move to a specific sheet.

·      Data Labels can help the user know more about the data.

·      The file can be protected by creating a password through VBA or directly in the Excel file.

Weaknesses:

·      As a result of the data file being too large, the program will run very slowly.

·      For large numbers, the program rounds off using imprecise calculations.

·      Amount of time taken to enter numerous amounts of data can take a very long time.

·      Another easy alternative is using VBA (Visual Basic for Applications) in Excel. However, most users do not know how to use VBA.

·      Used for one-time analysis. Becomes a problem as the data grows over time.

Opportunities:

·      By issuing security, your file is completely protected from competitors and other rivals.

·      Rapid rise in technology such as updates have improved the Excel program.

·      Organizes data easily and can, therefore, lead to the formulation of methods and ideas to boost sales.

Threats:

·      Virus can enter the file through macros (mini programs written in Excel).

·      If password is shared, then the file can be accessed by various rivals and can use that information to get rid of the company.

How to improve as a company?

·   Input a password or lock the file using VBA coding and only make it accessible by your company.

·     Work in partnership with programming companies such as Microsoft to improve the software and make it invincible.

·   Create an open online framework where managers will be able to communicate business strategies and ideas to their employees.

Sources:

"Advanced Excel Tutorial". www.tutorialspoint.com. N.p., 2016. Web. 2 Oct. 2016.

"SWOT Analysis - Strengths, Weaknesses, Opportunities And Threats". Shell-livewire.org. N.p., 2016. Web. 2 Oct. 2016.

Soffar, Heba et al. "Microsoft Excel And Its Advantages And Disadvantages | Science Online". Science online. N.p., 2015. Web. 2 Oct. 2016.

"Myp-Tech - Design Brief". Myp-tech.wikispaces.com. N.p., 2016. Web. 2 Oct. 2016.

"Help Zone". Theictclub.com. N.p., 2016. Web. 2 Oct. 2016.