CIS 1220 Level Two Exam

This is your Level Two Exam. You can use your class notes, textbook, D2L tutorials, and the Internet; but not a tutor. 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) 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!Note that the ranges for referencing may change slightly depending upon your formatting. The references are based on the original data file. 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. Create and format a Chart G. Create a macro that will move the chart to a chart sheet and print a PDF 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 same format.This may change your referencing ranges. 2. The Date 3. Your name as the author 4. The Purpose (you can summarize the problem statement above for the purpose). 5. 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. 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 Description Item# Unique identification code for each item Description Description of clothing item Selling Price Price charged to retailers or each clothing item Material Cost Cost of fabric, trimmings, etc. required for manufacturing Lead time Average delivery time for item (in days) Figure 1 Entry Description Orders# Unique order identification number Account# 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 sold at multiple store locations). Quantity Number of items ordered Item# Type of item ordered Date Ordered Day the retailer placed the order Date Delivered Day the retailer received the order Figure 2 2. In column G of the Orders worksheet, enter the selling price of the item (as provided in the Products worksheet) for each order in the table. Label the column Selling Price per Item. 3. In column H, enter the material cost of the item (as provided in the Products worksheet) for each order in the table. Label the column Materials Cost per Item. 4. In column I, you need to enter the shipping costs of the item for each order in the table. This value can be calculated as $2.50 or 5% of the material costs, whichever is greater. (Hint: you can use the MAX function or IF function). Create a named range called input in the Orders worksheet in the range A100:B105. In cells B100 and B101 enter the preceding parameters ($2.50 and 5% of the material cost) for calculating shipping cost. Create appropriate range names for the values in these cells. Then use these range names to reference these cells when writing the formula to calculate the shipping costs per item for each order. Copy this formula down column I and then label the column Shipping Costs per Item.(Hint: you can also use a nested if statement). 5. In column J, you need to calculate and enter the labor costs per item. These costs are based on percentages of material cost. (Hint: this is a nested if statement). a. For products with material costs less than $100, labor cost will equal 100% of the material cost. b. For products with material costs of at least $100 but less than $500, labor cost will equal 50% of the material cost. c. For products with material costs of at least $500 or more, labor cost will equal 20% of the material cost. Label column J Labor Costs per Item, and then calculate the labor costs per item by entering these parameters in B103:B105 of the input range you created in step 4. 6. In column k, you want to calculate the overhead costs per item. The total overhead for the month was $150,000. This overhead will be applied equally to each item sold, regardless of the price of the item. For example if a total of 25,000 items were sold during the month, the overhead for each individual item would be total overhead divided by 25,000.Write an appropriate formula to calculate the overhead cost per each item, and label column K Overhead Costs per Item. (Hint: you are dividing the total overhead by the total number of items sold, +5 extra credit for using the ROUND function, and using two decimal places). There is more, you will need to use mixed absolute references to copy this formula. 7 Add the following headings to columns L through Q, and create formulas to calculate the data for these columns. Display the values in Currency format with no decimal places. Column L-Total Revenues per Order (selling price per item multiplied by quantity) Column M-Total Material Costs (material cost per item multiplied by quantity) Column N-Total Shipping Costs (shipping cost per item multiplied by quantity) Column O-Total Labor Costs (labor cost per item multiplied by quantity) Column P-Total Overhead Costs (overhead cost per item multiplied by quantity) Column Q-Total Profit per Order (total revenue per order minus total costs per order as provide in columns M through P) 8. In column R calculate the profit margin for each order by determining the profit as a percentage of revenue. Format the result as a percentage rounded to the nearest tenth decimal place. Label column R Profit Margin. Hint: the equation is Total Profit per Order divided by Total Revenues per Order. 9. Perform the following aggregate calculations at the bottom of the table you have created. Separate these calculation from the rest of the order data by leaving row 42 blank, and filling this row with the color blue. • In row 43 calculate the averages for those values in columns G through Q • In Row 44 calculate the totals for those values in columns L through Q • In cell R43, calculate the average profit margin based on the aggregate revenue and profit values. To highlight this value, apply a yellow fill color to the cell. 10. Next, you will analyze the orders by profit margin and assign each order to a profit margin category. Label column S Profit Category and then write a formula that will determine the profit margin category of each order. Use conditional formatting to visually highlight the profit margin categories. The Formulas should be written to automatically update if there are changes to the input values. Use the following guidelines for completing this step: • Display the word “High”with profit margins over 40%. • Display the word “Low” profit margins between 0% and 40% inclusive. • Display the word “Loss” profit margins that did not yield a profit. Part Three: Analyzing Data using VLOOKUP In addition to being concerned about profits, management at Fashion Impressions is also concerned with quality service to their customers the most critical service concern is “on-time delivery.” Customers are given lead time in the number of days from order placement to deliver. Fashion Impressions wants you to determine which orders were delivered on time. They are considering providing rebates to those customers who received their orders late, and they want you to also calculate the cost of this rebate. 1. On the Orders worksheet, add a column to the table labeled On Time? Write a formula using the VLOOKUP function to specify each order’s delivery status as follows: On Time: is indicated by a “true value” (you will actually want to return a value as the text TRUE (delivered on or before the projected arrival date). Late: is indicated by a “false value” (you will actually want to return a value as the text FALSE (delivered after the projected arrival date). (Hint: You will need to determine the elapsed time between the date the order was placed and the date the order was delivered, and then compare the elapsed time to the specified Lead time for the item ordered. Remember, lead times are measured in days and are listed by item number on the Products worksheet.) 2. Fashion Impressions has asked you to estimate the cost to the company if it were to retroactively give a 15% rebate (based on total sales price) on each order that was delivered late. Add another column to the Orders worksheet to calculate the rebate value by order (zero dollars if no rebate) and the total rebate for all orders. Label this column Rebate. Part Four: Use Nested IF, SUMIF, COUNTIF, and AND/OR arguments. Management has asked you to calculate the profits earned from the individual items sold last month. This information can then be used to determine which items Fashion Impressions should continue to sell and possibly promoted further with targeted advertising. 1. On the Products worksheet, label column F Total Profit per Item, and then in this column calculate the total profit for each item based on the orders of that item placed during the month (Hint: Use the SUMIF function) 2. In Cell F2, total the values you entered into column F in the previous step. (Hint: This value should equal the value displayed in cell Q44 of the Orders Worksheet.) Apply a yellow fill to this cell. 3. On the Products worksheet, add a column to the table and label it Recommendation. Use a nested If statement to display one of the following recommendations for each item: a. Discontinue: if the profit for the item is negative (i.e., the company is losing money). b. Continue As Is: If the item profit is less than 10% of the overall profit for all items, but is not losing money. (Hint: You can use an AND argument). c. Advertise: If the item profit is greater than 10% of the overall profits. 4. Create a table in the range F22:G24 that summarizes how many items should be discontinued, continued as is, and those items that should be advertised. Place the labels Discontinue, Continue As Is, and Advertise incells F22:F24,and calculate the corresponding totals using the COUNTIF function in cells G22:G24. Sort the Categories Discontinue, Continue As Is, and Advertisein ascending order. Part Five: Summarize Profits Using Subtotals Fashion Impressions is considering offering an incentive to their top accounts to encourage them to purchase more products. You will identify the top accounts for management. 1. Copy the account #, Total Revenues per Order, and the Total Profit per Order columns from the Orders worksheet, and insert the copied columns into a new worksheet in the workbook. This new worksheet should automatically update if any of the values on the Orders worksheet are changed. Name the sheet Customers. 2. Insert subtotals to calculate the revenues and profits for each account. Click the Level 2 Outline button to show only subtotals by account. 3. In column D, calculate the percentage of total profit per account, Label the column % of Total Profit. 4. Apply conditional formatting to column D to highlight the accounts that are responsible for 10% or more of the total profits. Part Six: Create a Pivot Table 1. Use the table from the Products Worksheet to create the pivot table below. Do not include the totals data, only the product data. Refer to the following figure as a guide. Name the pivot table Percentage of Profits: Figure 3 Part Seven: Create a Chart and a Macro 1. Create a chart that compares the profit contributions (percentage of total profit) from each of the items Fashion Impressions sells. Use a chart that you feel best illustrates this information (Hint: Pie Charts are good). The legend should contain the Item descriptions as provided on the Products worksheet. Make sure to include a chart title and display the profit values within the categories of the chart. Choose an appropriate chart style. Move the chart to a separate worksheet and name the chart. Create a macro that prints a PDF of the chart. Name the macro, short-cut key, and description appropriately. Add a button for your macro. Please submit your PDF file with your test submission. Save your file as Fashion Impressions both as a workbook and a macro enabled workbook.