Last One – https://courseworkhero.co.uk/ Only

Okay, I’ve added the directions along with the assignments. Three are already done as you can tell they just look a little incomplete. If you could please complete them along with the final, that would be awesome. So, that even better for you. Thanks for all your help.

Excel Assignment Directions1

Excel Tutorial 2: Mini-Case 2

Scenario:
Justine Temps received a large sum of money last year for graduation from her grandmother. Rather than spend it on a new car, Justine decided to try her hand at investing in stocks. After spending some time researching how the stock market works and how to evaluate the value of a company’s stock, she decided to jump in with both feet and purchased shares of twenty stocks. Justine also decided to use her Excel skills to create a workbook that she can use to track the performance of her portfolio. Her workbook consists of two spreadsheets. One (the portfolio sheet) lists all of her stocks. The other (the summary sheet) provides a summary of the portfolio and includes a section that will let her see a summary of an individual stock from the portfolio based on entering the stock market symbol.

Starter File: Tutorial 2: Mini-Case Stock Portfolio

Your Task:
Your job is to complete the stock portfolio workbook for Justine. To do this, you will need to download the Excel file EXC_2_MC_Stock Portfolio.xlsx. Open it in Excel and carry out the following operations. (Comments are provided within the worksheets to remind you what to do.)

Portfolio sheet: Look up each of the stocks that are listed in your local paper or on the Web and enter the current price. (Remember, the purpose of this exercise is to practice Excel, so it may be permissible to enter fake data.)

Portfolio sheet: Enter a formula for calculating the purchase commission. The purchase commission is equal to the value of the stock when purchased (quantity times purchase price) times the purchase commission rate that is provided at the top of the spreadsheet. Copy this formula to the rest of the column.

Portfolio sheet: Enter a formula for calculating the commission if sold. The commission if sold is equal to the value of the stock when sold (quantity times current price) times the sales commission rate that is provided at the top of the spreadsheet. Copy this formula to the rest of the column.

Portfolio sheet: Enter a formula for calculating the return value of the stock if it were to be sold at the current price. The return value is equal to the value of the stock when sold (quantity times current price) minus the value of the stock when purchased (quantity times purchase price) less the purchase and sales commissions. Copy this formula to the rest of the column.

Summary sheet: Complete the Portfolio Summary section by entering the appropriate formulas/functions for each item in this section.

Summary sheet: The Lookup section should allow Justine to type a stock symbol into the appropriate cell. When this is done, the other cells should display the appropriate values that correspond to the entered stock symbol. Enter the appropriate formulas/functions into each of these cells.

Both Sheets: Format all numeric values appropriately.

Both sheets: Be sure to test all your formulas and functions thoroughly.

Excel Tutorial 3: Mini-Case 1

Scenario:
Portalis, Inc. is a Web incubator. That is, the company creates and maintains new Web sites until each site is well enough along that Portalis can spin the site off as a new start-up company. The firm currently has five promising Web sites. Kelly Fenetre has been put in charge of monitoring the number of unique visitors to the sites. The end of the fiscal year has just passed, and Kelly has been invited to the managers’ retreat to present the performance of each Web site over the past year. Kelly has been told to focus on two things: (1) the relative trends in numbers of unique site visits, and (2) the proportion of total visits on each site. Kelly has been keeping track of the site visits in an Excel workbook and would like to generate a couple of charts to tell the story behind the numbers.

Starter File: Tutorial 3: Mini-Case Site Visits

Your Task:
Your job is to complete the Site Visits Excel file for Kelly by creating the following charts using the data found EXC_3_MC_Site_Visits.xlsx, which you can download.

Relative Visit Trends Chart: Select the appropriate chart type to show how the number of unique visits to each site has changed over time. Your chart should allow for the trends of all five sites to be seen at once. Complete your chart with the following:

Add the following titles: Main title: Unique Site Visits by Month (FY: 06); X axis: Months; Y axis: Unique Visits.

Place the legend underneath the chart.

Include horizontal and vertical main gridlines.

Save your chart as a separate sheet in the workbook

Site Proportions Chart: Select the appropriate chart type to show how the proportion of total visits that each site received during the month of May. Complete your chart with the following:

Choose a three-dimensional chart type for greater effect.

Add the following titles: Main title: Unique Site May-06.

Do not include a legend.

Label each portion with the site and the percentage of the overall visits to the site.

Highlight the site with the largest number of unique visits in May 06 by separating it from the rest of the chart.

Save your chart as a separate sheet in the workbook.

Excel Tutorial 4: Mini-Case 2

Scenario:
Amelie, the excursions manager for the outdoor recreation retailer WildOutfitters, has started to offer guided raft trips to rivers in the southeastern United States. Groups of clients can select and reserve trips to one of seven rivers: the Chattooga, Ocoee, Pigeon, Nantahala, Nolichucky, French Broad, or Cheoah. The store offers one trip a day, and each trip is led by a certified raft guide. Amelie has collected data about each of the river trips taken during the 2006 season. She would like to use this data to make decisions about how to reward her guides. The main problem is that the data is listed in order of occurrence. Amelie would like to look at the data in various ways to see what it tells her.

File:
Download and open the Excel spreadsheet EXC_4_MC_WO_River_Trips.xlsx

Starter File: Tutorial 4: Mini Case Wild Outfitter River Trips

Your Task:
Your job is to create a PivotTable and PivotChart that will help Amelie evaluate the number of clients supported by each WildOutfitters guide. Create your PivotTable so that you can answer the following questions:

