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 nameFashion
Impressions. The Workbook contains two worksheets—Products and Orders. The
content of these worksheets is described in Figures 1 and 2.

 

Figure1

 

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 columnSelling 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 columnMaterials
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 columnShipping
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 JLabor 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 KOverhead 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 RProfit
Margin. Hint: the equation isTotal
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 SProfit 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 ofdays 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 labeledOn 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 theProducts 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 columnRebate.

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
theProducts Worksheet, with the
goal of increasing the Total Profits by an additional $100,000. Record this
alternative price on theScenario
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 theProducts 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 FTotal 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 itRecommendation. 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 labelsDiscontinue, Continue As Is,and Advertiseincells F22:F24,and
calculate the corresponding totals using the COUNTIF function in cells G22:G24.
Sort the CategoriesDiscontinue,
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

 

 

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.