OFF$2395$ Assignment $1$

This assignment relates to the following Course Learning Requirements:

CLR $1$: Describe general concepts of spreadsheet software.

CLR $2$: Create, format, enhance, and manage worksheets and workbooks.

CLR $3$: Perform mathematical calculations using formulas and functions in worksheets.

CLR $4$: Create charts to display worksheet data visually.

Objective of this Assignment:

The objective of this assignment is to display the Excel skills you have learned in Modules $1-4$ in a practical way.

Pre$-$Assignment Instructions:

$1.$ To prepare for this assignment, ensure that you have read Modules $1-4$ in the textbook.

$2.$ Ensure that you have completed all Practice Activities associated with each module.

Assignment Tasks:

GETTING STARTED

$$ In the Assignment $1$ folder, download the Excel workbook titled OFF$2395$ Assignment $1$ Data File.

$$ Save the file as Lastname$\_$OFF$2395\_$Assignment$1(2$ points$)$

$$ Take note of the $$Required Sheet$$ worksheet. This is a required component of this assignment. If you delete or modify that worksheet, you will receive a mark of $0($zero$).$

ASSIGNMENT STEPS

$1.$ Kamal Haman directs the Ottawa office of Harp Worldwide, a global communications company. He has been tracking revenues and expenses along with customer data in an Excel workbook, including charts to help him visualize the data. He has asked you to help him complete the workbook and insert additional charts.

Go to the Revenue & Expenses worksheet. In cell K$1,$ insert a formula using the TODAY function to display today's date. $(2$ points$)$

$2.$ Fill the range D$4$:F$4$ with a series based on the value in cell C$4$ to provide the missing month names. $(2$ points$)$

$3.$ Format the text in cell A$4$ as follows to make it readable and more meaningful: $(8$ points$)$

a$.$ Merge and center the contents of the range A$4$:A$17.$

b$.$ Rotate the text in the merged cell up to $90$ degrees so it reads from bottom to top.

c$.$ Middle$-$align the merged cell.

d$.$ Resize column A to a width of $6\mathrm{.}00.$

$4.$ Use AutoFit to resize column B to its best fit to display all the revenue and expense types. $(2$ points$)$

$5.$ Complete the calculations for the Revenue data as follows: $(6$ points$)$

a$.$ In cell C$7,$ enter $47,600$ as the Business services revenue for September.

b$.$ In cell C$8,$ use the SUM function to total the September Revenue values.

c$.$ Copy the formula in cell C$8$ to the range D$8$:F$8$ and to cell H$8$ to complete the totals.

$6.$ Using the Cell Styles button, format the nonadjacent ranges C$13$:F$16$ and H$13$:H$16$ using Comma style and no decimal places to match the formatting of the Revenue data. $(2$ points$)$

$7.$ Kamal wants to display the highest and lowest revenue amounts from September to December. Enter and format this information as follows: $(6$ points$)$

a$.$ In cell C$24,$ enter a formula using the MIN function to display the lowest revenue in the range C$5$:F$7.$

b$.$ In cell C$25,$ enter a formula using the MAX function to display the highest revenue in the range C$5$:F$7.$

c$.$ Apply Outside Borders to the range B$24$:C$25$ using Tan, Background $2,$ Darker $75\%$ as the border colour to show the information belongs together.

$8.$ In the clustered column chart in the range J$3$:P$17,$ Kamal wants to show the expenses by type, not by month. He also wants to make the contents of the chart clearer. Provide this information for him as follows: $(12$ points$)$

a$.$ Switch the rows and columns to display expenses by type.

b$.$ Move the legend to the right side of the chart using the Chart Elements button.

c$.$ Add Monthly Amount as the primary vertical axis title.

d$.$ Add Expenses by Type as the chart title.

e$.$ Change the fill colour of the October data series to Brown, Text $2.$

f$.$ Add a border to the entire chart using the Orange, Accent $2$ shape outline colour. Ensure that the chart corners are still within J$3$ and P$17.$

$9.$ Kamal wants to include a chart showing the monthly profits for the Ottawa office to determine which months have been more favorable. Create a new chart as follows: $(8$ points$)$

a$.$ Create a doughnut chart based on the range C$21$:F$22.$

b$.$ Resize and reposition the chart so that its upper$-$left corner is within cell J$20$ and its lower$-$right corner is within cell P$34.$ Note: if the edges of the chart spill into the adjacent cells, you will not receive marks for this step. Edges must be within the cells indicated.

c$.$ Enter Sept to Dec Profit as the chart title.

d$.$ Apply Layout $6$ to the chart to display percentages on each part of the doughnut.

$10.$ Kamal also wants to include a chart showing the revenue earned from mobile phones, wireless services, and business services. Create and format a chart for him as follows: $(10$ points$)$

a$.$ Create a Stacked Column chart $($the default option$)$ based on the range B$4$:F$7.$

b$.$ Using the Move Chart button, move the chart to a new sheet named Revenue Chart.

c$.$ Change the chart style to Style $7$ to match the style of the clustered column chart on the Revenue & Expenses worksheet.

d$.$ Change the font size of all the chart text to $14$ point to ma