Excel Project

Content-Based Assessments

Business Running Case | Excel 19

Nelson Architectural Planning Jennifer Nelson graduated with a Master of Architecture degree in 1997. She honed her space planning and design skills in a large architectural firm for several years before deciding to open her own firm. Nelson Architectural Planning specializes in corporate space planning, facility layouts, and interior design for high-tech companies in the San Francisco Bay and Silicon Valley areas of California.

Jennifer and her staff work closely with their clients to create efficient, safe, and beautiful work spaces that are conducive to a fast pace of busi- ness. Jennifer's team includes two network specialists who help assure that every client's space is scalable for continuous upgrades in computer systems and networking. Nelson Architectural Planning also maintains an inventory of popular office furniture and accessories, such as mobile workstations, office chairs, and desk lamps.

Business Running Case3 Excel

chapterthree

Content-Based Assessments

3 Excel

chapterthree

20 Excel | Chapter 3

Project 3J — Office Loan

In this project, you will construct a solution by applying any combination of the skills you practiced from the Objectives in Projects 3A and 3B.

Nelson Architectural Planning designs and stocks its own design of furni- ture and accessories. Some pieces are designed especially for health care facilities, an area of architectural design for which the firm is becoming well known.

President Jennifer Nelson wants to build an Office of the Future in which the firm will showcase its expertise in space planning, furniture products, design services, and state-of-the-art networking. The cost of construction is $750,000. Jennifer has requested a summary of the current product line and information on loan options to build the office and a storage area. In the following project, you will edit a workbook for Jennifer Nelson that provides the requested information. Your completed workbook will look similar to BRC Figure 1.

Business Running Case

BRC Figure 1

For Project 3J, you will need the following file:

e3J_Office_Loan

You will save your documents as 3J_Office_Loan_Firstname_Lastname

Content-Based Assessments

3 Excel

chapterthree

Business Running Case | Excel 21

1. Start Excel and from your student files, open the folder 03_business_running_case, locate the Business Running Case project for this chapter, and open the file e3J_Office_Loan. Save it in your Excel Chapter 3 folder as 3J_Office_Loan_Firstname_Lastname In cell B4, enter the NOW function, and then on the Home tab, in the Number group, click the Number Format arrow, and apply the Short Date format.

2. In cell F11, enter an IF function to deter- mine if the Quantity in Stock value in cell B11 is less than 15. If true, display Order and if false, display OK Copy the formula down through row 74. In the Stock Level column, use Conditional Formatting to apply a New Rule of Bold Italic to Format cells that contain the text Order

3. In cell C7, enter a COUNTIF function to count the number of Furniture items in the Category column. In cell C8, enter a COUNTIF function to count the number of Accessories items in the Category column. Select the range A10:F74, and then Insert a Table with headers. Apply Table Style Medium 17. Press C + h to display the top of the worksheet, select row 11, and then Freeze Panes. Sort the table from A to Z on the Product# column. Click the Design tab, and then change the Table Style Options to display the Total Row.

4. Filter the table to display the Furniture Category. Scroll as necessary to bring the Total row closer to the table headings. By using the Total Row Retail Price arrow, determine the average retail price for the Furniture category. Right-click over the result, click Copy, right-click over cell D7, click Paste Special, and then paste the Values and number formats. Determine

the average retail price for the Accessories category, and then copy the value and number format to cell D8.

5. Filter the table to display the Furniture Category. Then filter the Stock Level field to display only those Furniture items that display Order. In the Total Row, select the Order column, click the arrow, and then click the Count function. Copy the value and number format of the result, 23, to cell E7. Use the same techniques to deter- mine how many Accessories products need to be ordered, and then copy the value and number format to cell E8.

6. Remove all filters to display all of the data in the table, click any cell in the table, click the Design tab, and then remove the Total Row from the table. Convert the table to a normal range.

7. Unfreeze the panes. Insert a footer with the File Name in the left section, and then Center the worksheet Horizontally. Set the Print Titles option to repeat row 10 at the top of each page. Change the Orientation to Landscape, display the worksheet in Normal view, and then dis- play the top of the worksheet. Save your workbook.

8. Display the Loan Options-Showcase Office worksheet. In cell B9, enter a PMT function using the data in B3:B5; convert the rate to a monthly rate, and be sure that the result displays as a positive number. Format the result with zero decimal places.

9. Create a Data Table using the information in cells B3:B5 in which the Row input cell is the Period and the Column input cell is the Interest rate. Using the Format Painter, format the results in the data table using the format in cell B9.

Business Running Case

(Project 3J–Office Loan continued)

(Project 3J–Office Loan continues on the next page)

Content-Based Assessments

3 Excel

chapterthree

22 Excel | Chapter 3

10. Insert a footer in the left section with the File Name, and then Center the work- sheet Horizontally on the page. Display the top of the worksheet in Normal view.

11. Save your workbook. Print the Entire workbook or submit electronically as directed. If you are directed to submit printed formulas, refer to Activity 9.17 to do so.

12. If you printed your formulas, be sure to redisplay the worksheet by pressing C + `. From the Office menu, click Close. If you are prompted to save changes, click No. Close Excel.

Business Running Case

End You have completed Project 3J

(Project 3J–Office Loan continued)