Hello, Can you help me with this assignment? the instruction for this assignment is in the attachment below

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 worksheetsProducts 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 sold at multiple store locations). Number of items ordered Type of item ordered Day the retailer placed the order 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 1 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. 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. 1 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 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 Summary below 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 Advertise in cells F22:F24, and calculate the corresponding totals using 1 the COUNTIF function in cells G22:G24. Sort the Categories Discontinue, Continue As Is, and Advertise in ascending order. Part Six: Loan Analysis Scenario Fashion Impressions is considering 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 1 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. 1 Item# Description 1 Silk Brocade Gown 2 Alligator Skin Belt 3 Black Cocktail Dress 4 Cashmere Scarf 5 Lavender Silk Blouse 6 Lace Shawl 7 Cashmere Blend Suit 8 Plaid Blazer 9 Black Velvet Gown 10 CroppedTee Shirt 11 Navy Blue Pant Suit 12 Cotton Crisp Blouse 13 Bikini & Wrap 14 Khaki Trousers 15 Black Trousers 16 Black Wool Blazer 17 Black Skirt 18 Capri Khaki Trousers Selling Price Materials Cost $1,800.00 $500.00 $300.00 $98.00 $1,500.00 $700.00 $100.00 $10.00 $100.00 $25.00 $25.00 $15.00 $1,000.00 $650.00 $367.00 $125.00 $625.00 $225.00 $50.00 $20.00 $300.00 $185.00 $60.00 $30.00 $253.00 $150.00 $100.00 $35.00 $100.00 $35.00 $175.00 $75.00 $200.00 $75.00 $100.00 $40.00 Lead Time 60 25 35 25 20 15 30 30 0 15 30 25 35 20 20 25 25 15 Order# Account# Quantity Item# 1 310 1250 2 234 500 3 75 144 4 266 2400 5 233 1300 6 234 1650 7 299 200 8 157 200 9 94 100 10 94 400 11 94 100 12 310 11200 13 94 600 14 112 350 15 112 225 16 112 199 17 356 498 18 75 160 19 455 120 20 233 1700 21 455 33 22 81 400 23 81 500 24 81 600 25 310 2200 26 299 115 27 76 12 28 199 44 29 213 860 30 213 100 31 110 155 32 110 45 33 299 152 34 85 500 35 255 500 36 88 24 37 88 100 38 88 200 39 88 400 40 88 600 Date Ordered Date Delivered 1 3/1/2010 5/20/2010 15 3/2/2010 5/24/2010 10 3/2/2010 5/2/2010 9 3/3/2010 4/14/2010 16 3/3/2010 5/27/2010 7 3/7/2010 3/26/2010 5 3/6/2010 5/22/2010 3 3/6/2010 5/20/2010 11 3/6/2010 5/22/2010 9 3/8/2010 5/5/2010 5 3/9/2010 4/5/2010 17 3/4/2010 3/20/2010 9 3/22/2010 4/8/2010 2 3/1/2010 5/22/2010 14 3/8/2010 4/15/2010 6 3/12/2010 4/14/2010 7 3/16/2010 4/3/2010 18 3/9/2010 6/9/2010 10 3/5/2010 5/19/2010 1 3/9/2010 5/25/2010 13 3/31/2010 4/9/2010 9 3/2/2010 5/20/2010 6 3/8/2010 5/29/2010 10 3/12/2010 5/2/2010 7 3/9/2010 4/10/2010 6 3/10/2010 6/2/2010 2 3/4/2010 4/19/2010 8 3/5/2010 3/24/2010 3 3/9/2010 4/3/2010 4 3/15/2010 3/24/2010 9 3/4/2010 4/2/2010 10 3/9/2010 3/29/2010 1 3/12/2010 4/18/2010 8 3/19/2010 5/6/2010 7 3/21/2010 4/20/2010 6 3/19/2010 6/8/2010 12 3/19/2010 4/25/2010 9 3/19/2010 4/5/2010 14 3/19/2010 5/12/2010 6 3/31/2010 5/17/2010 $ 5% 2.50 Part Six: Loan Analysis Scenario Fashion Impressions is considering 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 $9,903 b. Number of changing cells in the model 4 c. Logical values and whether or not the current constraints have been met (Information omitted) d. The arrays that are saved in the solver model and what the iterative Process is 1 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. 1 Item# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Description Silk Brocade Gown Alligator Skin Belt Black Cocktail Dress Cashmere Scarf Lavender Silk Blouse Lace Shawl Cashmere Blend Suit Plaid Blazer Black Velvet Gown CroppedTee Shirt Navy Blue Pant Suit Cotton Crisp Blouse Bikini & Wrap Khaki Trousers Black Trousers Black Wool Blazer Black Skirt Capri Khaki Trousers Selling Price $1,800.00 $300.00 $1,500.00 $100.00 $100.00 $25.00 $1,000.00 $367.00 $625.00 $50.00 $300.00 $60.00 $253.00 $100.00 $100.00 $175.00 $200.00 $100.00 Materials Cost $500.00 $98.00 $700.00 $10.00 $25.00 $15.00 $650.00 $125.00 $225.00 $20.00 $185.00 $30.00 $150.00 $35.00 $35.00 $75.00 $75.00 $40.00 Lead Time (Days) 60 25 35 25 20 15 30 30 0 15 30 25 35 20 20 25 25 15 Total Profit Per Recommendation Item Advertise $3,629,774 $34,115 Continue As Is $657,348 Continue As Is $7,264 Continue As Is $12,792 Continue As Is Discontinue ($17,774) Advertise $885,439 $91,604 Continue As Is Advertise $1,127,625 $2,400 Continue As Is $839 Continue As Is Discontinue ($736) $516 Continue As Is $14,150 Continue As Is $11,320 Continue As Is $21,307 Continue As Is $463,568 Continue As Is $2,022 Continue As Is $6,943,574 Advertise Continue As Is Discontinue 3 13 2 Order# Account# Quantity Item# Date Ordered 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 310 234 75 266 233 234 299 157 94 94 94 310 94 112 112 112 356 75 455 233 455 81 81 81 310 299 76 199 213 213 110 110 299 85 255 88 88 88 88 1250 500 144 2400 1300 1650 200 200 100 400 100 11200 600 350 225 199 498 160 120 1700 33 400 500 600 2200 115 12 44 860 100 155 45 152 500 500 24 100 200 400 1 15 10 9 16 7 5 3 11 9 5 17 9 2 14 6 7 18 10 1 13 9 6 10 7 6 2 8 3 4 9 10 1 8 7 6 12 9 14 3/1/2010 3/2/2010 3/2/2010 3/3/2010 3/3/2010 3/7/2010 3/6/2010 3/6/2010 3/6/2010 3/8/2010 3/9/2010 3/4/2010 3/22/2010 3/1/2010 3/8/2010 3/12/2010 3/16/2010 3/9/2010 3/5/2010 3/9/2010 3/31/2010 3/2/2010 3/8/2010 3/12/2010 3/9/2010 3/10/2010 3/4/2010 3/5/2010 3/9/2010 3/15/2010 3/4/2010 3/9/2010 3/12/2010 3/19/2010 3/21/2010 3/19/2010 3/19/2010 3/19/2010 3/19/2010 40 88 600 6 3/31/2010 $2.50 5.00% Date Delivered 5/20/2010 5/24/2010 5/2/2010 4/14/2010 5/27/2010 3/26/2010 5/22/2010 5/20/2010 5/22/2010 5/5/2010 4/5/2010 3/20/2010 4/8/2010 5/22/2010 4/15/2010 4/14/2010 4/3/2010 6/9/2010 5/19/2010 5/25/2010 4/9/2010 5/20/2010 5/29/2010 5/2/2010 4/10/2010 6/2/2010 4/19/2010 3/24/2010 4/3/2010 3/24/2010 4/2/2010 3/29/2010 4/18/2010 5/6/2010 4/20/2010 6/8/2010 4/25/2010 4/5/2010 5/12/2010 Selling Price Per Item $1,800.00 $100.00 $50.00 $625.00 $175.00 $1,000.00 $100.00 $1,500.00 $300.00 $625.00 $100.00 $200.00 $625.00 $300.00 $100.00 $25.00 $1,000.00 $100.00 $50.00 $1,800.00 $253.00 $625.00 $25.00 $50.00 $1,000.00 $25.00 $300.00 $367.00 $1,500.00 $100.00 $625.00 $50.00 $1,800.00 $367.00 $1,000.00 $25.00 $60.00 $625.00 $100.00 Materials Cost Shipping Cost Per Labor Cost per Per Item Item Item $500.00 $25.00 $100.00 $35.00 $2.50 $35.00 $20.00 $2.50 $20.00 $225.00 $11.25 $112.50 $75.00 $3.75 $75.00 $650.00 $32.50 $130.00 $25.00 $2.50 $25.00 $700.00 $35.00 $140.00 $185.00 $9.25 $92.50 $225.00 $11.25 $112.50 $25.00 $2.50 $25.00 $75.00 $3.75 $75.00 $225.00 $11.25 $112.50 $98.00 $4.90 $98.00 $35.00 $2.50 $35.00 $15.00 $2.50 $15.00 $650.00 $32.50 $130.00 $40.00 $2.50 $40.00 $20.00 $2.50 $20.00 $500.00 $25.00 $100.00 $150.00 $7.50 $75.00 $225.00 $11.25 $112.50 $15.00 $2.50 $15.00 $20.00 $2.50 $20.00 $650.00 $32.50 $130.00 $15.00 $2.50 $15.00 $98.00 $4.90 $98.00 $125.00 $6.25 $62.50 $700.00 $35.00 $140.00 $10.00 $2.50 $10.00 $225.00 $11.25 $112.50 $20.00 $2.50 $20.00 $500.00 $25.00 $100.00 $125.00 $6.25 $62.50 $650.00 $32.50 $130.00 $15.00 $2.50 $15.00 $30.00 $2.50 $30.00 $225.00 $11.25 $112.50 $35.00 $2.50 $35.00 5/17/2010 Average Total $25.00 $15.00 $2.50 $15.00 $487.43 $204.28 $10.79 $70.09 Overhead Costs Per Item $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 $4.86 Total Revenues Total Material Total Shipping Total Labor Costs per Order Costs Costs $2,250,000 $625,000 $31,250 $125,000 $50,000 $17,500 $1,250 $17,500 $7,200 $2,880 $360 $2,880 $1,500,000 $540,000 $27,000 $270,000 $227,500 $97,500 $4,875 $97,500 $1,650,000 $1,072,500 $53,625 $214,500 $20,000 $5,000 $500 $5,000 $300,000 $140,000 $7,000 $28,000 $30,000 $18,500 $925 $9,250 $250,000 $90,000 $4,500 $45,000 $10,000 $2,500 $250 $2,500 $2,240,000 $840,000 $42,000 $840,000 $375,000 $135,000 $6,750 $67,500 $105,000 $34,300 $1,715 $34,300 $22,500 $7,875 $563 $7,875 $4,975 $2,985 $498 $2,985 $498,000 $323,700 $16,185 $64,740 $16,000 $6,400 $400 $6,400 $6,000 $2,400 $300 $2,400 $3,060,000 $850,000 $42,500 $170,000 $8,349 $4,950 $248 $2,475 $250,000 $90,000 $4,500 $45,000 $12,500 $7,500 $1,250 $7,500 $30,000 $12,000 $1,500 $12,000 $2,200,000 $1,430,000 $71,500 $286,000 $2,875 $1,725 $288 $1,725 $3,600 $1,176 $59 $1,176 $16,148 $5,500 $275 $2,750 $1,290,000 $602,000 $30,100 $120,400 $10,000 $1,000 $250 $1,000 $96,875 $34,875 $1,744 $17,438 $2,250 $900 $113 $900 $273,600 $76,000 $3,800 $15,200 $183,500 $62,500 $3,125 $31,250 $500,000 $325,000 $16,250 $65,000 $600 $360 $60 $360 $6,000 $3,000 $250 $3,000 $125,000 $45,000 $2,250 $22,500 $40,000 $14,000 $1,000 $14,000 $4.86 $15,000 $9,000 $1,500 $9,000 $4.86 $442,212 $17,688,472 $188,513 $7,540,526 $9,563 $382,505 $66,800 $2,672,004 Total Overhead Total Profit Per Costs Order $6,075 $1,462,675 $2,430 $11,320 $700 $380 $11,664 $651,336 $6,318 $21,307 $8,019 $301,356 $972 $8,528 $972 $124,028 $486 $839 $1,944 $108,556 $486 $4,264 $54,432 $463,568 $2,916 $162,834 $1,701 $32,984 $1,094 $5,094 $967 -$2,460 $2,420 $90,955 $778 $2,022 $583 $317 $8,262 $1,989,238 $160 $516 $1,944 $108,556 $2,430 -$6,180 $2,916 $1,584 $10,692 $401,808 $559 -$1,421 $58 $1,131 $214 $7,409 $4,180 $533,320 $486 $7,264 $753 $42,065 $219 $119 $739 $177,861 $2,430 $84,195 $2,430 $91,320 $117 -$297 $486 -$736 $972 $54,278 $1,944 $9,056 Profit Margin Profit Category On Time? 65% 23% 5% 43% 9% 18% 43% 41% 3% 43% 43% 21% 43% 31% 23% -49% 18% 13% 5% 65% 6% 43% -49% 5% 18% -49% 31% 46% 41% 73% 43% 5% 65% 46% 18% -49% -12% 43% 23% High Low Low High Low Low High High Low High High Low High Low Low Loss Low Low Low High Low High Loss Low Low Loss Low High High High High Low High High Low Loss Loss High Low Late Late Late Late Late On Time Late Late Late Late Late On Time Late Late Late Late On Time Late Late Late On Time Late Late Late Late Late Late On Time On Time On Time Late Late On Time Late On Time Late Late Late Late $2,916 -$7,416 $3,747 $149,863 $173,589 $6,943,574 Loss -49% 39% Late Rebate $270 $30 $23 $375 $131 $0 $105 $1,800 $405 $938 $165 $0 $1,219 $630 $225 $60 $0 $270 $143 $5,400 $0 $2,063 $86 $180 $3,750 $98 $1,215 $0 $0 $0 $2,906 $240 $0 $1,872 $0 $135 $333 $3,563 $585 $150 $29,362 Total Rebate Scenario Summary Current Values: Original scenario1 scenario2 Changing Cells: $B$100 $2.50 $2.50 $6.00 $12.00 $B$101 5.00% 5.00% 9.00% 7.00% Result Cells: $N$44 $382,505 $382,505 $694,707 $658,682 $Q$44 $6,943,574 $6,943,574 $6,631,372 $6,667,397 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Goal Seek Result $550.82 Target Selling Price to achieve $100,000 increase in profits *By changing Selling Price of Plaid Blazer (Item # 8) scenario3 $0.00 15.00% $1,131,079 $6,195,001 0,000 increase in profits Loan Analysis Worksheet Loan Analysis Loan Amount Payment Period (Years) Payment Period (Months) Interest Rate (%) Monthly Payment Total Payment Total Interest $500,000.00 10 120 6.25% $5,614.00 $673,680.58 $173,680.58 Input/Changing Cells *Note: Delete if not required Choose appropriate working from the two loan analysis she Months Payment # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 6.00% $502,500.00 $251,876.56 $168,336.10 $126,566.40 $101,504.99 $84,797.73 $72,864.27 $63,914.43 $56,953.68 $51,385.29 $46,829.52 $43,033.21 $39,821.12 $37,068.04 $34,682.18 $32,594.68 $30,752.90 $29,115.87 $27,651.26 $26,333.23 $25,140.81 $24,056.90 $23,067.33 $22,160.31 $21,325.93 $20,555.81 $19,842.82 $19,180.83 $18,564.57 6.25% $502,604.17 $251,954.82 $168,405.78 $126,631.83 $101,567.91 $84,859.01 $72,924.40 $63,973.72 $57,012.34 $51,443.45 $46,887.30 $43,090.69 $39,878.35 $37,125.08 $34,739.06 $32,651.43 $30,809.54 $29,172.43 $27,707.76 $26,389.67 $25,197.23 $24,113.29 $23,123.70 $22,216.67 $21,382.30 $20,612.19 $19,899.21 $19,237.24 $18,621.00 6.50% $502,708.33 $252,033.08 $168,475.47 $126,697.28 $101,630.85 $84,920.31 $72,984.55 $64,033.04 $57,071.02 $51,501.65 $46,945.12 $43,148.21 $39,935.63 $37,182.16 $34,795.98 $32,708.23 $30,866.24 $29,229.05 $27,764.32 $26,446.19 $25,253.71 $24,169.75 $23,180.16 $22,273.13 $21,438.76 $20,668.66 $19,955.70 $19,293.75 $18,677.54 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 $17,989.46 $17,451.52 $16,947.27 $16,473.64 $16,027.93 $15,607.75 $15,210.97 $14,835.69 $14,480.22 $14,143.04 $13,822.76 $13,518.16 $13,228.11 $12,951.60 $12,687.70 $12,435.58 $12,194.47 $11,963.66 $11,742.51 $11,530.43 $11,326.88 $11,131.35 $10,943.37 $10,762.53 $10,588.43 $10,420.69 $10,258.99 $10,102.99 $9,952.41 $9,806.96 $9,666.40 $9,530.48 $9,398.98 $9,271.69 $9,148.40 $9,028.94 $8,913.14 $8,800.82 $8,691.83 $8,586.03 $8,483.29 $8,383.46 $8,286.44 $8,192.11 $8,100.35 $8,011.07 $18,045.92 $17,508.01 $17,003.80 $16,530.21 $16,084.54 $15,664.40 $15,267.67 $14,892.44 $14,537.03 $14,199.89 $13,879.67 $13,575.12 $13,285.13 $13,008.68 $12,744.85 $12,492.79 $12,251.74 $12,020.99 $11,799.91 $11,587.90 $11,384.41 $11,188.94 $11,001.04 $10,820.27 $10,646.23 $10,478.57 $10,316.93 $10,161.00 $10,010.49 $9,865.12 $9,724.63 $9,588.79 $9,457.36 $9,330.14 $9,206.93 $9,087.54 $8,971.81 $8,859.57 $8,750.66 $8,644.94 $8,542.27 $8,442.52 $8,345.58 $8,251.32 $8,159.64 $8,070.43 $18,102.49 $17,564.62 $17,060.44 $16,586.89 $16,141.27 $15,721.18 $15,324.50 $14,949.33 $14,593.96 $14,256.88 $13,936.72 $13,632.23 $13,342.30 $13,065.92 $12,802.15 $12,550.15 $12,309.17 $12,078.49 $11,857.48 $11,645.53 $11,442.11 $11,246.72 $11,058.89 $10,878.19 $10,704.22 $10,536.63 $10,375.07 $10,219.22 $10,068.78 $9,923.49 $9,783.07 $9,647.31 $9,515.95 $9,388.81 $9,265.68 $9,146.38 $9,030.72 $8,918.56 $8,809.72 $8,704.08 $8,601.49 $8,501.83 $8,404.96 $8,310.79 $8,219.19 $8,130.06 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 $7,924.16 $7,839.54 $7,757.11 $7,676.80 $7,598.52 $7,522.20 $7,447.76 $7,375.14 $7,304.28 $7,235.10 $7,167.56 $7,101.60 $7,037.16 $6,974.19 $6,912.64 $6,852.46 $6,793.62 $6,736.06 $6,679.75 $6,624.65 $6,570.72 $6,517.91 $6,466.21 $6,415.57 $6,365.97 $6,317.37 $6,269.74 $6,223.05 $6,177.28 $6,132.41 $6,088.40 $6,045.23 $6,002.87 $5,961.32 $5,920.54 $5,880.51 $5,841.21 $5,802.63 $5,764.74 $5,727.53 $5,690.98 $5,655.07 $5,619.78 $5,585.11 $5,551.03 $7,983.61 $7,899.06 $7,816.71 $7,736.48 $7,658.27 $7,582.03 $7,507.67 $7,435.14 $7,364.35 $7,295.26 $7,227.80 $7,161.91 $7,097.55 $7,034.66 $6,973.19 $6,913.09 $6,854.33 $6,796.85 $6,740.62 $6,685.60 $6,631.75 $6,579.03 $6,527.40 $6,476.85 $6,427.32 $6,378.80 $6,331.25 $6,284.65 $6,238.96 $6,194.17 $6,150.24 $6,107.15 $6,064.88 $6,023.40 $5,982.70 $5,942.76 $5,903.54 $5,865.04 $5,827.23 $5,790.10 $5,753.63 $5,717.80 $5,682.60 $5,648.00 $5,614.00 $8,043.32 $7,958.85 $7,876.59 $7,796.43 $7,718.31 $7,642.15 $7,567.88 $7,495.42 $7,424.72 $7,355.71 $7,288.33 $7,222.53 $7,158.25 $7,095.44 $7,034.05 $6,974.04 $6,915.36 $6,857.97 $6,801.83 $6,746.89 $6,693.12 $6,640.48 $6,588.94 $6,538.47 $6,489.03 $6,440.59 $6,393.13 $6,346.61 $6,301.00 $6,256.29 $6,212.45 $6,169.44 $6,127.26 $6,085.87 $6,045.25 $6,005.39 $5,966.26 $5,927.84 $5,890.12 $5,853.07 $5,816.69 $5,780.94 $5,745.82 $5,711.31 $5,677.40 Answer: 1. Monthly payments 2. Future value ($4,000 per month payment) 3. No of Payments required to pay off loan 4. Amount to borrow (For $4,000 monthly payment) * Note: Answer of Sr. # 4 can also be found by using goal-seek rom the two loan analysis sheets as required. Rate 6.75% $502,812.50 $252,111.35 $168,545.17 $126,762.74 $101,693.81 $84,981.63 $73,044.73 $64,092.38 $57,129.74 $51,559.89 $47,002.98 $43,205.77 $39,992.95 $37,239.29 $34,852.96 $32,765.09 $30,923.00 $29,285.74 $27,820.96 $26,502.78 $25,310.28 $24,226.30 $23,236.69 $22,329.66 $21,495.30 $20,725.22 $20,012.29 $19,350.36 $18,734.18 7.00% $502,916.67 $252,189.62 $168,614.88 $126,828.22 $101,756.79 $85,042.97 $73,104.93 $64,151.76 $57,188.49 $51,618.16 $47,060.88 $43,263.37 $40,050.32 $37,296.47 $34,910.00 $32,822.01 $30,979.83 $29,342.49 $27,877.66 $26,559.45 $25,366.91 $24,282.92 $23,293.31 $22,386.29 $21,551.94 $20,781.88 $20,068.97 $19,407.08 $18,790.93 7.25% $503,020.83 $252,267.90 $168,684.60 $126,893.71 $101,819.78 $85,104.33 $73,165.16 $64,211.16 $57,247.27 $51,676.47 $47,118.81 $43,321.02 $40,107.73 $37,353.71 $34,967.09 $32,878.98 $31,036.71 $29,399.31 $27,934.43 $26,616.18 $25,423.63 $24,339.63 $23,350.02 $22,443.00 $21,608.67 $20,838.63 $20,125.74 $19,463.89 $18,847.78 7.50% $503,125.00 $252,346.18 $168,754.33 $126,959.21 $101,882.79 $85,165.71 $73,225.41 $64,270.59 $57,306.09 $51,734.81 $47,176.79 $43,378.71 $40,165.20 $37,410.99 $35,024.23 $32,936.01 $31,093.66 $29,456.19 $27,991.26 $26,672.99 $25,480.41 $24,396.41 $23,406.80 $22,499.80 $21,665.48 $20,895.47 $20,182.62 $19,520.80 $18,904.73 $18,159.17 $17,621.34 $17,117.20 $16,643.70 $16,198.13 $15,778.09 $15,381.46 $15,006.34 $14,651.04 $14,314.02 $13,993.92 $13,689.49 $13,399.63 $13,123.31 $12,859.60 $12,607.68 $12,366.76 $12,136.16 $11,915.21 $11,703.34 $11,500.00 $11,304.68 $11,116.92 $10,936.30 $10,762.41 $10,594.90 $10,433.41 $10,277.64 $10,127.28 $9,982.06 $9,841.73 $9,706.04 $9,574.77 $9,447.71 $9,324.66 $9,205.44 $9,089.86 $8,977.78 $8,869.03 $8,763.47 $8,660.97 $8,561.39 $8,464.61 $8,370.51 $8,279.00 $8,189.96 $18,215.95 $17,678.16 $17,174.07 $16,700.62 $16,255.10 $15,835.12 $15,438.55 $15,063.49 $14,708.25 $14,371.29 $14,051.25 $13,746.90 $13,457.10 $13,180.85 $12,917.22 $12,665.36 $12,424.52 $12,193.99 $11,973.12 $11,761.33 $11,558.06 $11,362.82 $11,175.14 $10,994.59 $10,820.79 $10,653.35 $10,491.95 $10,336.26 $10,185.98 $10,040.85 $9,900.60 $9,764.99 $9,633.81 $9,506.83 $9,383.87 $9,264.73 $9,149.24 $9,037.24 $8,928.58 $8,823.11 $8,720.69 $8,621.20 $8,524.50 $8,430.50 $8,339.07 $8,250.12 $18,272.85 $17,735.10 $17,231.06 $16,757.66 $16,312.20 $15,892.27 $15,495.76 $15,120.77 $14,765.59 $14,428.70 $14,108.73 $13,804.45 $13,514.72 $13,238.54 $12,974.99 $12,723.21 $12,482.45 $12,251.99 $12,031.20 $11,819.48 $11,616.30 $11,421.14 $11,233.54 $11,053.08 $10,879.36 $10,712.01 $10,550.69 $10,395.08 $10,244.89 $10,099.84 $9,959.68 $9,824.16 $9,693.06 $9,566.18 $9,443.30 $9,324.25 $9,208.85 $9,096.94 $8,988.37 $8,882.99 $8,780.66 $8,681.25 $8,584.65 $8,490.74 $8,399.40 $8,310.54 $18,329.84 $17,792.15 $17,288.16 $16,814.82 $16,369.42 $15,949.55 $15,553.11 $15,178.18 $14,823.07 $14,486.25 $14,166.36 $13,862.14 $13,572.49 $13,296.39 $13,032.91 $12,781.22 $12,540.53 $12,310.16 $12,089.45 $11,877.82 $11,674.71 $11,479.64 $11,292.13 $11,111.75 $10,938.12 $10,770.85 $10,609.62 $10,454.10 $10,304.00 $10,159.05 $10,018.97 $9,883.55 $9,752.54 $9,625.74 $9,502.96 $9,384.00 $9,268.69 $9,156.88 $9,048.40 $8,943.11 $8,840.87 $8,741.56 $8,645.06 $8,551.24 $8,459.99 $8,371.23 $8,103.30 $8,018.92 $7,936.74 $7,856.67 $7,778.63 $7,702.56 $7,628.37 $7,556.00 $7,485.38 $7,416.46 $7,349.17 $7,283.45 $7,219.26 $7,156.54 $7,095.24 $7,035.31 $6,976.72 $6,919.41 $6,863.35 $6,808.50 $6,754.82 $6,702.27 $6,650.82 $6,600.43 $6,551.08 $6,502.73 $6,455.35 $6,408.92 $6,363.41 $6,318.78 $6,275.03 $6,232.11 $6,190.01 $6,148.71 $6,108.18 $6,068.41 $6,029.36 $5,991.03 $5,953.40 $5,916.44 $5,880.14 $5,844.49 $5,809.45 $5,775.03 $5,741.21 $8,163.54 $8,079.25 $7,997.16 $7,917.18 $7,839.24 $7,763.25 $7,689.15 $7,616.87 $7,546.34 $7,477.50 $7,410.30 $7,344.68 $7,280.57 $7,217.94 $7,156.73 $7,096.90 $7,038.40 $6,981.18 $6,925.21 $6,870.45 $6,816.86 $6,764.40 $6,713.04 $6,662.74 $6,613.48 $6,565.22 $6,517.94 $6,471.60 $6,426.17 $6,381.64 $6,337.97 $6,295.15 $6,253.14 $6,211.93 $6,171.49 $6,131.81 $6,092.85 $6,054.62 $6,017.07 $5,980.20 $5,944.00 $5,908.43 $5,873.49 $5,839.16 $5,805.42 $8,224.06 $8,139.86 $8,057.86 $7,977.97 $7,900.12 $7,824.22 $7,750.22 $7,678.03 $7,607.59 $7,538.85 $7,471.74 $7,406.21 $7,342.20 $7,279.66 $7,218.54 $7,158.80 $7,100.39 $7,043.27 $6,987.40 $6,932.73 $6,879.23 $6,826.87 $6,775.60 $6,725.40 $6,676.23 $6,628.06 $6,580.87 $6,534.62 $6,489.29 $6,444.86 $6,401.28 $6,358.55 $6,316.64 $6,275.52 $6,235.18 $6,195.59 $6,156.73 $6,118.59 $6,081.14 $6,044.36 $6,008.25 $5,972.78 $5,937.93 $5,903.69 $5,870.05 $8,284.84 $8,200.74 $8,118.83 $8,039.04 $7,961.28 $7,885.48 $7,811.57 $7,739.48 $7,669.14 $7,600.49 $7,533.48 $7,468.04 $7,404.13 $7,341.69 $7,280.67 $7,221.02 $7,162.71 $7,105.69 $7,049.91 $6,995.34 $6,941.94 $6,889.67 $6,838.50 $6,788.39 $6,739.32 $6,691.25 $6,644.16 $6,598.01 $6,552.78 $6,508.43 $6,464.96 $6,422.32 $6,380.51 $6,339.49 $6,299.24 $6,259.75 $6,220.99 $6,182.94 $6,145.59 $6,108.91 $6,072.90 $6,037.52 $6,002.77 $5,968.63 $5,935.09 -$5,614.00 -$963,745.52 88 -$356,251.92 ng goal-seek 7.75% $503,229.17 $252,424.47 $168,824.06 $127,024.73 $101,945.82 $85,227.12 $73,285.69 $64,330.05 $57,364.93 $51,793.19 $47,234.81 $43,436.44 $40,222.70 $37,468.32 $35,081.42 $32,993.10 $31,150.67 $29,513.14 $28,048.17 $26,729.86 $25,537.28 $24,453.27 $23,463.67 $22,556.68 $21,722.39 $20,952.40 $20,239.59 $19,577.81 $18,961.79 $18,386.95 $17,849.31 $17,345.38 $16,872.10 $16,426.75 $16,006.96 $15,610.58 $15,235.72 $14,880.68 $14,543.94 $14,224.12 $13,919.99 $13,630.42 $13,354.39 $13,091.00 $12,839.38 $12,598.78 $12,368.49 $12,147.87 $11,936.32 $11,733.31 $11,538.32 $11,350.90 $11,170.61 $10,997.07 $10,829.90 $10,668.76 $10,513.33 $10,363.32 $10,218.46 $10,078.48 $9,943.15 $9,812.23 $9,685.53 $9,562.84 $9,443.98 $9,328.77 $9,217.05 $9,108.67 $9,003.47 $8,901.34 $8,802.12 $8,705.71 $8,611.99 $8,520.85 $8,432.18 $8,345.89 $8,261.88 $8,180.07 $8,100.38 $8,022.72 $7,947.02 $7,873.21 $7,801.22 $7,730.98 $7,662.43 $7,595.52 $7,530.18 $7,466.37 $7,404.02 $7,343.10 $7,283.56 $7,225.35 $7,168.42 $7,112.74 $7,058.27 $7,004.97 $6,952.80 $6,901.73 $6,851.73 $6,802.76 $6,754.79 $6,707.80 $6,661.75 $6,616.61 $6,572.37 $6,529.00 $6,486.46 $6,444.75 $6,403.83 $6,363.68 $6,324.29 $6,285.63 $6,247.68 $6,210.43 $6,173.86 $6,137.94 $6,102.66 $6,068.02 $6,033.98 $6,000.53 Loan Analysis Work-Sheet Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Balance B/Fwd $500,000.00 $496,990.16 $493,964.65 $490,923.38 $487,866.26 $484,793.23 $481,704.19 $478,599.06 $475,477.76 $472,340.20 $469,186.30 $466,015.97 $462,829.14 $459,625.70 $456,405.58 $453,168.69 $449,914.93 $446,644.24 $443,356.50 $440,051.65 $436,729.58 $433,390.21 $430,033.44 $426,659.19 $423,267.37 $419,857.89 $416,430.64 $412,985.55 $409,522.51 $406,041.43 $402,542.23 $399,024.80 $395,489.05 $391,934.88 $388,362.20 $384,770.92 $381,160.93 $377,532.14 $373,884.44 $370,217.75 $366,531.97 $362,826.98 $359,102.70 Interest $2,604.17 $2,588.49 $2,572.73 $2,556.89 $2,540.97 $2,524.96 $2,508.88 $2,492.70 $2,476.45 $2,460.11 $2,443.68 $2,427.17 $2,410.57 $2,393.88 $2,377.11 $2,360.25 $2,343.31 $2,326.27 $2,309.15 $2,291.94 $2,274.63 $2,257.24 $2,239.76 $2,222.18 $2,204.52 $2,186.76 $2,168.91 $2,150.97 $2,132.93 $2,114.80 $2,096.57 $2,078.25 $2,059.84 $2,041.33 $2,022.72 $2,004.02 $1,985.21 $1,966.31 $1,947.31 $1,928.22 $1,909.02 $1,889.72 $1,870.33 Payments $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 Principal $3,009.84 $3,025.51 $3,041.27 $3,057.11 $3,073.03 $3,089.04 $3,105.13 $3,121.30 $3,137.56 $3,153.90 $3,170.33 $3,186.84 $3,203.44 $3,220.12 $3,236.89 $3,253.75 $3,270.70 $3,287.73 $3,304.86 $3,322.07 $3,339.37 $3,356.76 $3,374.25 $3,391.82 $3,409.49 $3,427.25 $3,445.10 $3,463.04 $3,481.08 $3,499.21 $3,517.43 $3,535.75 $3,554.17 $3,572.68 $3,591.29 $3,609.99 $3,628.79 $3,647.69 $3,666.69 $3,685.79 $3,704.98 $3,724.28 $3,743.68 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 $355,359.02 $351,595.85 $347,813.07 $344,010.59 $340,188.31 $336,346.12 $332,483.92 $328,601.60 $324,699.06 $320,776.20 $316,832.90 $312,869.07 $308,884.59 $304,879.36 $300,853.27 $296,806.20 $292,738.07 $288,648.74 $284,538.11 $280,406.08 $276,252.52 $272,077.33 $267,880.40 $263,661.60 $259,420.83 $255,157.98 $250,872.92 $246,565.55 $242,235.74 $237,883.38 $233,508.35 $229,110.53 $224,689.81 $220,246.07 $215,779.18 $211,289.02 $206,775.48 $202,238.43 $197,677.75 $193,093.32 $188,485.01 $183,852.70 $179,196.26 $174,515.57 $169,810.50 $165,080.92 $1,850.83 $1,831.23 $1,811.53 $1,791.72 $1,771.81 $1,751.80 $1,731.69 $1,711.47 $1,691.14 $1,670.71 $1,650.17 $1,629.53 $1,608.77 $1,587.91 $1,566.94 $1,545.87 $1,524.68 $1,503.38 $1,481.97 $1,460.45 $1,438.82 $1,417.07 $1,395.21 $1,373.24 $1,351.15 $1,328.95 $1,306.63 $1,284.20 $1,261.64 $1,238.98 $1,216.19 $1,193.28 $1,170.26 $1,147.11 $1,123.85 $1,100.46 $1,076.96 $1,053.33 $1,029.57 $1,005.69 $981.69 $957.57 $933.31 $908.94 $884.43 $859.80 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $3,763.18 $3,782.78 $3,802.48 $3,822.28 $3,842.19 $3,862.20 $3,882.32 $3,902.54 $3,922.86 $3,943.30 $3,963.83 $3,984.48 $4,005.23 $4,026.09 $4,047.06 $4,068.14 $4,089.33 $4,110.63 $4,132.04 $4,153.56 $4,175.19 $4,196.94 $4,218.79 $4,240.77 $4,262.85 $4,285.06 $4,307.38 $4,329.81 $4,352.36 $4,375.03 $4,397.82 $4,420.72 $4,443.75 $4,466.89 $4,490.15 $4,513.54 $4,537.05 $4,560.68 $4,584.43 $4,608.31 $4,632.31 $4,656.44 $4,680.69 $4,705.07 $4,729.58 $4,754.21 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 Total $160,326.71 $155,547.74 $150,743.88 $145,915.00 $141,060.97 $136,181.66 $131,276.93 $126,346.66 $121,390.71 $116,408.95 $111,401.24 $106,367.45 $101,307.45 $96,221.08 $91,108.23 $85,968.75 $80,802.50 $75,609.34 $70,389.13 $65,141.74 $59,867.01 $54,564.81 $49,235.00 $43,877.43 $38,491.95 $33,078.43 $27,636.71 $22,166.64 $16,668.09 $11,140.90 $5,584.92 $835.03 $810.14 $785.12 $759.97 $734.69 $709.28 $683.73 $658.06 $632.24 $606.30 $580.21 $554.00 $527.64 $501.15 $474.52 $447.75 $420.85 $393.80 $366.61 $339.28 $311.81 $284.19 $256.43 $228.53 $200.48 $172.28 $143.94 $115.45 $86.81 $58.03 $29.09 $173,680.58 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $5,614.00 $673,680.58 $4,778.97 $4,803.86 $4,828.88 $4,854.03 $4,879.31 $4,904.73 $4,930.27 $4,955.95 $4,981.76 $5,007.71 $5,033.79 $5,060.01 $5,086.36 $5,112.85 $5,139.48 $5,166.25 $5,193.16 $5,220.21 $5,247.39 $5,274.72 $5,302.20 $5,329.81 $5,357.57 $5,385.48 $5,413.53 $5,441.72 $5,470.06 $5,498.55 $5,527.19 $5,555.98 $5,584.92 $500,000.00 Balance C/Fwd $496,990.16 $493,964.65 $490,923.38 $487,866.26 $484,793.23 $481,704.19 $478,599.06 $475,477.76 $472,340.20 $469,186.30 $466,015.97 $462,829.14 $459,625.70 $456,405.58 $453,168.69 $449,914.93 $446,644.24 $443,356.50 $440,051.65 $436,729.58 $433,390.21 $430,033.44 $426,659.19 $423,267.37 $419,857.89 $416,430.64 $412,985.55 $409,522.51 $406,041.43 $402,542.23 $399,024.80 $395,489.05 $391,934.88 $388,362.20 $384,770.92 $381,160.93 $377,532.14 $373,884.44 $370,217.75 $366,531.97 $362,826.98 $359,102.70 $355,359.02 Loan Details Loan Amount Repayment period (Years) Cost of Equipment Interest Rate (Prime Rate Plus 3%) Salvage Value of equipment $500,000.00 10 $125,000.00 6.25% $75,000.00 Answer: 1. Monthly payments 2. Future value ($4,000 per month payment) 3. No of Payments required to pay off loan 4. Amount to borrow (For $4,000 monthly payment) * Note: This working is optional. Delete if not required. Choose appropriate working from the two loan analysis sheets as required. $351,595.85 $347,813.07 $344,010.59 $340,188.31 $336,346.12 $332,483.92 $328,601.60 $324,699.06 $320,776.20 $316,832.90 $312,869.07 $308,884.59 $304,879.36 $300,853.27 $296,806.20 $292,738.07 $288,648.74 $284,538.11 $280,406.08 $276,252.52 $272,077.33 $267,880.40 $263,661.60 $259,420.83 $255,157.98 $250,872.92 $246,565.55 $242,235.74 $237,883.38 $233,508.35 $229,110.53 $224,689.81 $220,246.07 $215,779.18 $211,289.02 $206,775.48 $202,238.43 $197,677.75 $193,093.32 $188,485.01 $183,852.70 $179,196.26 $174,515.57 $169,810.50 $165,080.92 $160,326.71 $155,547.74 $150,743.88 $145,915.00 $141,060.97 $136,181.66 $131,276.93 $126,346.66 $121,390.71 $116,408.95 $111,401.24 $106,367.45 $101,307.45 $96,221.08 $91,108.23 $85,968.75 $80,802.50 $75,609.34 $70,389.13 $65,141.74 $59,867.01 $54,564.81 $49,235.00 $43,877.43 $38,491.95 $33,078.43 $27,636.71 $22,166.64 $16,668.09 $11,140.90 $5,584.92 $0.00 -$5,614.00 -$963,745.52 88 -$356,251.92 Depreciation Work-Sheet Depreciation Analysis Cost of Asset Depreciation Period (Years) Depreciation Period (Months) Salvage Value Depreciable Amount Yearly Depreciation Monthly Depreciation Total Depreciation $125,000.00 6 72 Input/Changing Cells $75,000.00 $50,000.00 $8,333.33 $694.44 $50,000.00 Equipment and Fixtures Months 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Balance B/Fwd (Depreciable Amount $ 50,000.00 49,305.56 48,611.11 47,916.67 47,222.22 46,527.78 45,833.33 45,138.89 44,444.44 43,750.00 43,055.56 42,361.11 41,666.67 40,972.22 40,277.78 39,583.33 38,888.89 38,194.44 37,500.00 36,805.56 36,111.11 35,416.67 34,722.22 34,027.78 33,333.33 32,638.89 31,944.44 Depreciation Expense Accumulated Depreciation $ $ 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 1,388.89 2,083.33 2,777.78 3,472.22 4,166.67 4,861.11 5,555.56 6,250.00 6,944.44 7,638.89 8,333.33 9,027.78 9,722.22 10,416.67 11,111.11 11,805.56 12,500.00 13,194.44 13,888.89 14,583.33 15,277.78 15,972.22 16,666.67 17,361.11 18,055.56 18,750.00 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 31,250.00 30,555.56 29,861.11 29,166.67 28,472.22 27,777.78 27,083.33 26,388.89 25,694.44 25,000.00 24,305.56 23,611.11 22,916.67 22,222.22 21,527.78 20,833.33 20,138.89 19,444.44 18,750.00 18,055.56 17,361.11 16,666.67 15,972.22 15,277.78 14,583.33 13,888.89 13,194.44 12,500.00 11,805.56 11,111.11 10,416.67 9,722.22 9,027.78 8,333.33 7,638.89 6,944.44 6,250.00 5,555.56 4,861.11 4,166.67 3,472.22 2,777.78 2,083.33 1,388.89 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 694.44 19,444.44 20,138.89 20,833.33 21,527.78 22,222.22 22,916.67 23,611.11 24,305.56 25,000.00 25,694.44 26,388.89 27,083.33 27,777.78 28,472.22 29,166.67 29,861.11 30,555.56 31,250.00 31,944.44 32,638.89 33,333.33 34,027.78 34,722.22 35,416.67 36,111.11 36,805.56 37,500.00 38,194.44 38,888.89 39,583.33 40,277.78 40,972.22 41,666.67 42,361.11 43,055.56 43,750.00 44,444.44 45,138.89 45,833.33 46,527.78 47,222.22 47,916.67 48,611.11 49,305.56 50,000.00 res Balance C/Fwd (Depreciable Amount $ 49,305.56 48,611.11 47,916.67 47,222.22 46,527.78 45,833.33 45,138.89 44,444.44 43,750.00 43,055.56 42,361.11 41,666.67 40,972.22 40,277.78 39,583.33 38,888.89 38,194.44 37,500.00 36,805.56 36,111.11 35,416.67 34,722.22 34,027.78 33,333.33 32,638.89 31,944.44 31,250.00 - 30,555.56 29,861.11 29,166.67 28,472.22 27,777.78 27,083.33 26,388.89 25,694.44 25,000.00 24,305.56 23,611.11 22,916.67 22,222.22 21,527.78 20,833.33 20,138.89 19,444.44 18,750.00 18,055.56 17,361.11 16,666.67 15,972.22 15,277.78 14,583.33 13,888.89 13,194.44 12,500.00 11,805.56 11,111.11 10,416.67 9,722.22 9,027.78 8,333.33 7,638.89 6,944.44 6,250.00 5,555.56 4,861.11 4,166.67 3,472.22 2,777.78 2,083.33 1,388.89 694.44 0.00 Answer: (A) Analysis (i) Do any of these scenarios seem feasible given your estimated monthly net income? Particulars Scenario 1 Current net income per month Add back: Depreciation Per Month Less: Loan Repayment (Per Month) Net Cash Flow (Per Month) Net Cash Flow (Per Year) Initial Investment Present value factor (6.25%) Present value Net Present value - 500,000.00 1 - 500,000.00 Scenario 2 9,903.00 694.44 10,597.44 5,614.00 4,983.44 59,801.28 ### 694.44 10,597.44 4,000.00 6,597.44 79,169.33 11.12 665,053.82 165,053.82 8.73 690,856.08 190,856.08 Scneario 1, 2, and 3 are feasible. Scneario 4 yield negative NPV and hence not financially worthwhile. (ii) Determine how depreciation affects your net income and cash flow? Depreciation is a non-cash expense and therefore excluded from the calculation of net cash flows. Depreciation can only be ignored in DCF analysis in the absence of tax capital allowances. When deprecitation is allowable for taxation, tax saving (cash inflow) is recorded by multiplying the amount allow In the determination of net income, depreciation is deducted, thereby reducing overall net profit figure. In the determination of cash flows, depreciation is added back into the accounting profits so as to reverse the effec (iii) What are some other considerations to take into account if you wish to own your facility? How Might that Following factors may be considered before opting in for the alternative of adding another facility. (a) Present value of incremental cash inflows and outflows arising as a consequence of opening a second distribut (b) The market competitiveness and prices of competitor products. ( c ) The product life cycle and economies of scale that could be achieved by increasing capacity. (d) Consumption trends, geographical traits and other ancillary economic, social and political factors. (B) Interpreting solver models (i) The current value of the objective cell 9,903.00 (ii) Number of changing cells in the model 4 (ii) Logical values and whether or not the current constraints have been met (Information omitted) (iii) The arrays that are saved in the solver model and what the iterative Process is (info. Required) ( c ) Please define the following terms regarding Solver Reports and how they may be used in analyzing your b (a) Binding and non-binding constraints Binding constraints represent the factors which limit the possibility of achieve desired result. Non-binding constraints does not restrict the possiblity of achieving desired result. They shows the conditions that (b) Slack Constraints with a slack value shows that the condition underlying a particular decision variable is satisfied with equality . Scenario 3 9,903.00 694.44 10,597.44 4,500.00 6,097.44 73,169.33 Scenario 4 9,903.00 694.44 10,597.44 8,000.00 2,597.44 31,169.33 *Note: 1. Net income to be confirmed 2. Assumed $9,903 net income. 3. Some information seems to be missing for solver model. 4. If current net income of 6943574 is taken then all scnearios Annuity factor for 06 years as investment will be w 8.73 8.73 638,498.22 271,993.24 138,498.22 - 228,006.76 ally worthwhile. cash flows. multiplying the amount allowable for capital allowances with the applicable corporate tax rate . net profit figure. fits so as to reverse the effect of non-cash items. ur facility? How Might that affect your analysis? other facility. f opening a second distribution facility. The option is financially viable only if incremental cash inflows outweigh incrementa g capacity. political factors. tion omitted) nfo. Required) be used in analyzing your business. ey shows the conditions that are fullfiled and are no more a limiting factor. able is satisfied with equality . e missing for solver model. 574 is taken then all scnearios are feasible 6 years as investment will be written off in 06 years. h inflows outweigh incremental cash outflows. Order# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Account# Quantity Item# Date Ordered Date Delivered 310 1250 1 3/1/2010 5/20/2010 234 500 15 3/2/2010 5/24/2010 75 144 10 3/2/2010 5/2/2010 266 2400 9 3/3/2010 4/14/2010 233 1300 16 3/3/2010 5/27/2010 234 1650 7 3/7/2010 3/26/2010 299 200 5 3/6/2010 5/22/2010 157 200 3 3/6/2010 5/20/2010 94 100 11 3/6/2010 5/22/2010 94 400 9 3/8/2010 5/5/2010 94 100 5 3/9/2010 4/5/2010 310 11200 17 3/4/2010 3/20/2010 94 600 9 3/22/2010 4/8/2010 112 350 2 3/1/2010 5/22/2010 112 225 14 3/8/2010 4/15/2010 112 199 6 3/12/2010 4/14/2010 356 498 7 3/16/2010 4/3/2010 75 160 18 3/9/2010 6/9/2010 455 120 10 3/5/2010 5/19/2010 233 1700 1 3/9/2010 5/25/2010 455 33 13 3/31/2010 4/9/2010 81 400 9 3/2/2010 5/20/2010 81 500 6 3/8/2010 5/29/2010 81 600 10 3/12/2010 5/2/2010 310 2200 7 3/9/2010 4/10/2010 299 115 6 3/10/2010 6/2/2010 76 12 2 3/4/2010 4/19/2010 199 44 8 3/5/2010 3/24/2010 213 860 3 3/9/2010 4/3/2010 213 100 4 3/15/2010 3/24/2010 110 155 9 3/4/2010 4/2/2010 110 45 10 3/9/2010 3/29/2010 299 152 1 3/12/2010 4/18/2010 Material Shipping Selling Price Costs per Costs per per Item Item Item 1800 500 $ 25.00 100 35 $ 2.50 50 20 $ 2.50 625 225 $ 11.25 175 75 $ 3.75 1000 650 $ 32.50 100 25 $ 2.50 1500 700 $ 35.00 300 185 $ 9.25 625 225 $ 11.25 100 25 $ 2.50 200 75 $ 3.75 625 225 $ 11.25 300 98 $ 4.90 100 35 $ 2.50 25 15 $ 2.50 1000 650 $ 32.50 100 40 $ 2.50 50 20 $ 2.50 1800 500 $ 25.00 253 150 $ 7.50 625 225 $ 11.25 25 15 $ 2.50 50 20 $ 2.50 1000 650 $ 32.50 25 15 $ 2.50 300 98 $ 4.90 367 125 $ 6.25 1500 700 $ 35.00 100 10 $ 2.50 625 225 $ 11.25 50 20 $ 2.50 1800 500 $ 25.00 Labor Costs per Item $ 100.00 $ 35.00 $ 20.00 $ 112.50 $ 75.00 $ 130.00 $ 25.00 $ 140.00 $ 92.50 $ 112.50 $ 25.00 $ 75.00 $ 112.50 $ 98.00 $ 35.00 $ 15.00 $ 130.00 $ 40.00 $ 20.00 $ 100.00 $ 75.00 $ 112.50 $ 15.00 $ 20.00 $ 130.00 $ 15.00 $ 98.00 $ 62.50 $ 140.00 $ 10.00 $ 112.50 $ 20.00 $ 100.00 Overhead Costs per Item $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 $ 4.86 34 35 36 37 38 39 40 Average Total 85 255 88 88 88 88 88 500 500 24 100 200 400 600 8 7 6 12 9 14 6 3/19/2010 3/21/2010 3/19/2010 3/19/2010 3/19/2010 3/19/2010 3/31/2010 5/6/2010 4/20/2010 6/8/2010 4/25/2010 4/5/2010 5/12/2010 5/17/2010 367 1000 25 60 625 100 25 125 650 15 30 225 35 15 487.425 204.275 $ $ $ $ $ $ $ 6.25 32.50 2.50 2.50 11.25 2.50 2.50 10.78875 $ 62.50 $ $ 130.00 $ $ 15.00 $ $ 30.00 $ $ 112.50 $ $ 35.00 $ $ 15.00 $ 70.0875 4.86 4.86 4.86 4.86 4.86 4.86 4.86 4.86 Total Revenues per Order $ 2,250,000 $ 50,000 $ 7,200 $ 1,500,000 $ 227,500 $ 1,650,000 $ 20,000 $ 300,000 $ 30,000 $ 250,000 $ 10,000 $ 2,240,000 $ 375,000 $ 105,000 $ 22,500 $ 4,975 $ 498,000 $ 16,000 $ 6,000 $ 3,060,000 $ 8,349 $ 250,000 $ 12,500 $ 30,000 $ 2,200,000 $ 2,875 $ 3,600 $ 16,148 $ 1,290,000 $ 10,000 $ 96,875 $ 2,250 $ 273,600 Total Material Costs $ 625,000 $ 17,500 $ 2,880 $ 540,000 $ 97,500 $ 1,072,500 $ 5,000 $ 140,000 $ 18,500 $ 90,000 $ 2,500 $ 840,000 $ 135,000 $ 34,300 $ 7,875 $ 2,985 $ 323,700 $ 6,400 $ 2,400 $ 850,000 $ 4,950 $ 90,000 $ 7,500 $ 12,000 $ 1,430,000 $ 1,725 $ 1,176 $ 5,500 $ 602,000 $ 1,000 $ 34,875 $ 900 $ 76,000 Total Shipping Costs $ 31,250 $ 1,250 $ 360 $ 27,000 $ 4,875 $ 53,625 $ 500 $ 7,000 $ 925 $ 4,500 $ 250 $ 42,000 $ 6,750 $ 1,715 $ 563 $ 498 $ 16,185 $ 400 $ 300 $ 42,500 $ 248 $ 4,500 $ 1,250 $ 1,500 $ 71,500 $ 288 $ 59 $ 275 $ 30,100 $ 250 $ 1,744 $ 113 $ 3,800 Total Labor Costs $ 125,000 $ 17,500 $ 2,880 $ 270,000 $ 97,500 $ 214,500 $ 5,000 $ 28,000 $ 9,250 $ 45,000 $ 2,500 $ 840,000 $ 67,500 $ 34,300 $ 7,875 $ 2,985 $ 64,740 $ 6,400 $ 2,400 $ 170,000 $ 2,475 $ 45,000 $ 7,500 $ 12,000 $ 286,000 $ 1,725 $ 1,176 $ 2,750 $ 120,400 $ 1,000 $ 17,438 $ 900 $ 15,200 Total Overhead Costs $ 6,075 $ 2,430 $ 700 $ 11,664 $ 6,318 $ 8,019 $ 972 $ 972 $ 486 $ 1,944 $ 486 $ 54,432 $ 2,916 $ 1,701 $ 1,094 $ 967 $ 2,420 $ 778 $ 583 $ 8,262 $ 160 $ 1,944 $ 2,430 $ 2,916 $ 10,692 $ 559 $ 58 $ 214 $ 4,180 $ 486 $ 753 $ 219 $ 739 Total Profit per Profit Order Margin $ 1,462,675 65.0% $ 11,320 22.6% $ 380 5.3% $ 651,336 43.4% $ 21,307 9.4% $ 301,356 18.3% $ 8,528 42.6% $ 124,028 41.3% $ 839 2.8% $ 108,556 43.4% $ 4,264 42.6% $ 463,568 20.7% $ 162,834 43.4% $ 32,984 31.4% $ 5,094 22.6% $ (2,460) -49.4% $ 90,955 18.3% $ 2,022 12.6% $ 317 5.3% $ 1,989,238 65.0% $ 516 6.2% $ 108,556 43.4% $ (6,180) -49.4% $ 1,584 5.3% $ 401,808 18.3% $ (1,421) -49.4% $ 1,131 31.4% $ 7,409 45.9% $ 533,320 41.3% $ 7,264 72.6% $ 42,065 43.4% $ 119 5.3% $ 177,861 65.0% Profit Category High Low Low High Low Low High High Low High High Low High Low Low Loss Low Low Low High Low High Loss Low Low Loss Low High High High High Low High On Time? FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE TRUE FALSE FALSE TRUE Rebate $ 337,500 $ 7,500 $ 1,080 $ 225,000 $ 34,125 $ $ 3,000 $ 45,000 $ 4,500 $ 37,500 $ 1,500 $ $ 56,250 $ 15,750 $ 3,375 $ 746 $ $ 2,400 $ 900 $ 459,000 $ $ 37,500 $ 1,875 $ 4,500 $ 330,000 $ 431 $ 540 $ $ $ $ 14,531 $ 338 $ - $ $ $ $ $ $ $ 183,500 500,000 600 6,000 125,000 40,000 15,000 $ $ $ $ $ $ $ $ 442211.8 17,688,472 $ 62,500 325,000 360 3,000 45,000 14,000 9,000 $ $ $ $ $ $ $ 188513.15 7,540,526 $ 3,125 16,250 60 250 2,250 1,000 1,500 $ $ $ $ $ $ $ 31,250 65,000 360 3,000 22,500 14,000 9,000 $ $ $ $ $ $ $ 2,430 2,430 117 486 972 1,944 2,916 $ $ $ $ $ $ $ 84,195 91,320 (297) (736) 54,278 9,056 (7,416) 9562.63 66800.09 3746.57 $ 173,589.36 382,505 $ 2,672,004 $ 149,863 $ 6,943,574 45.9% 18.3% -49.4% -12.3% 43.4% 22.6% -49.4% High Low Loss Loss High Low Loss FALSE TRUE FALSE FALSE FALSE FALSE FALSE $ $ $ $ $ $ $ 27,525 90 900 18,750 6,000 2,250 $ 1,680,356 39% Item# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Description Silk Brocade Gown Alligator Skin Belt Black Cocktail Dress Cashmere Scarf Lavender Silk Blouse Lace Shawl Cashmere Blend Suit Plaid Blazer Black Velvet Gown CroppedTee Shirt Navy Blue Pant Suit Cotton Crisp Blouse Bikini & Wrap Khaki Trousers Black Trousers Black Wool Blazer Black Skirt Capri Khaki Trousers Selling Price Materials Cost $1,800.00 $500.00 $300.00 $98.00 $1,500.00 $700.00 $100.00 $10.00 $100.00 $25.00 $25.00 $15.00 $1,000.00 $650.00 $367.00 $125.00 $625.00 $225.00 $50.00 $20.00 $300.00 $185.00 $60.00 $30.00 $253.00 $150.00 $100.00 $35.00 $100.00 $35.00 $175.00 $75.00 $200.00 $75.00 $100.00 $40.00 Lead Time 60 25 35 25 20 15 30 30 0 15 30 25 35 20 20 25 25 15 Total Profit per Item Recommendation $ 3,629,774 Advertise $ 34,115 Continue As Is $ 657,348 Continue As Is $ 7,264 Continue As Is $ 12,792 Continue As Is $ (17,774) Discontinue $ 885,439 Advertise $ 91,604 Continue As Is $ 1,127,625 Advertise $ 2,400 Continue As Is $ 839 Continue As Is $ (736) Discontinue $ 516 Continue As Is $ 14,150 Continue As Is $ 11,320 Continue As Is $ 21,307 Continue As Is $ 463,568 Continue As Is $ 2,022 Continue As Is $ 6,943,574 Discontinue Continue As Is Advertise 2 13 3