Scribd
Upload
220 views2 pages

Xercise P S P R: Roducing Ales and Rofit Eports

This document provides instructions for an exercise to create a sales and profit report in Excel using data from 5 subsidiaries of a solar panel company located in different cities. The report should include statistics on individual and total sales, profits, and costs calculated using Excel functions. A chart should also be included to visually compare sales and profits. Formatting and conditional formatting should be applied.

Uploaded by

Michael Bone-crusher Phiri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
220 views2 pages

Xercise P S P R: Roducing Ales and Rofit Eports

This document provides instructions for an exercise to create a sales and profit report in Excel using data from 5 subsidiaries of a solar panel company located in different cities. The report should include statistics on individual and total sales, profits, and costs calculated using Excel functions. A chart should also be included to visually compare sales and profits. Formatting and conditional formatting should be applied.

Uploaded by

Michael Bone-crusher Phiri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

BIS202 Exercises Year 2016/2017 – Semester 2

EXERCISE 2
PRODUCING SALES AND PROFIT REPORTS

1. PROBLEM STATEMENT
A SME companycalled SMART LIGHTspecialized in selling solar panels possesses 5 subsidiaries
across two continents, namely, in the cities New York, Los Angeles, London, Paris and Munich.
These cities are internally referenced by their codes C001, …, C005, respectively. At the end of each
quarter, a subsidiary should communicate to the headquarter the amounts of sales and costs done
during the quarter, however, monthly detailed. Based on the communicated data, the headquarter
wants to produce a quarterly that should entail statistics related to sales and profits done either
individually or globally. These statistics would be helpful for evaluating the activities of each
subsidiary and in making business decisions by the direction.

2. REQUIREMENTS
The details on sales and profits produced either individually or globally as well as the preferred
layout of the report required by the headquarter are depicted by the below table. Note the figured
amount and city facts on the table represent inputs (data) and the missing facts represented by a
question mark are information to be calculated using appropriate formulas and functions. Thus, the
task is to develop an Excel worksheet that should provide the same required details in content and
in [Link] an easy and a comprehensive way to view overall sales and profits comparatively, a
graphical representation using chart functions of Excel would be more advantageous as its shown below.

Page 1 of 2
BIS202 Exercises Year 2016/2017 – Semester 2

3. MAIN OBJECTIVES
The present assignment allows students learning and understanding the different items and topics
using Excel, including:

 Percentage operator
 Functions: SUM, SUM IF, COUNT IF
 Inserting Charts
 Format cells with styles
 Work with hyperlinked data
 Apply conditional formatting to cells

4. ACTIVITIES
a. Create the worksheet shown above.
b. Set the Text alignment, Columns width and high appropriately.
c. Use AutoFill to put the Series Numbers into cells A5:A7.
d. Format cells C3:G7, C8:E11, C13:E13 to include dollar sign with two decimal places.
e. Apply Sell Styles to the following cells:
 A1 (Accent 1)
 A2:G2 (Accent1 - 60%)
 A3:G7 (Accent 1 – 20%)
f. Find the Average Sales and Maximum Sales for each City.
g. Find the Total Sales for each Month.
h. Calculate the Profit for each month , where Profit = Total Sales – Cost
i. Calculate the 10% Bonus, which is 10% of the Profit.
j. Find the Total Sales for each Month; only for sales greater than 30,000.
k. Find the No of Sales for each Month; only for sales greater than 30,000.
l. Create the above Charts.
m. Insert a hyperlink to link the title in cell A1 to a website that shows the Sales of
Solar Lighting.
n. Apply Conditional Formatting to the Cells C8:E11 using the Color Scales (Red –
Yellow – Green).

5. NEED HELP?
Read Excel tutorials:

[Link]

Page 2 of 2

You might also like