CIS 1220 Final Exam

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)

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 same format.

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
Load time	Average delivery time for item (in days)

Figure1

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

Figure2

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 columnI 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. 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, each individual item would be the total number sold divided by 25,000 of the overhead expense. 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 quantity sold by the overhead, +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: Explore Alternatives Using Scenario Manager and Goal Seek

1. The shipping service Fashion Impressions uses is discussing a rate increase and has presented alternative pricing schemes. You need to determine the effect these changes have on shipping costs and overall profits.

Use Scenario Manager to perform these analyses. Save the original values as Original and then save each subsequent scenario as Scenario 1, Scenario 2, and Scenario 3. After inputting each scenario, create a Summary Report and name the sheet containing the report Scenario Summary.

Scenario 1: The shipping costs percentage is raised to 9% from 5%, and the minimum charge increased from $2.50 to $6.00.

Scenario 2: The shipping costs percentage is raised to 7% from 5%, and the minimum charge increases from $2.50 to $12.00.

Scenario 3: The Shipping costs percentage is raised to 15% with no minimum charge.

2. Use Goal Seek to determine an alternative Selling Price for Item #8 Plaid Blazer on the Products Worksheet, with the goal of increasing the Total Profits by an additional $100,000. Record this alternative price on the Scenario Summarybelow the Scenario Summary you completed in the previous step. Then return the selling price of the item #8 Plaid Blazer to its original value of $367.00 on the Products Worksheet.

Part Five: 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 Advertiseincells 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 Six: Loan Analysis Scenario

Fashion Impressions isconsidering a second distribution facility. After researching your options, you realized that you would need to take out a loan for $500,000, which you would like to repay over a ten year period. This includes packaging equipment, furniture, and additional licensures. The equipment and furniture will cost you 25% of your total investment. This is the amount to be used for depreciation. The current interest rate is the prime rate plus 3%. The prime rate is currently 3.25%, which means that your interest rate for your loan is 6.25%. You will use Straight Line Depreciation over a 6 year period to determine the cost of your depreciation. The estimated salvage value of your equipment is $75,000.

Create a Loan Analysis worksheet and Depreciation Worksheet using the following information:

1. What would your monthly payments be under the above scenario?

2. What is the future value of your loan after five years assuming you pay $4,000 per month?

3. How many total payments would it take to pay off this loan in years if you pay $4,500 per month?

4. How much could you borrow if you wanted to pay $4,000 per month over a 10 year period?

a.) Analysis

Do any of these scenarios seem feasible given your estimated monthly net income? Determine how depreciation affects your net income and cash flow? Remember depreciation is an expense, but it is a non-cash expense that you can add back in to increase your cash flow. Hint: see tutorial 9.

What are some other considerations to take into account if you wish to own your facility? How might that affect your analysis? Please save your responses to this section a.), b.) and c.) to a worksheet named Solver Model.

b.) Interpreting Solver Models

Using the solver model below, identify

a. The current value of the objective cell

b. Number of changing cells in the model

c. Logical values and whether or not the current constraints have been met

d. The arrays that are saved in the solver model and what the iterative Process is

Maximum Net Income Model
$9,903.00
4
TRUE
TRUE
TRUE
TRUE
32767
0
Minimum Material Expenses
69,874.00
4
TRUE
TRUE
TRUE
TRUE
32767
0

c.) Please define the following terms regarding Solver Reports and how they may be used in analyzing your business.

1. Binding and non-binding constraints

2. Slack

Save your file as Fashion Impressions.