Which guide served the most clients in the 2006 season?

Which guide served the least number of clients during the 2006 season?

Which guide guided the most clients down the Cheoah River?

Which guide guided the most “long” trips (4.5 hrs)?

How many trips did Luz guide on the Pigeon River? (Hint: Use the Show Detail feature of the PivotTable).

Microsoft Excel Project 1: Global River Conservancy Survey Analysis

Learning Objectives

By reading the business case sections and completing the three parts of this project (Parts A, B, and C) you will:

Extend your knowledge of MS Excel.

Learn how to apply MS Excel to solve business problems.

Understand how to find and use built-in MS Excel functions.

Project Introduction

The Global River Conservancy (GRC) is a nonprofit environmental group whose primary purpose is the protection of free-flowing rivers. Lisa Rios, a business school graduate, has recently become a brand manager for a major corporation. In her spare time she works as a volunteer for the GRC.

The GRC is interested in starting a major fund-raising drive. The funds will then be used to lobby Congress in an effort to designate the Broad River as a Wild and Scenic River. GRC has asked Lisa to use her marketing skills to develop effective advertising for the drive. Lisa will first need to determine the target market, which you will help her do in

Part A

of the Excel project. She will then conduct a phone survey to determine the best form of advertising to promote the fund-raising effort. In

Part B

you will help Lisa analyze the budget for the survey, and in

Part C

you will help her analyze the results of the survey. When Lisa and you have finished all the parts of the project, the GRC should have some valuable information with which it can begin its fund-raising effort.

Project Spreadsheet Instructions

Starter File: Project 1: Global River Conservancy

You will need to download the Excel file globalRiverConservancy.xls from the Web site for this text. This workbook contains three worksheets: one worksheet for each part of this project.

Part A

Part A Business Case

Lisa is excited about this opportunity. Not only will she be working for a cause that she believes in, but the project will also enable her to apply and refine the marketing skills she learned in school. Lisa knows that the marketing concept can be applied to any organization, and her knowledge of it will be beneficial.

One hurdle that Lisa must cross is the limited budget that she has to work with. She will have to plan carefully to achieve the maximum benefit from the available funds and also be able to justify her expenses to the GRC board of directors. Lisa decides that she needs to be armed with the proper information to best prepare and justify the plan.

The board has indicated that it wants Lisa to develop an advertising campaign for the fund-raising effort. The goal, as with any advertising campaign, is to reach the target market effectively and efficiently. Lisa concludes that she first needs to determine who the target market is and then find out the best way to reach them. To do so, she chooses first to study demographic information, with the hope that it will indicate the best market segments to target. Then she will conduct a survey of the target market to discover which media would most effectively reach them.

Lisa has learned that there is much demographic data available. However, she has also learned that data is useless without an effective way to analyze and interpret the information. Fortunately, Lisa had a lot of practice using electronic spreadsheet software, such as Excel, to analyze data while she was an undergraduate. She thinks she sees a way to use a spreadsheet to help her determine her target market.

Part A Problem Definition

Your job in this first project is to help Lisa by using MS Excel to analyze the required demographic data. Lisa has determined that a good way to select target markets is to examine household information based on income level. She reasons that such an analysis will point to the groups that are most likely to contribute to environmental causes.

She has already found information gathered by other groups that shows the percentage of households in each income level that have contributed to environmental causes in the past. In addition, the data includes the average annual amount of total environmental contributions by households that have made donations. She has also been able to obtain household figures from census reports and other marketing sources (this information is included in the project spreadsheet file). With this information she will be able to calculate the expected environmental contribution per household to all environmental causes. She can then estimate the total contribution for each group. By looking at the percentage figures for each group, she eventually wants to target advertising toward the group or groups that give the most contributions. She is also interested in seeing whether there are any recognizable trends in the data that might help her make future decisions.

Part A Spreadsheet Instructions

To complete the analysis, you need to perform the following calculations using the spreadsheet:

Contributions per household for all households in an income level are calculated by multiplying the percentage of households contributing for each income level by the average contributions for the income level. For example, 7.00% of $55

$3.85/household.

Lisa wants to forecast the numbers of households by income level for the years 2005 and 2010. She will do this using the following growth/decline rates, which she calculated earlier using estimates of relevant households. The numbers represent the growth rates between 1990 and 2000. Without evidence to the contrary, these growth rates are assumed to remain the same through the year 2010. Format these as percentages with 1 decimal place.

$0-$20K

$20-$40K

$40-$70K

$70-$100K

Over $100K

-11%

22.5%

66%

77%

70%

Enter the values into the column marked Growth Rates under the basic assumptions section of the spreadsheet. For example, the Over $100K group was expected to grow at a rate of 70% over the five-year period from 2000 to 2005. Assume this growth rate will also apply from 2005 to 2010.

Calculate the forecast number of households for this income level in 2005 using the equation:

Similarly, calculate the forecast number of households for each income level in 2010. Use the same growth rates and the 2005 forecasts to calculate forecasts for 2010. (Format these values to 0 decimal places, since households are counted in whole numbers.)

Use an appropriate function to obtain a total estimate of households for each of the years in the table.

Next, we need to calculate the total estimated environmental dollar contributions by income level for each of the years in the table.

Therefore, calculate these dollar figures by multiplying the number of actual or estimated households in an income level by the previously computed contributions per household. This latter number, we remember, takes into account both the households who gave and the others who did not. Compute the total dollar figures for 1990, 1995, 2000, 2005, and 2010. Format these as currency with 0 decimal places. (Hint: By carefully using absolute cell references and copying, you can save yourself some time here. See whether you can type the formula only once into cell B31 and then copy the formula to the rest of the table.)

