Bob is the co-owner of Blooming Everywhere Delivery, a floral delivery service accessed exclusively through mobile apps, and is preparing a report for his business partner on the company’s financials for the years 2018-2020.
computer science
Description
Blooming Everywhere Delivery
creatING a worksheet and a chart
GETTING STARTED
·
Open the file SC_EX16_1a_FirstLastName_1.xlsx,
available for download from the SAM website.
·
Save the file as SC_EX16_1a_FirstLastName_2.xlsx
by changing the “1” to a “2”.
o
If you do not see the .xlsx file extension in the Save As dialog
box, do not type it. The program will add the file extension for you
automatically.
·
With the file SC_EX16_1a_FirstLastName_2.xlsx
still open, ensure that your first and last name is displayed in cell B6 of the
Documentation sheet.
o
If cell B6 does not display your name, delete
the file and download a new copy from the SAM website.
PROJECT STEPS
1.
Bob is the co-owner of Blooming Everywhere
Delivery, a floral delivery service accessed exclusively through mobile apps,
and is preparing a report for his business partner on the company’s financials
for the years 2018-2020.
Open the Company Analysis worksheet and use AutoFit to adjust the width of column A.
2.
Select cell A1, enter the text Blooming Everywhere Delivery, and then center the merged range A1:D1.
3.
Apply the Heading
1 cell style to the merged range A1:D1.
4.
Enter the text Revenue in cell A2, and then merge
and center the range A2:D2.
5.
Clear the contents of cell E3.
6.
Enter the data shown in Table 1 below into the
range B7:D7.
Table 1: Data for the Range B7:D7
|
Cell |
B7 |
C7 |
D7 |
|
Value |
1800 |
1950 |
2020 |
7.
Using the Fill Handle, copy the formula in cell
B8 into the range C8:D8.
8.
Merge and
center the range A10:D10.
9.
Select the range B12:D16, and then apply the Accounting number format with two decimal places and $ as the symbol.
10.
The Expenses table shows all of the company’s
expenses from 2018 to 2020. Bob wants to calculate the total expenses by adding
up the total expenses from each year.
In cell E16, create a formula using the SUM function to calculate the total
expenses from 2018 to 2020 (B16:D16).
11.
Update the formatting of the merged range
A18:D18 as described below:
a.
Bold
the contents of the merged range.
b.
Change the font to Calibri.
c.
Change the font size to 13 pt.
d.
Change the font color to Green, Accent 6, Darker 25% (10th column, 5th
row of the Theme Colors palette).
12.
The company’s competition and taxes have
increased through the years, and Bob will only recommend continuing to grow the
business if the profit increases every year.
In cell B20
create a formula without using a function that calculates the profit earned in
2018 by subtracting the total expenses in 2018 (cell B16) from the total revenue in 2018 (cell B8).
Using the Fill Handle,
fill the range C20:D20 with the formula from cell B20.
13.
Change the tab color of the Company Analysis worksheet to Orange
(3rd column, 1st row of the Standard Colors palette).
14.
Select the clustered column chart with the title
Blooming Everywhere Delivery 2018 – 2020 Revenue. Update the chart as described
below:
a.
Change the chart style of the clustered column
chart to Style 5.
b.
Move the chart to its own chart sheet. Use 2018-2020 Revenue as the name for the
new chart sheet.
15.
Switch back to the Company Analysis worksheet. Select the non-adjacent ranges A12:A15
and D12:D15, and then create a 3-D Pie chart.
Update the chart as described below:
a.
In the title placeholder, enter Expenses in 2020 as the chart title.
b.
Change the chart style of the 3-D pie chart to Style 3.
c.
Move the chart to its own chart sheet, using 2020 Expenses as the name for the new
chart sheet.
Your workbook should look like the Final Figures
on the following pages. Save your changes, close the workbook, and then exit
Excel. Follow the directions on the SAM website to submit your completed
project.
