Excel Assignment
(as of 20 October)
This assignment uses
the Scenario
Manager tool. The instructor’s demonstration during
the lab and reviewing Technology Plug In 4 will
help you understand the mechanics of using the tool.
This problem set describes a retail store scenario, where the total
projected revenue for 2008 is expected to be $100,000.
The total revenue comes from the sales of a mixture of 3
different products sold in the store - called Products A, B
and C. For this assignment, you will create 8
different scenarios each time varying the product mix in order to
determine how much floor space should be dedicated to each product.
Assumptions:- The
percentage of floor space dedicated to a product will directly
coorelate to the percentage of total revenue generated by the product
(i.e. if Product A has 80% of the floor space, it will generate 80% of
total revenue).
- Each product
type has a different cost ratio (Cost of Goods Sold) which is
indicated in the COGS formula on the spreadsheet. This will
not vary.
- Salary is 15% of
Revenue
- Advertising is 2% of
Revenue
- Miscellaneous
expenses are 1% of Revenue
- Tax
rate is 30%
Assignment
Instructions:
- Download the file
from the Schedule website (ExcelHomework.xls).
- Click on each of the
values to become familiar with the construct of
the spreadsheet.
- Using
the assumptions listed above, enter the correct formulas for the
remainder of the spreadsheet. (hint... the Net Profit should be $29,050
if you fill out the spreadsheet properly)
- Keeping the floor
space for Product C constant at 10%, create and save 8 scenarios, each
time varying the product mix of Product A and B in increments of 10% (e.g. see below
for the first three variations)
| Product A | 0.8 | | Product A | 0.7 |
| Product A | 0.6 |
| Product B |
0.1 | |
Product B | 0.2 |
| Product B | 0.3 |
| Product C |
0.1 | |
Product C | 0.1 |
| Product C | 0.1 |
5. After the
scenarios are saved in
the spreadsheet, create
a summary report (like the one shown at the bottom of page 12 in T-4).
6. Make sure the results cells include Gross Profit and Net Profit.
7. Print out this sheet (yes, it will run across 2 pieces of paper).
8. Circle the product mix scenario that results in the highest net profit.
9.
Could you have figured out the optimum product mix without running the
spreadsheet? Why or why not? Write your answer on the bottom
of the paper.
10.Which of the assumptions listed above is the most unrealistic? Why? Write your answer on the bottom of the paper.
11. Make sure your name is on the paper.
Grading Rubric: each point is worth 20%
1. Turn it in, with your name on the paper.
2.
Meaningful labels are used for the Changing Cells and Results
Cells (for example using the label "Product A" instead of $D$10)
3. You have circled the correct answer.
4. You have correctly answered # 9 above.
5. You have correctly answered # 10 above.
-