Compute the total contributions for all income levels for each year by using the appropriate spreadsheet function.

To calculate the percentage of environmental contributions by income level, simply divide the estimated contribution for each level and year by the total contributions for that year. Format as a percent with two decimal places. (Hint: Keep absolute cell referencing in mind here.)

Lisa is also interested in obtaining a good graph that indicates the target market to use in a presentation for the GRC board, so you will need to create one. She decides she needs a histogram (column chart) of the percentage of environmental contributions by income level for the year 2005. The graph should be created as a column chart with the income levels as the labels for the columns and the percentages for 2005 as the heights of the columns. Since the graph will be used in a presentation, it should also have titles and legends

where

appropriate.

Part A Interpretative Questions

Based on your analysis of your results, answer the following questions.

Which is the best single-income level for Lisa to target for her advertising campaign? Why?

Briefly explain any trends you detect from the data in the spreadsheet, especially in terms of the number of households per income level. How might this affect future GRC fund raising?

Part B

Part B Introduction

After studying the demographic analysis (which you did for Lisa in project A), Lisa has decided that the demographic group $40–$70K is the target market for her survey. The next step is to determine the details of the survey:

sample size

, timing, and budget. She can use this information to prepare a report to the GRC board and request the necessary funds.

Part B Business Case

The GRC board has promised Lisa a total budget of $18,000 for the survey but will allow her a little extra if it is justified. She now needs to analyze her expected costs to see whether it is feasible to do the survey within budget. She has already decided that she will conduct a phone survey, so there will be several sources of expenses. These are wages, employee training, the costs of making phone calls, telephone rental, office rental, and miscellaneous supplies. The company Lisa works for has done extensive marketing research, and it has donated the use of a computerized phone number list for the survey. The list has the names and numbers of the heads of households divided into income groups.

Lisa tried to get volunteers to man the phones, but given the short notice and the fact that most of the GRC’s members work or go to school during the day, she was unsuccessful. One of the members gave her the name of a good temporary employment agency where she could get employees to work the phones. She would pay the agency $17.50 per hour for the workers, who would each work 8 hours per day. The agency has several workers who have experience conducting phone surveys, so training costs will be minimal. Lisa expects that training costs will be about $23 per person. This assumes that once an employee is trained, that employee will work every day until the project is completed.

Lisa has scheduled the survey for one week in the near future. This allows 5 days for the survey to be conducted. She will need to take this into account in her analysis in determining how many temporary workers and phones she will need. Through some other contacts, Lisa found office space she could rent for $220 a day and telephones she can rent for $30 each per day.

Based on previous surveys conducted by the GRC, Lisa figures that each call will average 9 minutes in length. This average is for all calls, including both those that reach a cooperative respondent and those that do not. The average excludes training time, which precedes actual phone calls. Unsuccessful calls include those calls in which no one answers and those in which someone answers but refuses to participate in the survey. Some time for paperwork and caller breaks is also built into this average.

A major consideration when designing a survey (and many other types of marketing research) is that of selecting an appropriate sample. A sample is a small portion of the population. Data from the sample is then used to deduce information about the entire population. Sampling is necessary in cases where it would be difficult or too expensive to collect information about the entire population. Three questions to answer when selecting a sample are (1) who is to be sampled, (2) how big the sample should be, and (3) how the sample should be selected. Lisa has already determined the answer to the first question using the demographic analysis you completed in Project A. The target population for the survey was chosen to be households with income between $40,000 and $70,000.

The sample size should be large enough to provide statistical validity to the survey. One method of determining

the sample size

for a random sample is based on statistical precision. This method uses a confidence interval for each important criteria measured in the survey. A confidence interval gives a range into which the true population value of the characteristic being measured will fall, assuming a given level of certainty. The smaller this range, the more precise our conclusions about the true population value of the characteristic are. A confidence interval is calculated using:

Where

C.I.

the range in which the true value lies

the estimated value of the characteristic

Zα/2

the reliability coefficient

standard deviation of the estimated value

the sample size

By making estimates of our characteristic and our desired precision, we can calculate the necessary sample size. The last term of the preceding equation, which includes the reliability coefficient, standard deviation, and sample size, determines the

tolerance level

we can accept. By selecting an acceptable tolerance level, which represents the allowable difference permitted between the estimate and its known true value, we can manipulate this part of the equation and solve for the sample size.

For our survey Lisa has decided that the most critical question involves how much respondents are likely to donate to the GRC. She wants to have a large enough sample size to estimate the average donation to within a tolerance level of plus or minus $3.25.

Next, we will choose the reliability coefficient. Reliability coefficients can be found on statistical tables based on how much confidence one wants to have in the estimate. Lisa would like to have 99% confidence in the result. For this confidence level we need Z1-α = 2.33.

We also need an estimate of the standard deviation. This can be estimated using a small test sample or through the use of past data. Lisa has analyzed the past contributions to GRC fund raising and she found that these averaged $96 with a standard deviation of $38.

With these values we can use the following formula to calculate the sample size:

where

the reliability coefficient

standard deviation of the estimated value

sample size
Z1-α
h	tolerance level

Lisa must also decide how the sample will be selected. Since the list includes addresses, it is easy to divide the list into different geographic regions. Then, samples can be selected at random and analyzed from each of the regions. This method is a form of sampling called stratified sampling.

