Do in Excel and explain. thanks

You work for Theo Walcott Tours Ltd which provide tourists and visitors with ‘experiences’ of Perth and its surrounds. Your manager is currently investigating introducing another product, which are ‘Luxury’ helicopter rides over beautiful bushland. Each trip would be 50km in total.

Your manager wants you to use cost-volume-profit analysis in order to help assess the plan’s feasibility.

She provides you with the following estimated data:

Selling price per trip: $600 (total for 3 customers – trips only run with 3 customers)

Costs:

Fuel: $50 per trip

Walcott ‘goodie bag’ per customer: $40

Helicopter rental per month: $20,000

Insurance per month (unlimited trips): $1,000

Pilot costs: $5,000 per month plus $100 per trip

Maintenance costs are difficult to estimate but data from a similar company in a different location shows that these monthly costs were $11,000 when 5,000 kms were flown and $5000 when 1,500 kms were flown.

REQUIRED: Calculate the following:

1) The Break-even point in trips per month

2) The Break-even point in dollars of revenue per month

3) Assuming a profit after tax requirement from the Helicopter trip business of $120,000 per year and a tax rate of 30%, calculate:

1. a) Trips required per month to obtain target profit
2. b) Revenue required per month to obtain target profit

Your manager has requested that the spreadsheet is easy to use for ‘What-if’ analysis – so she would like to be able to change some of the inputs to see the impact on the calculations above – for example, if the Helicopter were able to be rented more cheaply or the selling price was increased.

Hence 3 Marks are allocated to ease of use and accuracy for ‘what-if’ analysis (which will also depend on the formulas used)

4) Answering briefly in words in excel, include some notes to your manager explaining the limitations of your analysis and the assumptions included in it.

A retail company receives most of its revenue from credit or debit card transactions or payments by check. Funds received from credit and debit card transactions automatically deposit in interest bearing accounts. However, the company does receive some cash payments from customers each day. These cash receipts total $7,250,000 annually. The company makes no cash payments to its suppliers or creditors. Money held in the cash account earns no interest. The company estimates that the transaction cost of moving funds from the cash account into investments that earn interest are $30.00 regardless of the amount transferred. The company earns on average 1.65% interest on funds held in interest bearing accounts and investments. 1. How much cash should the company allow to accumulate before making a deposit if it wants to minimize the total cost associated with carrying and converting cash? 2. If the company follows the optimal strategy identified in question 1, how frequently (on average) will the company move cash from the cash account into interest bearing investments? 3. If the company transfers funds as frequently as indicated in question 2: a. what will the total annual costs to transfer funds be? b. what will the average balance in the company’s cash account be? c. how much interest will the company forego annually by keeping funds in the cash account? d. what is the total cost to maintain a cash account? 4a. Determine the total cost associated with the cash account if the company transfers funds to interest bearing investments weekly rather than as often as indicated in question 2.

1.

Bond Investment Transactions

Journalize the entries to record the following selected bond investment transactions for Starks Products:

For a compound transaction, if an amount box does not require an entry, leave it blank.

a. Purchased for cash $90,000 of Iceline, Inc. 8% bonds at 100 plus accrued interest of $1,200, paying interest semiannually.

b. Received first semiannual interest payment.

c. Sold $60,000 of the bonds at 103 plus accrued interest of $680.

2.

Stock Investment Transactions

On September 12, 2,700 shares of Aspen Company are acquired at a price of $32.00 per share plus a $135 brokerage commission. On October 15, a $0.80-per-share dividend was received on the Aspen Company stock. On November 10, 1,080.00 shares of the Aspen Company stock were sold for $27 per share less a $54 brokerage commission.

When required, round final answers to the nearest dollar. For a compound transaction, if an amount box does not require an entry, leave it blank.

Prepare the journal entries for the original purchase, the dividend, and the sale under the cost method.

Wal-Mart is the second largest retailer in the world. The data file (WalMart_revenue.xlsx) is included in the Excel data zip file in week one, and it holds monthly data on Wal-Mart’s revenue, along with several possibly related economic variables. Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only (a) independent variable. (b) Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable. (c) Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable. (d) Which of these three models is the best? Use R-square value, Significance F values and other appropriate criteria to explain your answer. Identify and remove the four cases corresponding to December revenue. (e) Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable. (f) Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable. (g) Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable. (h) Which of these three models is the best? Use R-square values and Significance F values to explain your answer. (i) Comparing the results of parts (d) and (h), which of these two models is better? Use R-square values, Significance F values and other appropriate criteria to explain your answer. Please use one Excel file to complete this problem, and use one sheet for one sub-problem. Use a Microsoft Word document to answer questions. Finally, upload the files to the submission link for grading.

Part A:  The number of cars arriving at a self-service gasoline station during the last 50 hours of operation are as follows:

The following random numbers have been generated: 44, 30, 26, 09, 49, 13, 33, 89, 13, 37. Simulate 10 hours of arrivals at this station. What is the average number of arrivals during this period?

