Gifts2Give House is a popular IT shop. Its popularity is due to two things: the good-for-reliable products, and the delivery service. The shop offers IT products. Since its establishment in May 2010, documentation of supply orders, customer orders, price calculation, and promotional activities are all done manually on paper. The owner of the gift shop is looking for a consultant who can automate the suppliers particulars and other transaction by using a spreadsheet application. Also, ensure that the generation of the report would be a detailed explanation of how the spreadsheet had been designed, as there can be further work done on the spreadsheet. Proper documentation of the work done in this section must be done in a professional manner. Assume that you have been assigned to work for this shop in the above mentioned capacity. Use only Ms Excel and Ms Word. You are to consider making the spreadsheet as user friendly as possible with figures that re-compute automatically for each month of the incoming years to come. Ultimately, make the spreadsheets future maintenance effortless.

Suppliers

Item Supplier Name Price

LAPTOPS ASUS K5000

DESKTOPS ASUS K4,000

PRINTERS ASUS K6,000

PROJECTORS I-TECH K1000

TONNER I-TECH K500

CABLES I-TECH K100

POWER PACKS I-TECH K250

EXTERNAL HHD HP K500

MONITORS HP K1200

KEYBOARDS HP K150

MOUSE HP K80

16 GB FLUSH DRIVES HP K100

Table 1: Supplies

The application should calculate daily and monthly total sales.

Assuming the shop owner wants to earn a 25% profit on each time supplied by ASUS, 35% profit on each item supplied by I-TECH, and 40% profit on each item supplied by HP, use a formulae to determine

1. The selling price for each item.

2. Monthly sales for each item (assume its ONE (1) month).

3. Total profits generated.

Furthermore, calculate the discount based on the following criteria: I. Price is greater than or equal to K10,000 3% discount,

II. Price is greater than or equal to K15,000 4% discount, III. Price is greater than or equal to K20,000 5% discount.

The shop also gives free flush drives if someone buys items worth K20,000 or more. Calculate the number of free drives to be given out: (Hint: Use INT() function to set the decimal value to a whole number value)

One (1) flush drive for every K5,000 beyond the first amount K20,000. Two (2) flush drives for every K7,000 after the first amount of K25,000.

Column titles should be in UPPER CASE, bold and italicized.

Format each column according to the right data type and do data validation where ever required.

Include a small logo of the gift shop designed on your own using Ms Word software on the top row of every worksheet created.

Include the current date and time below the logo for every worksheet created using a specific function.

a. Draw appropriate spending charts for each of the suppliers worksheets (ASUS, I-TECH, HP) based on the items.

b. Draw a comparison chart illustrating the amount of money spent to each supplier. Make the spreadsheet presentable.

Create a spending analysis for the current month using the template. Save it as Spending

Analysis.

(Hint: For effortless future maintenance, use logical if function and absolute reference where applicable).

PART B

Create a five (5) paged (max) report which includes;

a) An automated table of content followed by table of figures b) Two pages of the report with few screen shots

c) Last page with references in Harvard referencing style.

In your documentation, label out different features of Ms Word and Excel used. The advanced features may include the features that are not being taught to you in class, as well as your own findings from external resources. You may use you own creativity to arrange the text. However, you must ensure the ease of reading and visibility.

This question is mainly to show excel formulas and may have missing figure for illustration.