accounting help

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.

5455

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 [Note: SKU (Stock Keeping Unit) is a unique identifier for each of the distinct products and services that can be ordered from a supplier.]:

Top of Form

nsert a formula to calculate value as quantity times price in cell 19, 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 27. Add two American Syrah wines in those two rows: Carhatt, $34 (750 ml, year 2000, SKU 16769), quantity 10; and Cafaro, $35 (750 ml, year 2000, 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 and then by type, in ascending order. Change the name of Sheet1 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. Change the name of the column “Value” to “Cost.” Create a new formula in cell 19 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 portrait 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 portrait 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.

· c. Print the completed Pricing worksheet in Formula view, with gridlines and row and column headings, portrait 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, portrait 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.