Data Analysis with Excel: Customer, Product, Sales, and Revenue Report

This assignment solution showcases the application of Excel for comprehensive data analysis, encompassing customer, product, sales, and revenue forecasting. Task 1 focuses on creating and manipulating worksheets, utilizing the LOOKUP function to identify specific data points across various tables. This involves entering, sorting, and hiding data, along with protecting sensitive information. The solution also demonstrates the use of sorting functions and creating complex formulas. Task 2 delves into revenue forecasting using the IF function, along with the implementation and assignment of macros for automated calculations and data entry, including the creation of an invoice model. The solution incorporates input data, calculations, and the application of the Ctrl+Q shortcut for macro execution, providing a practical demonstration of Excel's data analysis capabilities.

TASK 1
1 Customer, product and sales worksheet and LOOKUP function
Table 1Customer worksheet
Customer unique reference Name Address Contact phone number Email
A1 Muhammed MacIntyre Nunavut 98658
A2 Barry French Nunavut 92522
A3 Barry French Nunavut 96558
A4 Clay Rozendal Nunavut 96666
A5 Carlos Soltero Nunavut 94457
A5 Carlos Soltero Antarctica 93165
A7 Carl Jackson Antarctica 97854
A8 Carl Jackson Antarctica 96587
A9 Monica Federle Antarctica 93265
A10 Dorothy Badders Antarctica 94568
A11 Neola Schneider West 96632
A12 Neola Schneider West 96667
A13 Carlos Daly West 92234
A14 Carlos Daly West 98564
A15 Claudia Miner West 98831
A16 Neola Schneider West 97754
A17 Allen Rosenblatt West 93245
A18 Bryan Mills West 93246
A19 Eugene Barchas West 93247
A20 Sylvia Foulston West 93248

Table 2Input for LOOKUP table
Input for LOOKUP variable
Customer Name A9
Product Name
Supplier name
Customer Name
Product Name
Supplier name
Customer Name
Product Name
Supplier name
Customer Name
Product Name
Supplier name
Customer Name
Product Name
Supplier name
Table 3LOOKUP table
Calculation field
Customer Product Suplier
Monica Federle Office Supplies Adrian Hane
#N/A #N/A #N/A
#N/A #N/A #N/A

#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
Table 4Product and supplier worksheet
Product
code Category Product description
Unit
description
Supplier
reference
Name of
supplier
Sales
commission
B1
Office
Supplies Computer Peripherals Small Box C1 Ann Chong 102.8016
B2
Office
Supplies
Telephones and
Communication Small Box C2 Ann Chong 549.5424
B3
Office
Supplies
Telephones and
Communication Wrap Bag C3 Joy Bell 22.9416

B4 Technology
Binders and Binder
Accessories Small Box C4 Joy Bell 517.4136
B5
Office
Supplies Appliances Small Box C5 Skye Norling 57.4992
B6 Furniture Chairs & Chairmats Small Box C6 Barry Weirich 17.5296
B7
Office
Supplies Computer Peripherals Small Pack C7 Grant Carroll 19.272
B8
Office
Supplies Office Machines Medium Box C8 Grant Carroll 112.8864
B9
Office
Supplies
Binders and Binder
Accessories Small Box C9 Adrian Hane 364.6896
B10
Office
Supplies Pens & Art Supplies Wrap Bag C10 Skye Norling 13.1472
B11
Office
Supplies Chairs & Chairmats Jumbo Drum C11 Skye Norling 11.2992
B12
Office
Supplies Paper Wrap Bag C12 Andrew Gjertsen 10.428
B13
Office
Supplies
Binders and Binder
Accessories Small Box C13 Ralph Knight 57.4992
B14 Technology
Telephones and
Communication Small Pack C14 Dorothy Wardle 126.6672
B15
Office
Supplies Pens & Art Supplies Wrap Bag C15 Dorothy Wardle 13.9392
B16 Furniture Pens & Art Supplies Wrap Bag C16 Dorothy Wardle 105.3096
B17
Office
Supplies Pens & Art Supplies Wrap Bag C17 Grant Carroll 105.6
B18 Furniture Pens & Art Supplies Wrap Bag C18 Dorothy Wardle 17.5296
B19 Technology
Binders and Binder
Accessories Small Pack C19 Grant Carroll 126.6672
B20 Furniture Chairs & Chairmats Medium Box C20 Dorothy Wardle 17.5296
In order to identify specific thing LOOKUP function is used. Under lookup function value or variable that one want to identify
is placed in the lookup function. Then range in which value that lie is determined in the LOOKUP formula. Finally, relevant range is

selected out of which value for the relevant variable will be identified. in this way LOOKUP function is used to identify value of the
specific variable.
2 Enter data in worksheet
Table 5Customer, product and supplier data
Sources of input for LOOKUP
Customer unique
reference
Product
Category
Name of
supplier
A1 Office Supplies Ann Chong
A2 Office Supplies Ann Chong
A3 Office Supplies Joy Bell
A4 Technology Joy Bell
A5 Office Supplies Skye Norling
A5 Furniture Barry Weirich
A7 Office Supplies Grant Carroll
A8 Office Supplies Grant Carroll
A9 Office Supplies Adrian Hane
A10 Office Supplies Skye Norling
A11 Office Supplies Skye Norling
A12 Office Supplies
Andrew
Gjertsen
A13 Office Supplies Ralph Knight
A14 Technology
Dorothy
Wardle
A15 Office Supplies
Dorothy
Wardle
A16 Furniture Ralph Knight
A17 Office Supplies
Andrew
Gjertsen