Using stratified sampling, we will divide the total sample size among the different regions. The amount of households sampled in each region can be determined by assigning a weight to each region, which is based on the number of households in the region. The regional sample sizes can then be found by multiplying the weights by the overall sample size.

For the GRC survey, because of the demographics of her state, Lisa has divided her state into five geographic regions. Region A includes the counties within the metropolitan areas around the state capital. Regions B, C, D, and E include the counties in the northeast, northwest, southeast, and southwest, respectively. The procedure Lisa used to determine the sample size for each region is to base each regional sample size on the proportion of households in the region. For example, if 28% of the total households in our target population live in Region A, then 28% of the total sample size will be contacted from this region. The numbers of households in each region are already provided in your spreadsheet.

Because some people will either not cooperate or not be at home when called, the survey team will need to make a larger number of calls in each region in order to obtain enough valid responses to meet the sample size requirements. Lisa was able to use data from past surveys to estimate a response rate for each region. The response rate represents the percentage of total calls made that result in a valid survey response. For example, if the response rate in a region is 0.39, then 39% of the total calls made to the region will be useful for the survey. If we divide the needed regional sample size by the response rate, we can calculate the number of calls necessary to obtain the sample size in a region. The response rates for each region are already provided in your spreadsheet.

The average cost per call from Lisa’s city to each region is also provided for you on the spreadsheet. These averages include the costs for all calls including those that result in invalid responses.

Now Lisa and you have enough information to complete the survey budget analysis.

Part B Problem Definition

Your job is to help Lisa with her survey budget analysis. Using Excel, you should conduct an analysis that will provide answers to the following questions:

What is the total needed sample size for the entire survey?

How many total calls will be needed to meet the sample size?

How many phones and workers will Lisa need?

What is the expected total cost of the survey?

You will answer these questions in writing in the Part B Interpretive Questions section that is found later in the project. For now, keep them in mind as you create your budget analysis.

Part B Spreadsheet Instructions

You have already downloaded the project Excel file when you downloaded the file for Part A. The spreadsheet will initially contain all labels needed and some of the values mentioned above.

To complete the spreadsheet, you need to perform the following steps:

Enter the basic information into the appropriate cells on the spreadsheet. The numbers to be entered can be found by reading through the background section above. Remember to give them the proper format (currency, number, etc.).

Calculate the total sample size using the formula discussed above:

Round the answer to the nearest whole number (since we don’t want to survey 1/2 of a household) using the appropriate spreadsheet function (this is not simply formatting the value to 0 decimal places). Be sure to use the appropriate cell addresses in all formulas.

Complete the Call Calculations sections as follows: format all values to zero decimal places, except regional response rate, which is already formatted for you.

Use the correct spreadsheet function to compute the total number of all households.

Compute the Percent of Total households for each region by dividing the Number of Households in each region by the overall total Number of Households.

Calculate the Regional Sample Sizes by multiplying the Percent of Total households in each region by the overall sample size. Use a function to round these to values with no decimal places.

Compute the Number of Calls (to make) per region by dividing the Regional Sample Size by the Regional Response rate. Use a function to round these to values with 0 decimal places.

Use the appropriate spreadsheet function to calculate the grand totals for Regional Sample Size and Number of Calls.

Complete the Budget Calculations section as follows:

Compute the Regional Call Costs by multiplying the Cost per Call by the Number of Calls needed in each region. (The format for these numbers should be currency with 0 decimal places.)

Calculate the Call Time by Region by multiplying the Number of Calls and the average time per call. Convert this to hours by dividing the result by 60. (The format for these numbers should be fixed with 0 decimal places.)

Use the appropriate spreadsheet function to compute the grand totals for Regional Call Costs and Total Call Time.

Compute the Total Shifts by dividing the grand total Call Time by Region by Calling Shift. A shift is equal to one 8-hour day. (The format for these numbers should be fixed with 1 decimal place.)

Calculate the Number of Employees and Phones Required by dividing the number of Shifts Required by the number of Days for Survey. Use a function to round this value to the nearest integer.

The following formulas are used to compute the various expenses. (The format for these numbers should be currency with 0 decimal places.)

Wages = (Total Call Time)*(Wage Rate)

Training = (Employees/Phones Required)*(Training Costs)

Calls = (Total Call Costs)

Phone Rent = (Employees/Phones Required)*(Phone Rental)*(Days for Survey)

Office Rent = (Office Rent per day)*(Days for Survey)

Miscellaneous Supplies = $1750

Use the appropriate spreadsheet function to total the expenses.

Lisa would also like to have a horizontal bar chart she can use to compare the Number of Calls required per region. Create this chart using the data in the spreadsheet. Include appropriate titles and legends, as necessary.

Interpretive Questions

Based on your analysis results, answer the following questions.

Here again are the questions from the Part B Problem Definition section:

(a)

What is the total needed sample size for the entire survey?

(b)

How many total calls will be needed to meet the sample size?

(c)

How many phones and workers will Lisa need?

(d)

What is the expected total cost of the survey?

What effect would decreasing the average time per call from 9 minutes to 8 minutes have on Lisa’s total budget? Give specific values.

Part C

Part C Introduction

Lisa and her team have completed the media survey. Thanks to the careful planning and the analyses you helped Lisa with in Project Parts A and B, there were few problems, and an adequate sample size was reached. In Part C, you will help Lisa analyze part of the results of the survey. From the analysis it will then be possible to choose the advertising medium or media that is/are expected to be most effective for the fund-raising effort.

Part C Business Case