Part B:  The time between arrivals at a drive-through window of a fast-food restaurant follows the distribution given below. The service time distribution is also given in the table in the right column. Use the random numbers provided to simulate the activity of the first five arrivals. Assume that the window opens at 11:00 a.m. and the first arrival is after this, based on the first interarrival time generated.

Random numbers for arrivals: 14, 74, 27, 03

Random numbers for service times: 88, 32, 36, 24

What time does the fourth customer leave the system?

3-1 Fast Pizza hires college students who drive their own cars to deliver pizzas to customers. Fast Pizza is concerned that the company may be liable for damages caused by the company employees while they are driving their cars on company business. Identify a liability coverage form that Fast Pizza could purchase to deal with this exposure. Explain your answer.

Entrepreneurs have been a driving force in the beverage industry for more than a century. In 1886, John Pem- berton began marketing Coca-Cola as an over-the- counter medicine, and in 1929 Charles Grigg developed Bib-Label Lithiated Lemon-Lime Soda, today known as 7UP. The beverage industry has always provided oppor- tunities for entrepreneurs, but in the current market, the cost of purchasing new ingredients and technologies and the intense competition make the odds of a successful new product introduction less likely than in the past.1
New beverages are developed every year. In some years, more than 3,000 new beverage products are brought to the market, but many do not succeed. Entre- preneurs who attempt to succeed in this industry must be aware of the changing consumer tastes and industry trends.

Caffeinated Products: Coffee, Soft Drinks, and Water

Specialty coffee outlets in the United States experienced explosive growth during the 1990s, growing from only 200 in 1989 to approximately 10,000 by 2000.3
The most well-known name in the gourmet coffee in- dustry is Starbucks, but few people realize the company began in 1971. The company was started by three en- trepreneurs in Seattle’s Pike Place Market. The focus was on coffee and equipment, including filters, grinders, and pots—no scones, no cappuccinos. By 1987, there were only six Starbucks outlets, but another entrepreneur, Howard Schultz, saw the potential of Starbucks after traveling to Italy and seeing the many coffee bars there. Schultz raised $3.8 million and bought the company. The company went public in 1992 at $17 per share and within five months the stock price had doubled.4 By 2001, Starbucks had expanded to 3,500 stores in North America and 800 stores overseas.5 By 2004, it had 7,569 stores worldwide.6 Starbucks is also equipping its stores for high-speed wireless Internet access, so customers can surf the Net on their laptops or Palm Pilot. The longer people linger at the stores, the more likely they are to order another latte.7
Many entrepreneurs are not willing to let Starbucks own the coffee market, though. Caribou Coffee Com- pany was started by entrepreneurs after they had climbed mountains in Alaska in 1990 and saw a herd of  caribou in the valley below. By 2004, the company was the nation’s second largest specialty coffee company, em- ploying more than 3,000 people. The Caribou Coffee outlets look like Alaskan lodges with fireplaces and wooden cabinetry.8
A recent trend toward caffeinated soft drinks began with Jolt. Jolt was introduced in 1985 by C. J. Rapp, president of Global Beverages. Jolt became a moderate success and a fixture in the marketplace at a time when most other companies were taking caffeine out of their products. Although similar products entered the mar- ket after Jolt, there were few other successes.9 How- ever, by the late 1990s, caffeinated soft drinks were common and other companies were introducing simi- lar products.10
By the mid-1990s, an entrepreneur had developed another successful idea. A college student, David March- eschi, who used to pull all-nighters cramming for tests, developed the idea for caffeinated water. Although other students drank coffee or soda to stay awake, Marcheschi did not like the taste of either. He wondered why some- one couldn’t caffeinate plain water. A few years later, he mentioned his idea to a friend whose father owned a beverage company and within a few weeks, the formula  beverage company and within a few weeks, the formula for Water Joe was developed. In 1995, Marcheschi formed a partnership with Nicolet Forest Bottling and the product was launched.11 A small article appeared in a local paper, and then the Milwaukee Sentinel ran a front-page story that was picked up by the Associated Press. Articles about Water Joe spread rapidly across the United States.12 By the end of 1996, Water Joe was ship- ping 400,000 bottles each week and annual sales were about $12 million.13 By the year 2000, Water Joe had be- come a subsidiary of Artesian Investments, a 16-year-old company in Green Bay, Wisconsin. The national account manager for Artesian Investments states, “What we’re giving people is a healthier alternative.”14 As of 2003, Water Joe had expanded into Germany and was being introduced in the United Kingdom.15
Other creative entrepreneurs decided to sell similar products over the Internet. The founders of Thinkgeek. Com sell a “Case O’ Buzz Water.” Each bottle of water has the same amount of caffeine as two extra large cups of coffee.16
Herbal Drinks and Green Teas