3 Use name in referring cells
Figure 1 Use of name in LOOKUP
4 Split of product table
Table 6Sales commission table for office supply
Category Sales commission
Office
Supplies 102.8016
Office
Supplies 549.5424
Office
Supplies 22.9416
Office
Supplies 57.4992
Office
Supplies 19.272
Office
Supplies 112.8864
Office
Supplies 364.6896
Office
Supplies 13.1472
Office 11.2992

Supplies
Office
Supplies 10.428
Office
Supplies 57.4992
Office
Supplies 13.9392
Office
Supplies 105.6
Table 7Technology wise classification
Category
Sales
commission
Technology 517.4136
Technology 126.6672
Technology 126.6672
Table 8Funiture wise classification
Category
Sales
commission
Furniture 17.5296
Furniture 105.3096
Furniture 17.5296
Furniture 17.5296

Sort function us clicked in excel and in this regard button is clicked in the Home tab. By doing so sorting of relevant variables
is done in excel.
5 Enter data by filling data series
Already covered in answer 1and 4.
6 Hiding relevant variables
Covered in excel sheet.
7 Protect cost price per unit sales
Covered in excel sheet and protected sheet is protected by password “qwe”.
8 Sorting of sales worksheet
Table 9Sorting by Name
Supplier name Product
Total unit price including
VAT
Ann Chong Office Supplies 46.728
Ann Chong Office Supplies 249.792
Adrian Hane Office Supplies 165.768
Andrew Gjertsen Office Supplies 4.74
Barry Weirich Furniture 7.968
Dorothy Wardle Technology 57.576
Dorothy Wardle Office Supplies 6.336
Dorothy Wardle Furniture 47.868
Dorothy Wardle Furniture 7.968
Dorothy Wardle Furniture 7.968
Grant Carroll Office Supplies 8.76
Grant Carroll Office Supplies 51.312
Grant Carroll Office Supplies 48

Grant Carroll Technology 57.576
Joy Bell Office Supplies 10.428
Joy Bell Technology 235.188
Ralph Knight Office Supplies 26.136
Skye Norling Office Supplies 26.136
Skye Norling Office Supplies 5.976
Skye Norling Office Supplies 5.136
Table 10Category wise classification
Ann Chong Office Supplies 46.728
Ann Chong Office Supplies 249.792
Andrew Gjertsen Office Supplies 4.74
Dorothy Wardle Office Supplies 6.336
Grant Carroll Office Supplies 8.76
Grant Carroll Office Supplies 51.312
Grant Carroll Office Supplies 48
Joy Bell Office Supplies 10.428
Ralph Knight Office Supplies 26.136
Skye Norling Office Supplies 26.136
Skye Norling Office Supplies 5.976
Skye Norling Office Supplies 5.136
Barry Weirich Furniture 7.968
Dorothy Wardle Furniture 47.868
Adrian Hane Furniture 165.768
Dorothy Wardle Furniture 7.968
Dorothy Wardle Furniture 7.968
Dorothy Wardle Technology 57.576
Grant Carroll Technology 57.576
Joy Bell Technology 235.188

9 Print sorted sheet
10 Creation complex formula by using brackets
1 2 3 4 5 6 7 8
Ann Chong Ann Chong Adrian Hane
Andrew
Gjertsen
Barry
Weirich
Dorothy
Wardle
Dorothy
Wardle
Dorothy
Wardle
Office
Supplies
Office
Supplies
Office
Supplies Office Supplies Furniture Technology
Office
Supplies Furniture
46.728 249.792 165.768 4.74 7.968 57.576 6.336 47.868
9 10 11 12 13 14 15 16
Dorothy
Wardle
Dorothy
Wardle Grant Carroll Grant Carroll Grant Carroll
Grant
Carroll Joy Bell Joy Bell
Furniture Furniture
Office
Supplies
Office
Supplies
Office
Supplies Technology
Office
Supplies
Technolog
y
7.968 7.968 8.76 51.312 48 57.576 10.428 235.188
17 18 19 20
Ralph Knight Skye Norling Skye Norling
Skye
Norling
Office Supplies Office Supplies
Office
Supplies
Office
Supplies
26.136 26.136 5.976 5.136

TASK 2
1 Revenue forecast using IF function
Table 11Percentage change in Q4
Percentage change on basis of Q4
Percentage change for Q1 and Q4 Percentage change for Q2 and Q4 Percentage change for Q3 and Q4
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%
75% 40% 17%

Table 12Forcasting revenue and growth rate
Jan Feb
Marc
h
Apri
l
Ma
y
Jun
e
Forecast sales for six months based on analysis of previous 6 months
data
44
%
44
% 44% 44%
44
%
44
% 2353.274
44
%
44
% 44% 44%
44
%
44
% 12579.80267
44
%
44
% 44% 44%
44
%
44
% 525.1656667
44
%
44
% 44% 44%
44
%
44
% 11844.329
44
%
44
% 44% 44%
44
%
44
% 1316.238
44
%
44
% 44% 44%
44
%
44
% 401.2773333
44
%
44
% 44% 44%
44
%
44
% 441.1633333
44
%
44
% 44% 44%
44
%
44
% 2584.129333
44
%
44
% 44% 44%
44
%
44
% 8348.260667
44
%
44
% 44% 44%
44
%
44
% 300.958
44
%
44
% 44% 44%
44
%
44
% 258.6546667
44
%
44
% 44% 44%
44
%
44
% 238.7116667
44
%
44
% 44% 44%
44
%
44
% 1316.238
44
%
44
% 44% 44%
44
%
44
% 2899.591333
44 44 44% 44% 44 44 319.088