This company specialises in cosmetics and started in 1996. Therefore the company is still growing and expanding. It is a small company with only 2 stores located in Essex and the surrounding area. The managing director of both stores is Mrs. Bell.

Design Specification

Background Information

User requirements

Makeup Mania receives discounts for products that they order. The company does not have a wide range of products as they are still expanding. For each order, there are a number of units in each pack. Below are the numbers of units for each product ordered.

It depends on how many packs that are ordered to how much discount the company receives. The discount will start at 10% for 3 packs and go up to 50% for 10 packs.

The Sales Director Mr. Simpson wants a spreadsheet that will show the lists of stock, the current stock level, and whether or not each product needs ordering. The discount that the company receives needs to be recorded in terms of percentage and money. The company does not know which products receive the most discount, so there needs to be a way in which this is recorded also.

Therefore I am going to display this information for the user, under the following headings:

Product Name
Colour
QTY per pack
Packs in store
Packs sold
Current stock level
Action
To order
% Discount
Saved
Rank order

Output information

Previously the discount was checked manually, and the purpose of the spreadsheet is to provide the sales director with a way to check what discount can be received and which stock is receiving the most discount. The output the system will give is figures that will show discount trends.

Currently

Currently the ...

This is a preview of the whole essay

Therefore I am going to display this information for the user, under the following headings:

Product Name
Colour
QTY per pack
Packs in store
Packs sold
Current stock level
Action
To order
% Discount
Saved
Rank order

Output information

Currently

Currently the Sales Director, Mr. Simpson orders the goods that is needed, and then checks manually if the discount received from the supplier was correct. How much discount in total is not recorded, neither are other details regarding discount such as which items receive the most. The problem with this is that if the discount is not recorded the company does not know how much money they are saving in total and therefore they are not aware of all money generating in and out of the business. This could be why the company has not expanded much since 1996.

Data

The data that I am going to input is to come from administrative form from the company and their suppliers.

Data capture method

I am going to read the data and input it into the spreadsheet using the data form. This is easier to input the data and is done much more quickly.

Data processing the output

I am going to use several ways to present the output. Firstly I am going to use formulas to show how much stock there is in store by calculating from how much has been sold in the month.

I am going to use an IF function to determine whether the user requires to order stock for a particular product or not depending on the level of stock currently in store.

I am going to use look-up tables to determine what percentage the company will receive for different products. These averages will be ranked and averaged to enable the user to see which products are receiving the most discount.

Aids to help the user

In order to make the system easier for the user to use, I will use macros to help the user see how much is being saved for each product. This will make inputting data error-free. The use of colour to match the company image will be used to display the data.

I am going to use the following functions to do all this:

Look-up table – This is where the spreadsheet will determine what percentage discount MakeUp Mania will receive for each order. There will be a look-up table separate from the spreadsheet that gives order value and discount. I will use this to easily determine the correct discount.
Average – Once it has been totalled how much money the company has saved for the order, I am going to use this function to see the average amount of discount saved. This will show the user the average and enable them to monitor the average over a time period to see if the company is saving more or less money.
IF – I am going to use this function to decide for the user whether or not there needs to be more ordered for each product. Makeup Mania is not a large company and so I am going to use the criteria less than 15. So if there are less than 15 units of the product in stock then it will tell the user to ‘order more stock’. If the stock level is over 15 then the spreadsheet will say ‘Stock level ok’. This will help the user, as the spreadsheet will be able to decide for them if they need to order stock instead of checking manually. This way will save time and be more efficient.
Date – This function will display the correct date every time the spreadsheet is opened. It will enable the user to know what dates they are working with. I will situate the date in the top corner to keep it separate from the data
RANK – Once the amount of discount each product has saved has been recorded, I will use this function to display in number order that has saved the least, to the most. This function will display easily the product that has saved the most, and will save the user time and display it on the spreadsheet for efficiency.
Comment boxes – So that the user is aware of what everything on the spreadsheet does I have included comment boxes to explain what certain cells are.

Problem Definition

The main aim is to create a system that saves the company time instead of searching manually for discounts. The spreadsheet should be simple for the person to use and display data accurately with any complications clearly noted so that the user is aware of the spreadsheets capabilities.

Main objectives

To provide a simple user-friendly system available to MakeUp Mania, which allows the company to see details of the discounts they receive from suppliers.