The survey was completed as planned, in 5 days with only a few extra hours of overtime required to obtain a good sample size. The final total sample size obtained by Lisa and her team was 977 households. After carefully looking through the responses, it was determined that 43 responses were invalid. A response was designated invalid if key questions were not answered or if the answers obtained were inconsistent. Invalid responses were removed from the data set and were not used in the analysis.

The survey was designed so that each worker could first record the name, address, and telephone number of the person he or she was about to contact. This information was available on the telephone list that was donated by Lisa’s company. The callers were careful not to ask for this information from the respondents, since asking for this information will often cause a respondent to choose not to respond to the survey. When a respondent was contacted, the caller would begin the conversation by reading an opening paragraph. If the respondent needed additional information about the GRC and its purpose (as was often the case), the caller would then read a second explanatory paragraph. Next, the respondent would be asked if he or she could answer some brief questions. As mentioned in Part B, many times there would be no answer to the call or respondents would refuse to participate. In these cases, the caller would simply discard the survey form and begin again with the next person on the list. The callers would also ask a first question to ascertain whether the respondent was an adult and qualified to give valid responses. If the person who answered was not an adult, the caller would then ask to speak to one.

The remaining questions were divided into three categories: Media Viewing Habits, Environmental Group Involvement, and Personal Information. The questions under Media Viewing Habits were designed to solicit information that could be used to determine the media most likely to be seen by the target market. The Environmental Group Involvement questions were designed to determine the level of potential donations from each respondent household. The purpose of the Personal Information category was to gather demographic information, which is an important part of many surveys. The demographic information could be used when designing the advertisements so that they appeal to the target market.

When giving the survey, each caller would read each question and mark the responses as instructed. At the end of each day, the completed surveys were gathered and the question responses tabulated. Those surveys judged invalid were noted and discarded. The tabulated results of the valid questionnaires are now ready for analysis.

One basic form of analysis conducted with most survey results is to examine the frequency distribution for each question. A frequency distribution breaks each question into categories and shows the number of responses that fall into each category. By dividing the number of responses in a category by the total number of responses for the question, the distribution is shown in percentage terms. Usually, a cumulative frequency is presented, which shows the percentage of responses in a category plus all previous categories. The frequency and cumulative frequency distributions can be used to calculate statistics, such as the median and the mean. For example, the frequency distribution for question 2 (television hours per day) would be:

Frequency Distribution for Television Hours:

Summary

Justine Temps’ Stock

Portfolio

Portfolio Summary Section Total Value: 20

91.4104

5 Average Value: 104.5

5225 Largest Gain: $220.82 Largest Loss: Lookup Section Stock Symbol

: AA C

ompany Name

: Alcoa Inc. Purchase Date

: 7/10/06 Quantity

70
Purchase Price

: 33.55 Purchase Commission

: 0.15% Current Price

: $35.64 Commission if Sold

: 0.20% Return Value

: 137.787

Enter Stock Symbol from Protfolio here
Use an appropriate function to look up the Company Name from the Portfolio page based on the value provided above for the Stock Symbol
Use an appropriate function to look up the Purchase Date from the Portfolio page based on the value provided above for the Stock Symbol
Use an appropriate function to look up the Purchase Price from the Portfolio page based on the value provided above for the Stock Symbol
Use an appropriate function to look up the Current Price from the Portfolio page based on the value provided above for the Stock Symbol
Use an appropriate function to look up the Return Value from the Portfolio page based on the value provided above for the Stock Symbol
Use an appropriate function to look up the Quantity from the Portfolio page based on the value provided above for the Stock Symbol
Use an appropriate function to look up the Purchase Commission from the Portfolio page based on the value provided above for the Stock Symbol
Use an appropriate function to look up the Commission if Sold from the Portfolio page based on the value provided above for the Stock Symbol
Enter the appropriate formula/function to calculate the total current return value of the portfolio
Enter the appropriate formula/function to calculate the average current return value of the portfolio
Enter the appropriate formula/function to calculate the largest negative return value of the portfolio. If there is no negative return, then the cell should display “No Losers”

Portfolio

0.15%

0.20%

Stock Symbol Company Name Purchase Date Quantity Purchase Price Purchase Commission Current Price Commission if Sold Return Value
AA Alcoa Inc. 7/10/06 70

$35.64

7/10/06

7/10/06 50

7/10/06 5

7/10/06 80

7/10/06 90

7/10/06 80

7/10/06 65

7/10/06 80

7/10/06 50

7/10/06

$220.82

7/10/06 20

7/10/06 65

7/10/06 50

7/10/06 40

7/10/06 70

	Justine Temps’ Stock Portfolio
