Overview
For this assignment, you will demonstrate your knowledge using the features available in Excel. Many businesses track customers, products, and other financial data using software applications instead of pencil and paper. One reason for this is to manage data and information easily and to utilize creative and unique customization tools provided with Excel.
You will create a workbook for a company to use to input customer information, using formatting and page setup features, and explain why you should use Excel.
Hands-On Project 1 Directions
Open a blank workbook and then save the workbook as CIS105_Lesson6Project1_MEID.xlsx. NOTE: Change the MEID to your personal school MEID. Ex. as CIS105_Lesson6_ABC1234567.xlsx.
Rename the Sheet1 worksheet as Customers.
Enter the following data into the specified cells:
Cell A1: Bubbles & Bows
Cell A2: Date
Cell A20: Use to track customer sales for Bubbles & Bows Gift Shop
Cell B2: Customer Name
Cell C2: Customer Address
Cell D2: Contact Number
Cell E2: Total Sales
Cell F2: Discount Applied
Set the width of column A to 10.00.
Set the width of column B to 15.00.
Set the width of column C to 25.00.
Set the width of column D to 10.00.
Set the width of column E to 10.00.
Set the width of column F to 16.00.
Set the height of row 1 to 36.00.
Change the Font of the entire worksheet to Times New Roman with Font Size 11.
Merge & Center cells A1 through F1.
Merge & Center cells A20 through F20.
Apply Wrap Text to cell D2.
Apply Bold and center alignment to cells A2 through F2.
Apply Bold and font size 20 to merged cell A1:F1.
Apply Red font color to words in merged cell A1:F1.
Apply a fill color of your choice to merged cell A1:F1.
Apply italics and underline font style to merged cell A20:F20.
Select cell range A1:F20 and apply All Borders.
Add a new sheet to the workbook. Rename Sheet2 as Purpose.
On the Purpose worksheet, provide a minimum of 2-3 sentences to answer for each of the following questions:
Cell A1 answer – What software program group does Excel belong to, and when was it created?
Cell A3 answer – What tasks can you perform in Excel when compared to Word?
Cell A5 answer – Why do you think Bubbles & Bows Gift Shop might want to use Excel with their business?
Back on the Customers worksheet, enter appropriate data of your choosing (real or made up) data into all cells A3:D19. (Dates in column A, customer names in column B, customer addresses in column C, and contact phone number in column D).
Select columns B, C and D, and AutoFit Columns to the longest entry of each column.
Apply Narrow margins to your worksheet. (.75” Top/Bottom; .25 Right/Left).
Apply Landscape orientation.
Add a custom Header with your first and last names in the left section and your MEID in right section of the Header.
Add a custom Footer by inserting the DATE function in the left section and the words “Lesson 6” in the right section of the Footer.
Review your entire workbook for spelling.
Save your file and close. NOTE: Be sure your file is named CIS105_Lesson6Project1_MEID.xlsx.
Submit the assignment according to the instructions in the lesson.
PART 2
Overview
For this assignment, you will demonstrate your knowledge of how to create and modify different formulas and functions available in Excel. When businesses track customers, products, and other financial aspects, Excel’s features can help customize and personalize business needs and expectations.
You will continue using the Lesson 6 Project workbook you created in Project 1 to input customers’ personal and sales information. You will use additional formatting techniques, apply formulas and functions, and use a Quick Analysis tool to determine a varying sales total projection.
Hands-On Project 2 Directions
Open the workbook CIS105_Lesson6Project1_MEID.xlsx created in the previous project and save it as CIS105_Lesson6Project2_MEID.xlsx NOTE: Change the MEID to your Rio Salado MEID.
Delete column F.
Insert two new columns to the left of column E; you will now have columns through G.
Type the word Regular in cell E2 and Clearance in cell F2.
In each cell in range E3:E7, type 150.
In each cell in range E8:E14, type 100.
In each cell in range E15:E19, type 75.
In each cell in range F3:F9, type 30.
In each cell in range F10:F19, type 50.
Select cell range E3:F19, and apply the Accounting number formatting ($ English).
In cell G3, enter a formula to provide the answer when adding cells E3 and F3. Copy the formula down through G19 to add the values in columns E and F for each appropriate row.
Enter a new row after row 19.
Select cell range A1:G21, and apply All Borders.
Merge cells A20 through D20 and then apply the same fill color as in row 1.
In cell E20, enter the appropriate function to add E3:E19.
In cell F20, enter the appropriate function to add F3:F19.
In cell G20, copy the function used in F20 and use to add G3:G19.
Insert a new sheet and name the sheet Projections.
On the Customers worksheet, copy cell range E2:G20 and then with A1 active in the Projections worksheet, paste the copied cells.
On the Projections worksheet, AutoFit columns A through C.
In cell A21 on the Projections worksheet, enter a Function that will add the current date.
On the Projections worksheet, click into cell C21 and then enter a Scenario Manager What-If Analysis completing the following:
In the Add Scenario dialog box:
Scenario Name – No Clearance
Changing Cells – B2:B18
Do not make any changes to the Comment
Protection – click “Prevent Changes” box
In the Scenario Values dialog box, change each value to 0 then press OK. TIP: Pressing tab after each zero will take you to the next value entry box.
Once you are back to the Scenario Manager dialog box, press Show and then Close.
Apply Bold to cell G20 on the Customers worksheet and apply Bold to cell C19 on the Projections worksheet.
On the Customers worksheet, add a custom Header with your name in the left section and your MEID in the right section of the Header.
On the Customers worksheet, add a custom Footer with the Date function in the left section and the words Lesson 6 in the right section of the Footer.
Review your workbook for spelling.
Save your file and close Excel.
Place the following files in a folder and label it “Lesson 6.” Zip the folder as described in the Syllabus.
CIS105_Lesson6Project1_MEID.xlsx
CIS105_Lesson6Project2_MEID.xlsx
Submit the assignment according to the instructions in the lesson.
