BITS211 Students, This document is being provided to you as a model, or example of a CCP Project. The document is in PDF format and it's purpose is to give students a visual concept of the scope of the project. This specific example, or any variation thereof, is not to be used for your own CCP project. You must create your own business scenario that meets the guidelines and rubric requirements. Your proposal must be approved by your instructor in Week 2. Review the documents in the Syllabus area for all details regarding the CCP project. Note 1: Each page in this PDF document represents a separate worksheet page in the Excel workbook. In other words, this model Excel workbook has 12 sheets. Note 2: This model does not include examples of every option that you have available to you to fullfill the requirements. (i.e. Macro and Solver are not included)

Title: Author: Jane Doe Date: 31-May-11 Purpose: This workbook is designed to organize the inventory and average amount of linens rented out during the month of June for the company called Party Town USA. It presents this information in various ways to help the company while making business decisions. Currently, the company is thinking of expanding, so the calculations focus on deciding which colors to order more items of. It also presents a projected income statement and various calculations related to net income. (Expand as necessary) Project Elements Explanation **Feel free to utilize more space for explanation then what is provided by stretching the rows or wrapping the text. Location (Sheet Tab and Cell References hyperlinks) Data Sheets The first sheet of data shows the inventory the store currently has on hand for each color in each item (napkins, table cloths, etc.). The second sheet show the rental amounts for each week in June, with the averages for the month of June displayed at the top of the sheet. The final sheet of data show a projected income statement for the business. Data 1 A1:I34 Data 2 A1: I173 Data 3 A1:B25 Formulas/Functions (list each) Formula #1 The AVERAGE function is used to calculate the average number of items rented out based off data for the four weeks in the month of June. Data 2 B3:F33 Formula #2 The SUM function is used to calculate the total number of items in inventory based off type (napkin, chair tie, etc.) and based on color. Data 1 H3:H34; B34:F34 Formula #3 Subtraction is used to calculate the number of items that were not rented out during an average week in the month of June. This takes the total number in stock, minus the average number rented out. Data 2 H3:H33 IF-function The IF function is used to determine how soon the company should order more of each color based off the popularity of the color. For example if the item has a very high popularity they need to order more, if it has a high popularity, they need to order more soon. Data 2 J3:J33 Filtered List The filtered list is used to indicate which colors had over 500 items that were not rented out during the average week in June. This may indicate that there are too many of this item in stock and it shows that the company will not need to order more of this item in the near future. Filtered List Conditional Formatting The conditional formatting highlights the top 10% of inventory rented out during the average week of June based on category and based on the total column. Data 2 B3:G33 Chart #1 This chart shows the total number of items the store carries in each color. This will help them determine which colors they have the fewest number of items for, which will help them to decide which colors to order more of. Bar Graph A1:P28 Chart #2 This chart shows the most popular colors based off the total number rented out during an average week in June. This will help the store to decide which items they need to make sure they have a good supply of. Pie Chart A1:P28 Lookup Table w/Lookup Function The Lookup Function is used to assign a popularity rating to each color. This will help the store to determine which colors they will want to buy more of first when they are ready to expand their operations. Data 2 I2:I33; L2:M7 Data Validation w/ error message There is a data validation on the numbers that can be entered for the weekly rentals. The number must be a whole number because a customer cannot rent a fraction of a linen. The number also must be less than or equal to 1,500 because that is the largest number of items Party Town USA has in stock. As the stock increases, so will the limit on these cells. Data 2 B38:F68; B73:F103; B108:F138; B143:F173 Pivot Table with chart The pivot table and chart are used to show how many items the store has in stock based off the popularity ratings for the colors. Since the store has more low popularity items in stock, they may want to order more of the high and very high popularity items to help expand operations. Pivot Table A1:F27 Pick one of two One-Variable Data Table A one-variable data table is used to see the effect that different revenue amounts from linen rentals would have on sales, gross profit, and net income. The store wants to earn a higher income, and is interested in seeing how increasing the linen rentals will affect this and other income statement items. One-Variable E3:H11 Goal Seek One of Excel's What-If Analysis tools that provides a method to find a specific value for a cell by adjusting the value of one other cell. The business question to answer is: If we want to decrease the value of the rent expense percentage of cost of goods sold to 30%, what rent cost should we look for? Since there is only one variable in this calcultion, Goal Seek can be used. Goal Seek'!A1 Pick two of four Macro (Note: A Macro example is not included in this model but it is one of the options to meet this area of the project requirements) Two-Variable Data Table A two-variable table is used to show the company how their net income would be affected by various levels of sales and expenses. If they increase operations, they would also have to spend some more on expenses, so this table would allow them to see what levels of sales and expenses they would need in order to reach targeted net income. Two-Variable E1:K10 Scenario Manager The scenario manager allows the company to view some scenarios they have created and their effects on net income. The company is looking at either increasing their sale of party supplies, increasing the rental of linens, increasing both, or streamlining operations to decrease expenses. The scenario manager allows them to see the affect these options will have on their projected net income. These scenarios also consider the change the increase or decrease in operations will have on operating expenses. For example, rent expense increases when there are higher sales of party supplies because the store will need extra space to display the new merchandise. Summary shown in: Scenario Summary A1:H21 Solver (Note: A Solver example is not included in this model but it is one of the options to meet this area of the project requirements) Additional Included Functionalities Addition is used to determine total sales. Data 3 B6 Another formula is addition to determine cost of goods sold. Data 3 B11 Subtraction is used to determine gross profit. Data 3 B13 The sum function is used to add up total expenses. Data 3 B23 Subtraction is used again to determine net income. Data 3 B25 Defined Names The cells in Data 3 B3:B25 have defined names. This makes the scenario summary easier to interpret because it lists the name of the cell rater than just the cell reference. Data 3 B3:B25 Input Messages The cells in Data 2 where the weekly rental amounts are entered have an input message. This allows users to know what value they are supposed to enter into the cell. Data 2 B38:F56; B73:F103; B108:F138; B143:F173