Herbal drinks first become popular in 1970 when Mor- ris J. Siegel founded Celestial Seasonings, Inc., which markets herbal teas.17 Siegel has been described as a hip- pie with a penchant for herbs, and this persona has had a positive effect on the company. The culture of non- conformity led to a great deal of creativity, and by the mid-1990s, Celestial Seasonings was the leading spe- cialty tea maker in the United States.18 By 1998, Celes- tial Seasonings had jumped into the fastest growing segment in the tea industry—the green tea category. The market for green tea increased 53 percent in 1997 and showed no signs of slowing. Much of the growth in sales was attributed to research reports indicating that green tea may lower the risk of certain types of cancer and bal- ance cholesterol.19 By the end of the decade, Celestial Seasonings had teamed up with the company that intro- duced Arizona Iced Tea and launched a line of ready-to- drink teas in a smart retro bottle that looks like the melding of a glass bottle and a tin can.20
In 2000, Celestial Seasonings merged with the Hain Food Group. As of 2004, Celestial Seasonings was sell- ing 1.2 billion cups of tea per year. Morris (Mo) Siegel retired to climb the last section of the Colorado moun- tains he had not yet climbed.21
John Bello, cofounder of SoBe Beverage Co., says his company is “taking the concept of herbal remedies to the mass market.” SoBe’s products include a variety of teas containing plant extracts that improve alertness. One of the company’s “energy tonics” allows drinkers “to perform all day and all night.” Other teas include echi- nacea, selenium, or bee pollen for additional therapeutic purposes.22 A new marketing approach was imple- mented for some of its products in 2000. Six of its products—Energy, Lizard Fuel, Lizard Lightning, Elixir, Green Tea, and Lemon Tea—were marketed in paper cans. Each octagonal paper can was adorned with the radical SoBe lizard. The colorful labels come in pink, or- ange, tan and bright yellow.23 As of 2004, SoBe bever- ages were available internationally. The company was selling its product in Canada, Mexico, the Bahamas, the United Kingdom, Barbados, and Guam.24
Richard Keer, president of The Natural Group, an im- porter of all-natural nonalcoholic beverages, has re- cently begun to market a product called Ame, a drink made with fruit juices, eastern herbs, and spring water. It is available in red, white, and rose and is packaged in 250-ml and 750-ml bottles. The company also sells Nor- folk Punch, a nonalcoholic beverage based on an ancient monastic recipe of 35 different herbal extracts like fennel, rosemary, and peppermint.25

Juice Bars and Smoothies
Proponents of smoothies contend that the beverage is one of the most promising new beverage items since spe- cialty coffees. The term smoothie is a generic term for a blender-made concoction typically made from fresh fruit, fruit juices, ice, and sherbet or yogurt. Optional add-ons include calcium, protein powder, bee pollen, or the herb gingko biloba. Smoothies are often sold at juice bars and are marketed as a lowfat, high-nutrition meal in a cup.26
One company, Smoothie King, has been in existence for 24 years, since long before the great demand for the product developed. Richard Leveille, vice president of franchise development, calls Smoothie King’s products the first and best available. Its product is not yogurt- or sherbert-based, but primarily fruit-based. Smoothie King makes daily deliveries to the Dallas Cowboys camp, and during spring training it delivers 200 to 300 smoothies a day to the New York Yankees in Tampa.27 By 2004, Smoothie King had 340 units in 34 states and also had three international units.28
Another company, Jamba Juice Co., was establishing itself as a leader in the juice bar segment. Founder, Kirk Perron, established his first juice bar when he was 26 years old. Perron states that his company did not “invent smoothies or squeeze-to-order juices,” but his company was the first to “unlock the code and create a sensory ex- perience in those products.” Jamba Juice sells its prod- ucts in an atmosphere of hot pinks, purples, greens, oranges, and natural woods.29 By December 2004, the company had 430 units, with locations in airports and  oranges, and natural woods.29 By December 2004, the company had 430 units, with locations in airports and on college campuses.30

Duiscussion Questions

Using demographic segmentation, segment the market for

a. Water Joe

b. Celestial Seasonings tea

c. Smoothies

d. the green tea industry

Using benefit segmentation, segment the market for

a. Water Joe

b. Koppla

c. Smoothies

d. the green tea industry

The rapid growth of Water Joe fueled the creation    of the caffeinated water industry in 1996. How long do you expect the rapid growth of this industry to continue?

Identify potential market segments for Ame and the energy tonic, the products of SoBe Beverage Co.

What impact do entrepreneurs have on the beverage industry?

What national trend would be beneficial for Celestial Seasonings but detrimental for Water Joe?

Winterbourne is considering a takeover of Monkton Inc. Winterbourne has 27 million shares outstanding, which sell for $74 each. Monkton has 22 million shares outstanding, which sell for $37 each. Merger gains are estimated at $110 million.

If Winterbourne has a price-earnings ratio of 15 and Monkton has a P/E ratio of 10, what should be the P/E ratio of the merged firm? Assume in this case that the merger is financed by an issue of new Winterbourne shares. Monkton will get one Winterbourne share for every two Monkton shares held. Assume that merged firm will have net earnings equivalent to the sum of each individual firm. (Do not round intermediate calculations. Round your answer to 2 decimal places.)