CIS 1220 Final Exam Problem Statement Fashion Impressions is a


Question Description:

35

Hello, Can you help me with this assignment? the instruction for this assignment is in the attachment below CIS+1220+Spring+2016+Final.docx CIS 1220 Final Exam Problem Statement Fashion Impressions is a designer clothing manufacturer interested in improving service to their retailers, as well as improving their financial tracking of the various profit components. The company uses Excel to track, store, and analyze all the data associated with its retailer, clothing, and billing. To determine ways to improve sales, management has hired you to thoroughly examine the retailer and clothing information for a sample month’s orders. Your job is to analyze the data and report back to management. You will use various Excel tools and functions as appropriate for each task. Remember that all calculations should be based on input values that can be easily modified (i.e. the price of an item is stored in only one place and all calculations referencing price refer back to that original input cell). In addition, all final worksheets should look professional. This may require modifying cell formats, text wrapping, fonts, colors, and so on, as you see fit. Data files: Fashion.xlsx (in d2L) 1 Project Outline: Format all titles, headings, and data appropriately. Use your text, past tutorials, and cases for reference. Use font styles and fill color to differentiate parts of your worksheets. Use number formatting to emphasize your data. You will lose points if you do not format data and text. Each student may have different formatting. That is fine! A. Develop a Worksheet for Profit Calculations and Analysis a. You will need to know how to calculate percentages of totals for comparison b. You will need to reference several worksheets as well as create several worksheets. B. Analyze Data using Vlookup, including nested Vlookups C. Name ranges D. Use Nested If, Sumif, Countif Functions in your formulas as well as And/Or Arguments E. Summarize Profits Using Subtotals F. Explore alternatives using Scenario Manager and Goal Seek G. Create a loan analysis. Note that your referenced cells may be different than what is in the instructions depending upon how you format you worksheets. Part One: Download the data file from D2L. Add a documentation sheet with the following information: 1. The Workbook Title: “Fashion Impressions”. This title should be on each worksheet in the 2. 3. 4. 5. same format. The Date Your name as the author The Purpose (you can summarize the problem statement above for the purpose). Format the text as you feel is appropriate (note: non-formatted text will not receive credit). You can use prior tutorials and assignments from you textbook as examples. 1 Part Two: Develop a Worksheet for Profit Calculations and Analysis using VLOOKUPs and Advanced Functions. In this section, you will be calculating Fashion Impressions’ revenues, expenses, and profit per item, and per order during a one month period. Once you have determined these values, you will be totaling the values and summarizing profits into groupings that will help fashion Impressions ultimately make decisions about the items they sell to their customers. 1. Open the Fashion.xlsx workbook and save it with the name Fashion Impressions. The Workbook contains two worksheets—Products and Orders. The content of these worksheets is described in Figures 1 and 2. Entry Item# Description Selling Price Material Cost Load time Description Unique identification code for each item Description of clothing item Price charged to retailers or each clothing item Cost of fabric, trimmings, etc. required for manufacturing Average delivery time for item (in days) Figure 1 Entry Orders# Account# Quantity Item# Date Ordered Date Delivered Description Unique order identification number Retailer number that placed this order (information about the specific retailers is not included in the workbook. Retailers are often large companies that order quantities to be sol

Answer

35