ADVANCED EXCEL LAB @ HOME 6

1.

    Consider a scenario of Ideas Bookstore, which is a popular book store in Mississippi. The sales

details of the previous month are recorded in a Microsoft Excel worksheet. As it was the fifth

anniversary month of the store, Kathy, the Store Owner, decides to give a discount voucher to all

the customers who purchased books in the anniversary month on their next purchase. Each

customer will be given a discount voucher of 15% of their bill, if they have purchased books worth

$ 1800 or above in the previous month, otherwise, a voucher of 5% of their bill. You are required

to help Kathy to decide the amount of discount voucher that should be given to the customers.

ANS: DOWNLOAD

2.

   

To expand the business, the managementof Alpha Supermart decides to open a new branch.

Before opening the branch, themanagement wants to create business plans to decide the location

where the newbranch should be opened. To create the strategic plans and to make theimportant

business decisions, the management needs to compare the month wiserevenues of the last two

years. Therefore, the management asks Alvin, the SalesExecutive, to create a report that

graphically depicts the sales data of thelast two years. To accomplish this task, Alvin decides to

insert a chart in thereport. Alvin wants to make this report understandable by adding the following

features to the chart:

The chart title at the center, atthe top of the report

The title for the horizontaland vertical axes

The data table beneath thechart

The data labels at the top ofeach bar in the chart

Help Alvin accomplish the desiredtasks.

ANS: DOWNLOAD

3.

    Harry asks Alvin tocreate a report containing the product wise revenue distribution of the

totalrevenue generated in 2011. To accomplish this task, Alvin creates a textualreport and submits

the same to Harry. When Harry analyzes this report, he failsto understand the individual

contribution of each product in the total revenue.Therefore, he asks Alvin to generate a meaningful

graphical report. Help Alvinaccomplish the desired task.

 ANS: DOWNLOAD

4.

   Alvin sends the report containing themonth wise revenue details of the last two years to Peter, the

Senior SalesManager. After reviewing this report, Peter asks Alvin to create a report thatonly

contains the chart and its components. In addition, he asks Alvin that thereport should not contain

the details of the data sets. Further, he asks Alvinto use the similar formatting style and layout in

all the graphical reports infuture. Help Alvin accomplish the desired tasks.

ANS: DOWNLOAD

5.

   At the beginning of 2011, Harry has asked Alvin to create a report on the sales of 2010 depicting:

The total sales made against each client.

The total sales made against all the clients.

The percentage of the total sales generated from each client.

The total number of transactions made against each client.

The total number of transactions made against all the clients.

Therefore, Alvin has created the following sales report.

Similarly, at the beginning of 2012, Harry has asked Alvin to create the similar report for the sales

of 2011. Help Alvin create this report.

ANS:DOWNLOAD

6.

    Consider the scenario of Paul Furnishers Inc. While generating the sales report for the last week

at the end of each month, Alvin needs to insert a chart in the report to illustrate the comparison

among the total sales of all the weeks in the month. To avoid repetition of steps performed to

insert a chart in the last weekly sales report created each month, Alvin decides to automate the

task of inserting the chart in the worksheet. Help Alvin in accomplishing the required task.

ANS: DOWNLOAD