Reference no: EM131586947

ASSIGNMENT

PROJECT OVERVIEW

You are in the accounting department for Independence Home Improvement, a large hardware and home improvement store. Now that you've converted the company's accounting records to Excel, the manager would like you to work on an analysis of the inventory. Although many more items will be added, the worksheet has enough information for you to begin your work.

Instructions

1. Create a formula in cell G4 that calculates the value of the on-hand inventory, based on the price paid for the item, in cell C4. Copy the formula into the range G5:G14. Format the range G4:G14 in the Comma Style.

2. In cell H4, use an absolute reference to the markup percentage in cell K1 to calculate the selling price of one wrench set. (Hint: Multiply the price paid by the markup percentage.) Copy the formula into the range H5:H14.

3. Add the bold attribute to the column headings, and italicize the items in A4:A14.

4. Increase the width of column A to be at least 19.00 characters (or 138 Pixels).

5. Format the Selling Price column so it displays the Accounting Number Format with two decimal places.

6. Change the Price Paid column so it displays the Comma style with two decimal places.

7. Add a row directly under wrench set for Box of Nails, price paid 1.31, sold individually (each), with 56 on hand and 756 sold YTD. Then enter appropriate formulas in cells G5 and H5.

8. Use the spelling checker to correct and replace any spelling errors in the worksheet.

9. Use conditional formatting on the range E4:E15 to call attention to items with a quantity of less than 20 on hand. Use yellow fill with dark yellow text.

10. Create an icon set for the range E4:E15 using the symbols of your choosing.

11. Delete the row containing the Nail Gun item.

12. Add "all borders" in cells A3 to H15. This allows you to see the cells when you print.

13. Insert a table with headers to the area A3 to H14. Select the color of your choice.

14. Create a formula in cell B4 to show the Year to Date (YTD) Profit by subtracting the price paid from the selling price and multiplying by Total Sold YTD. (Hint: You'll need to put parentheses () around the subtraction to tell Excel to compute that part first). Format B4:B14 in the comma style.

15. Use the auto sum button to get the Total YTD Profit in cell B15.

16. Add a pie chart to summarize the YTD Profit of each item. Move the pie chart under the table.

17. Change the font size of "Independence Home Improvement" in cell A1 to Times New Roman, 36 point.

18. Save the file using the naming convention APP101_your last name_week4_assignment. Compare your work to the sample shown below.

Attachment:- Attachments.rar