Guided Exercises

Open the Chapter07_WhatIfAnalysis file.

LoanAmount worksheets

You’re going to buy a house! Or a car…or whatever you want. Use the LoanAmount1 worksheet to model this as well as how different interest rates will affect the monthly payment.

1. In cell B4, calculate the actual amount of the loan given the purchase price in cell B2 and the down payment amount in cell B3.
2. In cell B7, use the following financial function to find the monthly payment for this loan:

=PMT(B5/12, B6*12, -B4)

3. In D3:E16, create a one-variable data table to show how various interest rates will affect the monthly payment. Format the monthly payment values as Accounting.
4. In B19:O20, create another one-variable data table to show how various interest rates will affect the monthly payment. Format the monthly payment values as Accounting.

You’re looking at houses (or cars, or whatever) that have a variety of purchase prices and now you want to know how various purchase prices and interest rates will affect your monthly payment so you can see what you can actually afford to buy. Use the LoanAmount2 worksheet to show this what-if analysis.

5. In C10:L21, create a two-variable data table to show how various interest rates and purchase prices will change the monthly payment. Format the values in D11:L21 as Accounting.

BigTenHoodies worksheet

This model involves a store buying hooded sweatshirts from a screen printer then selling them to customers. The model simulates the supply that the store decides to order, the demand that they expect from consumers, the prices at which they expect to sell the hoodies, the revenue that they will make from selling the hoodies, and finally the profit they hope to make with this business.

6. The value in cell B4 represents the initial supply of hoodies that the store has decided to buy from the screen printer. The cost per hoodie is based on the Price Structure table beginning in cell A23. In cell B5 use a function to retrieve the cost per hoodie that the store will pay for their initial supply. Format the value as Accounting.
7. In cell B6, use a formula to calculate the cost of the initial supply.
8. The value in cell B8 represents the initial demand for these hoodies which the store has estimated. In cell B10, write a function that will return how many of the hoodies the store is able to sell at the retail price. The store can only sell either the number of hoodies that it has in-stock (the initial supply) or the number of hoodies that consumers actually want to buy at full price (the initial demand). Therefore, the quantity of items sold at the retail price will be the smaller of those two numbers.
9. In cell B11, write a function that will return the number of hoodies left over that will need to be sold on clearance. This is again based on supply and demand. If the supply is higher than the demand, then the number of hoodies leftover will be the difference between the supply and the demand. Otherwise, the number of leftover hoodies will be 0 because the store would have sold all of them at the retail price.
10. The store has decided to sell the hoodies at the retail price in cell B13. Any leftovers will be sold at a price that is marked up from the cost that the store paid so that the store will still make a little bit of money. The markup percent is indicated in cell B14. In cell B15 write a formula that will calculate the clearance sale price. Format the value as Accounting.
11. In cell B17, use a formula that will calculate the revenue that the store will earn from selling hoodies at the retail price.
12. In cell B18, use a formula that will calculate the revenue that the store will earn from selling hoodies at the clearance price.
13. In cell B20, use a formula that will calculate the profit that the store will earn based on this model. Profit is the difference between the total revenue and the costs.

The model is now complete, but it only shows one situation. The store would like to see how their costs, revenue, and profit would be affected if the demand changes. We can test these different scenarios all at once with a one-variable data table.

14. In cell F5, make a cell reference to the cost of the initial supply from the model.
15. In cell G5, make a cell reference to the revenue made from the sale of hoodies both at retail and clearance prices from the model.
16. In cell H5, make a cell reference to the profit that the store makes from the model.
17. Create a one-variable data table to show how various values for the demand will affect the cost, revenue, and profit. Format all the values in the table, cells F6:H13, as Accounting.

The store would like to see how different supply and demand values would affect their profit. If the demand is high, but the supply is low, then the store could be missing out on making more profit. However, if the supply is high and the demand is low, then the store wants to see how much money they would potentially lose. We can test the impact of two-variables on a single output by using a two-variable data table.

18. In cell E19, make a cell reference to the profit from the model.
19. Create a two-variable data table to show how various supply values and demand values will affect the profit. Format all the values in the table, cells F20:L27, as Accounting.

If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive.

More Practice