Create a “What-If” Analysis displaying the following:
• What is the effect on total cost of the loan when interest rate changes to 5.3%, 5.7%, 6.5% and 7.3%.
• What is the effect on Monthly Payment when interest rate changes to 5%, 5.5%, 6%, 6.5%, 7%, 7.5% and 8%; while the amount of the loan changes to: $200,000; $250,000; $300,000 AND $350,000?
• What is the effect on Monthly Payment and Total Cost of the loan when interest rate changes to 5.3%, 5.7%, 6.5%, 7.3%?
• Use Goal Seek to find out how much should the interest rate be if you want to keep the loan amount in $250,000 for 120 months, but you want to pay only $2,500?
2. Go to the Worksheet “Tax Analysis”
NAME B1 as “Tax”, name B2 as “OrderTotal”.
Use “Create from Selection” to name all columns in the table.
Compute “TaxPerUnit” as UnitPrice*Tax. (NOTICE THAT IF YOU USE THE CELL NAME, YOU DON’T HAVE TO PRESS F4 TO CREATE AN ABSOLUTE CELL IN A FORMULA!)
Compute the Cost for each part=(UnitPrice+TaxPerUnit)*UnitsOrdered.
And finally, compute the order total as the SUM(Cost).
We now want to analyze the results of changing values in the cell Tax and Unit Price as SPF 15 gel, Yan Can Gel and Yan Can Masque; and see the impact on Order Total and cost for those products.
Since you will use Unit Price of the corresponding products, please name cells D5, D13, D15, F5, F13 and F15 WITH descriptive names.
Now, create the following scenarios and generate the report (it is REALLY important to name all cells before creating the scenarios):
a. Scenario: “5 Low Price”: Tax=5%, UnitPriceSPF15Gel=11.2, UnitPriceYanCanGel=4.75, UnitPriceYanCanMasque=5.81.
b. Scenario: “5 High Price”: Tax=5%, UnitPriceSPF15Gel=12.25, UnitPriceYanCanGel=5.75, UnitPriceYanCanMasque=6.31.
1. Click on PURCHASE ORDER worksheet.
Use solver to find the combination of units sold for each product that will result in the maximum profit.
a. Create a formula in B1 using SUMPRODUCT(B5:B12,F5:F12)-SUMPRODUCT(C5:C12,F5:F12). This formula will first obtain the product for each row, and then, add all rows. Make sure you study this formula! If you name the columns as “Retail” for cells B5:B12, “UnitsSold” for cells F5:F12 and “COG” for cells C5:C12, then, the formula in B1 can be =SUMPRODUCT(Retail,UnitsSold)-SUMPRODUCT(COG,UnitsSold)
b. Create a formula in cell F1 (named it as OrderWeight) showing the SUMPRODUCT(Weight,UnitsSold)= SUMPRODUCT(D5:D12,F5:F12)
c. Set the objective to find the maximum value of cell B1 (Profit).
d. Use the range name UnitsSold as the variable cells.
e. Add the following constraints:
i. The values in the UnitsSold named range (F5:F12) must be less than or equal to the values in the Stock named range (E5:E12).
ii. The value in the Order Weight named range (D5:D12) must be less than or equal to the maximum weight for the order as defined in the MaxWeight named range (F2)
iii. The values in the UnitsSold named range (F5:F12) must be whole numbers only.
f. Run Solver and accept the solution.
g. NOW, VERY IMPORTANT, GO BACK AND UNDERSTAND WHAT YOU JUST DID! You just ran an optimization model where the objective is to maximize profit by changing the units sold for each product. This is great for forecasting and assigning Marketing expenditures!
2. Click on PO July worksheet.
Remove any exact duplicates.
Create a Pivot Table showing OrderDate and PO# under ROWS, and “COUNT of ItemID”, “SUM of Received” and “SUM of Value Received” as VALUES. It will automatically create the values for COLUMNS.
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.
By purchasing this solution you'll be able to access the following files:
Solution.xlsx and Solution1.xlsx.