Purchase Commission Rate:
Sales Commission Rate:
$33.55	3.52275	4.9896	$137.79
AMGN	Amgen	30	$66.39	$2.99	$68.71	4.1226	$62.49
BBW	Build A Bear Workshop					80	$20.	90	$2.51	$23.09	3.6944	$169.00
BUD	Anheuser Busch	60	$45.96	$4.14	$46.55	5.586	$25.68
CitiGroup				50	$49.08	$3.68	$50.98	5.098	$86.22
GCI	Gannett Co. Inc.	$56.49	$4.24	$56.90	5.69	$10.57
GOOG	Google, Inc	$425.00	$3.19	$426.50	4.265	$0.05
GPW	Gerogia Power	$21.55	$2.59	$24	3.84	$189.57
INTC	Intel Corporation	$18.56	$2.23	$20.76	3.3216	$170.45
JBLU	JetBlue Airways Corp.	$12.10	$1.63	$14.12	2.5416	$177.62
LOW	Lowe’s Companies Inc.	$28.96	$3.48	$30.09	4.8144	$82.11
MCD	McDonalds Inc	$33.18	$3.24	$35.86	4.6618	$166.30
MOT	Motorola Inc.	$19.81	$2.38	$22.05	3.528	$173.29
NSC	Norfolk Southern Co.	$51.55	$3.87	$52.62	5.262	$44.37
SUNW	Sun Microsystems Inc	200	$3.91	$1.17	$5.03	2.012
UPS	United Parcel Services	$81.09	$2.43	$85.07	3.	40	$73.76
VIA	Viacom Inc	$34.80	$3.39	$35.74	4.6462	$53.06
WMT	Wal-Mart Stores Inc.	$46.00	$3.45	$47.33	4.733	$58.32
XOM	Exxon Mobil Corp.	$62.82	$3.77	$64.20	5.136	$46.29
YHOO	Yahoo, inc	$32.50	$3.41	$34.67	4.8538	$143.63

Insert a formula or function here that will calculate the purchase commission.
Purchase commission = purchase commission rate times quantity times purchase price.
Use cell addresses for each value. DO NOT type any numbers into the formula.
Copy your formula to the other cells in this column.
Search the web for the current price of this stock.
Insert a formula or function here that will calculate the commission if sold.
Commission if sold = commission if sold rate times quantity times purchase price.
Use cell addresses for each value. DO NOT type any numbers into the formula.
Copy your formula to the other cells in this column.
Insert a formula or function here that will calculate the return value. This is the amount of money that Justine will earn minus the cost of the investment.
return value = current price times quantity
minus purchase price times quantity
minus the purchase commission
minus the commission if sold..
Use cell addresses for each value. DO NOT type any numbers into the formula.
Copy your formula to the other cells in this column.
Insert a formula or function here that will calculate the purchase commission.
Purchase commission = purchase commission rate times quantity times purchase price.
Use cell addresses for each value. DO NOT type any numbers into the formula.
Copy your formula to the other cells in this column.

Site Visit Stats

								Portalis, Inc.
Unique Site Visits Summary
Fiscal Year:	2006
Month	Site 1	Site 2	Site 3	Site 4	Site 5
Jun-05	20424	6086	34147	39202	24075
Jul-05	23450	6123	33289	41203	25397
Aug-05	24929	5745	31693	42918	24891
Sep-05	29003	6351	30035	44958	22741
Oct-05	31921	7182	28361	48711	24032
Nov-05	34827	7318	25763	53553	24976
Dec-05	37046	7177	24391	55399	23781
Jan-06	42284	6760	23078	57064	21655
Feb-06	47634	6314	23105	62788	20590
Mar-06	52587	5546	24833	70367	18570
Apr-06	59494	5282	23194	75322	19022
May-06		65167		6224		24282	82351
	17712

Site Visit Stats

Site 1
Site 2
Site 3
Site 4
Site 5

Unique site visits chart

Unique Site visits by Month (FY: 06)

months

Unique Visits

Unique site visits chart

38504 38504 38504 38504

38534 38534 38534 38534

38565 38565 38565 38565

38596 38596 38596 38596

38626 38626 38626 38626

38657 38657 38657 38657

38687 38687 38687 38687

38718 38718 38718 38718

38749 38749 38749 38749

38777 38777 38777 38777

38808 38808 38808 38808

38838 38838 38838 38838

				38504
				38534
				38565
				38596
				38626
				38657
				38687
				38718
				38749
				38777
				38808
				38838

Site 1
Site 2
Site 3
Site 4
Site 5
20424
6086
34147
39202
24075
23450
6123
33289
41203
25397
24929
5745
31693
42918
24891
29003
6351
30035
44958
22741
31921
7182
28361
48711
24032
34827
7318
25763
53553
24976
37046
7177
24391
55399
23781
42284
6760
23078
57064
21655
47634
6314
23105
62788
20590
52587
5546
24833
70367
18570
59494
5282
23194
75322
19022
65167
6224
24282
82351
17712

Unique site May-06

65167
6224
24282
82351

17712

May-06

Sheet3

Trip Data

06 Season

/06

Ocoee

30 2.5

/06

Chattooga Joe

2.5

Gunter 36 2.5

Gunter

4.5

Nantahala Joe

2.5

Nolichucky Joe

2.5

Pigeon Gunter 20 4

Ocoee Nick

2.5

/06

Pigeon Nick

Nick

4.5

/06

Cheoah Nick

4.5

/06

Chattooga Gunter

4.5

Cheoah Amelie

4.5

Chattooga Joe

4.5

Pigeon Gunter

/06

French Broad Nick 32 4.5

/06

Ocoee Luz 21 2.5

/06

Cheoah

4.5

Ocoee Nick

2.5

Nolichucky Nick 18 2.5

/06

Chattooga Mary 23 4.5

Pigeon Mary 29 4

/06

Nolichucky Mary 26 2.5

French Broad Amelie

4.5

Nantahala Amelie

2.5

Nolichucky Gunter 11 2.5

French Broad Gunter 14 4.5

Cheoah Luz 37 4.5

Nantahala Amelie

2.5

Nantahala Nick

2.5

French Broad Luz 32 4.5

Ocoee Amelie

2.5

Nolichucky Gunter 28 2.5

French Broad Luz 23 4.5

Nolichucky Amelie 33 2.5

Cheoah Joe 34 4.5

Ocoee Gunter 29 2.5

French Broad Mary 17 4.5

