The Wine Depot, located in Santa Barbara, California, imports and sells high-quality wine from around the world. Owner Barbara Fairfield is curious to see how Excel might help her manage the business and account for its inventory.
Her husband, Bud, is the accountant in the family but hasn’t had much experience with Excel. He’s hired you to help him construct several worksheets to help the business better understand the financial and managerial aspects of the company.
To begin, you decide to create a worksheet of some of Barbara’s wine inventory. Start with worksheet ch2-06. Format the company name and worksheet title bold and italics. Format the title of each column of data bold with a bottom border. Format all values for the column Price as Accounting Number Format.
Add two column labels, Quantity and Value, to the right of the existing data and then insert quantity amounts as shown below
SKU                         Quantity
17521 ..........................    4
16716 ..........................  10
16528 ..........................  12
16739 ..........................    5
15347 ..........................    7
17539 ..........................    3
11599 ..........................    1
14539 ..........................    5
17840 ..........................  12
13883 ..........................  12
15966 ..........................  24
17454 ..........................  24

17024 ..........................  10
16554 ..........................    8
17425 ..........................    7
17549 ..........................    3
17578 ..........................    2
17275 ..........................    1
Insert a formula to calculate value as quantity times price in cell I9, then fill-down this formula for all cells in the table. Insert a text label ‘‘Total’’ in cell H28 and a formula to calculate the total value in cell I28. Insert two additional rows after row 26. Add two American Syrah wines in those two rows: Carhatt, $34 (750 ml, year 2014, SKU 16769), quantity 10; and Cafaro, $35 (750 ml, year 2014, SKU 16874), quantity 15. Delete the row containing the American Merlot from Wildhorse. Change the formatting of the Price and Value columns to
include no decimals. After using Excel’s Help function to learn how to sort items in a worksheet, sort the information on your worksheet by location, by type, and then by winery, in A to Z order. Change the name of Sheet 1 to ‘‘Pricing.’’ Create a copy of this worksheet and place it before Sheet2, then change the name of this sheet to ‘‘Cost.’’ Add a label at cell C4 of the Cost sheet called ‘‘Cost %’’ in Bold, Italics. Type 60% as the cost percentage in cell D4 of the Cost sheet. (Make sure this cell is formatted as a percent.) Change the name of the column ‘‘Value’’ to ‘‘Cost.’’ Create a new formula in cell I9 to calculate cost as Price times Cost % times Quantity. (Be sure to use absolute or relative references where appropriate.) Fill the formula in cell I9 down to all items. Save the file as ch2-06_student_name (replacing student_name with your name).
a. Print the completed Pricing worksheet in Value view, with landscape orientation, scaling to fit to 1 page wide by 1 page tall, and with your name and date printed in the lower left footer and file name in the lower right footer.
b. Print the completed Cost worksheet in Value view, with landscape ori-entation, scaling to fit to 1 page wide by 1 page tall, and with your name and date printed in the lower left footer and file name in the lower right footer.
c. Print the completed Pricing worksheet in Formula view, with gridlines and row and column headings, landscape orientation, scaling to fit to 1 page wide by 1 page tall, and with your name and date printed in the lower left footer and file name in the lower right footer.
d. Print the completed Cost worksheet in Formula view, with gridlines and row and column headings, landscape orientation, scaling to fit to 1 page wide by 1 page tall, and with your name and date printed in the lower left footer and file name in the lower right footer.