Pigeon Gunter 21 4

French Broad Mary

4.5

French Broad Luz 19 4.5

Pigeon Nick 20 4

Nantahala Mary 28 2.5

Cheoah Mary 29 4.5

Nolichucky Gunter 38 2.5

Cheoah Mary 12 4.5

Cheoah Luz 22 4.5

Nantahala Amelie 40 2.5

Chattooga Luz 36 4.5

Pigeon Joe 32 4

Nolichucky Joe 23 2.5

French Broad Nick 12 4.5

Nantahala Gunter 16 2.5

Cheoah Luz 35 4.5

Pigeon Joe 12 4

Ocoee Amelie 35 2.5

Nantahala Luz 23 2.5

Nantahala Mary 20 2.5

Pigeon Nick 28 4

Pigeon Joe 11 4

Nolichucky Luz 19 2.5

French Broad Amelie 37 4.5

French Broad Mary 25 4.5

Cheoah Joe 24 4.5

Cheoah Joe 27 4.5

Nolichucky Amelie 13 2.5

French Broad Luz

4.5

Cheoah Mary 33 4.5

Nantahala Mary 26 2.5

Ocoee Amelie 12 2.5

Chattooga Luz 11 4.5

Nantahala Mary 25 2.5

Chattooga Nick 15 4.5

Nantahala Amelie 16 2.5

Ocoee Gunter 10 2.5

Chattooga Nick 25 4.5

Ocoee Mary 18 2.5

Pigeon Luz 35 4

Nantahala Joe 32 2.5

Pigeon Mary

Ocoee Gunter 28 2.5

Nolichucky Amelie 32 2.5

Pigeon Gunter 20 4

Chattooga Nick 23 4.5

Nolichucky Joe 31 2.5

Chattooga Amelie 15 4.5

Ocoee Joe 43 2.5

Pigeon Amelie 35 4

Nantahala Gunter

2.5

Chattooga Amelie 27 4.5

Nantahala Joe 35 2.5

Pigeon Nick 25 4

Ocoee Mary 39 2.5

Nolichucky Joe 39 2.5

French Broad Luz 36 4.5

Pigeon Mary 40 4

Nantahala Nick 26 2.5

WildOutfitters Southeastern Raft Trips –

Lead

Number of

Date

River

Guide

Clients

Length of Trip (hrs)

Ocoee

Joe

2.5

Luz

Chattooga

Gunter

6/1/06

4.5

6/2/06

Nantahala

Amelie

6/3/06

Nolichucky

6/4/06

French Broad

6/5/06

6/6/06

6/7/06

Pigeon

Nick

6/8/06

6/9/06

6/11/06

Cheoah

6/14/06

6/15/06

6/16/06

Mary

6/20/06

6/21/06

6/23/06

6/25/06

6/26/06

6/27/06

6/28/06

6/29/06

6/30/06

7/1/06

7/2/06

7/3/06

7/4/06

7/5/06

7/6/06

7/7/06

7/8/06

7/9/06

7/10/06

7/11/06

7/12/06

7/13/06

7/14/06

7/15/06

7/16/06

7/17/06

7/18/06

7/19/06

7/20/06

7/21/06

7/22/06

7/23/06

7/24/06

7/25/06

7/26/06

7/27/06

7/28/06

7/29/06

7/30/06

7/31/06

8/1/06

8/2/06

8/3/06

8/4/06

8/5/06

8/6/06

8/7/06

8/8/06

8/9/06

8/10/06

8/11/06

8/12/06

8/13/06

8/14/06

8/15/06

8/16/06

8/17/06

8/18/06

8/19/06

8/20/06

8/21/06

8/22/06

8/23/06

8/24/06

8/25/06

8/26/06

8/27/06

8/28/06

8/29/06

8/30/06

8/31/06

9/1/06

9/2/06

9/3/06

9/4/06

9/5/06

9/6/06

>Part

–

emographic Analysis

onservancy

asic Assumptions:

Contributions

-$20K

.09

–

stimates of Households: By Income Level

Income
Level:

$0-$20K

$21-$40K

$41-$70K

$71-$100K

,076

Over $100K

Income
Level: 1990 1995 2000 2005 2010

$0-$20K

.01

$21-$40K

2.38

$41-$70K

.92

,178.83

$71-$100K

Over $100K

Total

Income
Level: 1990 1995 2000 2005 2010

$0-$20K

6% 4%

$21-$40K

10%

$41-$70K

$71-$100K 9%

Over $100K 5% 8% 9% 10% 10%


	Global River		C
Survey Demographic Analysis –	Contributions
		B
Percentage of
			Income			Households	Average	per	Growth
			Level:	Contributing:	Contributions:	Household:	Rates:
								$0		8				1	$	5	$4.21	-1	1%
			$21-$40K	25.37%	$	9		$23	22.	5%
			$41-$70K	58.00%						$1	$88.16	6	6%
			$	71					$100	65.10%	$168	$109.37	77%
			Over $100K	55.1	4%	$177	$97.60	70%
		E	$322.42
		1990			1995			2000			2005			2010
1,181,684	1,033,429	885,174	787,805	701,1	46
544,	89	671,640	798,355	977,985	1,198,031
103,037	352,105	601,173	997,9	47	1,656,592
25,766	109,787		193	28	3	43	607,244
17,172	57,735	98,297	167,105	284,078
		Total	1,872,554	2,224,6	96	2,576,827	3,273,917	4,447,092
Source: Adapted from U.S. Census Reports 1980,1990,2000
Estimated Environmental Contributions By Income Level
$4,977,	253	$4,352,802.95	$3,728,352.89	$3,318,234.07	$2,953,228.32
$12,579,827.40	$15,505,951.19	$18,431,	38	$22,578,443.41	$27,658,593.18
$9,083,	741	$31,041,576.80	$52,999,411.68	$87,979,023.39	$146,0		45
$2,817,975.89	$12,007,184.		62	$21,198,580.70	$37,521,487.85	$66,413,033.49
$1,675,949.42	$5,634,760.18	$9,593,57		0.95	$16,309,070.61	$27,725,420.04
$31,134,747.63	$68,542,275.74	$105,951,298.60	$167,706,259.33	$270,795,453.85
Percentage Environmental Contributions By Income Level
16%	2%
40%	23%		17%	13%
2		9%	45%	50%	52%	54%
1	8%	20%		22%	25%

PART B – Budget Analysis

/day

ing Shift:

$23

calculations

.19

Number of

Region Households Total Sample size

17%

22%

1 742 2

Regional

Region Call

Totals:

.74

$3,139.74

Office Rent:

Global River Conservancy
Survey Budget Analysis
Basic Information:
Standard Deviation:	$38	Wage Rate:	$17.50	/hr
Z(alpha = 0.01):	2.33		Office Rent:	$220		/day
Tolerance: +/-	$3.25	Phone Rental:	$30
Average time per call:9	minutes	Days for Survey:
	Call	hrs	Training	Cost	/person
	Sample size
based on above estimates :	n =		742
Call calculations:
	Number of	Percent of				Region	Regional
Response Rate	Calls
156315	26%	192.89	0.35	551.11
84253	14%	103.97			0.31	335.38
101999	125.87		0.32	393.33
132564	163.58	0.26	629.16
126324	21%	155.88		0.33	472.37
	Totals:	601455	2381
Budget Calculations:
	Cost per	Call Time by
Call Costs	Region (hrs)
$1.05	$578.67	82.67
$1.34	$449.40	50.31
$1.42	$558.53	59.00
$1.44	$905.99	94.37
$1.37	$647.14	70.86
$6.62		$3,	139	$357.20
Shifts Required:
Employees/Phones Required:
Expenses:
Wages:	$6,251.04
Training:	$205
Calls:
Phone Rent:	$150
$1,100
Misc. Supplies	$1,750
Total Expenses:	$12,596.17

PART B – Budget Analysis

Cost per
Regional
Call Time by

PART C – Survey Analysis

Global River Conservancy

Distribution for

Responses ***

Frequency Frequency

$0 43

0.08

139

0.33

to $100 253

0.95

$500 28

for Radio =

Responses ***

Cumulative

Frequency Frequency

$0 62 0.08 0.08
$1 to $50

0.31

$51 to $100

$101 to $500

0.17

More Than $500 46

1.00

Percent of Total Sample =

Responses ***

Cumulative

Frequency Frequency

$0 62 0.08 0.08

$1 to $50

0.31

$51 to $100

0.46 0.78

$101 to $500

0.94

More Than $500 47 0.06 1.00

741

Percent of Total Sample =

Mail Responses ***

Direct Cumulative

Frequency Frequency

$0 71

0.09

$1 to $50

0.32

$51 to $100

0.46 0.78

$101 to $500 127 0.16 0.94
More Than $500 45 0.06 1.00

Responses =

Percent of Total Sample =

Responses ***

Cumulative

Frequency Frequency

$0 89

0.12

$1 to $50 193 0.25

$51 to $100

0.45

$101 to $500

0.95

More Than $500 38 0.05 1.00

Percent of Total Sample =

Calculations ***

Cost per Median Households

Expected

Donation

Proceeds Cost Proceeds

Radio

Newspaper

Cable TV

Direct Mail

Telemarketing

Survey Data Analysis
Total Survey Sample Size:
***									Frequency	Radio
				Cumulative
Radio¥
					0.08
											to		$50	0.25
				$51		0.45		0.78
				$101							$500	0.17
				More Than		0.05				1.00
Total Radio Responses =	559
	Median	Donation					Percent of Total Sample =
*** Frequency Distribution for	Newspaper
Newspaper¥
165	0.22
338			0.46	0.77
	127			0.94
		0.06
Total Newspaper Responses =	738
Median Donation for Newspaper =
*** Frequency Distribution for	Cable TV
Cable TV¥
171	0.23
344
117		0.16
Total Cable TV responses =
Median Donation for Cable TV =
*** Frequency Distribution for	Direct
Mail¥
	0.09
186	0.24
362
Total	Direct Mail	791
Median Donation for Direct Mail =
*** Frequency Distribution for	Telemarketing
Tele-
Marketing¥
	0.12
0.37
348	0.82
102	0.13
Total Telemarketing Responses =	770
Median Donation for Telemarketing =
***	Expected		Proceeds
Target Households =	601172
Gross	Net
1000	Reached
$6.79
$7.65
$7.38
$4.50
$4.75
¥ Individual media for which responses to the donation question
have been tabulated for each frequency distribution.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Last One – https://courseworkhero.co.uk/ Only ”

Get high-quality paper

Guarantee! All work is written by expert writers!

Still stressed from student homework?

Get quality assistance from academic writers!

Order now

the number of the category in which the median falls
Un-1	the upper bound on the (n – 1)th category
c.f.n-1	the cumulative frequency of the (n – 1)th category
c.f.n	the cumulative frequency of the nth category
Un	the upper bound on the nth category