COMPSYS is a start-up computer company. This year, the first year of operations, the company expects to reach a Sales Revenue of $750,000. The company expects its sales to grow at a rate of 15% per year. Its Cost of Goods Sold (COGS) is running at 34% of Sales Revenue, and is expected to remain at that rate. Its Selling Costs are 12% of Sales in the first year and are expected to increase by an additional 3% per year after that. Its General and Administrative Costs (including Research and Development) are $410,000 this year, and are scheduled to rise at 4% every year after that. Earnings Before Taxes (EBT) is Sales Revenue less Cost of Goods Sold, Selling Costs, and General and Administrative Costs. Taxes are 25% of Earnings Before Taxes, if the Earnings Before Taxes are greater than zero but less than or equal to $50,000. If earnings are greater than $50,000 per year, then the tax rate is 35%. Use IF statements for computing taxes.

a)        Design and implement a worksheet to make a 5-year income statement projection for COMPSYS starting from this year. First, plan the format and layout of your worksheet areas. You should have separate areas for documenting the spreadsheet, indicating areas of the worksheet, and identifying assumptions with well-labeled separate cells for each of the growth rates and proportional factors. You should have a separate row for each item in the income statement and a separate column for each year. The final row should be for Earnings After Taxes. Use the Fill operation wherever possible.

b)        Calculate the Net Present Value (NPV) of the Earnings After Taxes for the 5 years. Use a Discount Rate of 7%. Provide a label to indicate the results and place the value at the bottom of your worksheet.

c)         Format your spreadsheet in an attractive manner. The first sheet should contain a brief documentation of the software package that you develop. The second sheet should be the EXCEL model. Here, the assumptions (given in the initial paragraph) should be first stated followed by the actual spreadsheet. The third sheet should contain the graphs you generate.

Optional (the following sections: d and e are optional but will be awarded bonus points if completed correctly)

d)        The management feels that the estimate for the first year's Sales Revenue, Cost of Goods Sold, Selling Costs, and General and Administrative Expenses may not be as certain. Since these four items are critical to the success of the operations, the management would like you to perform a sensitivity analysis to see what would the NPV look like when these numbers fluctuate within a range of +/- 20% of the estimate. That is, provide estimates of costs and revenues for this range which should be 80, 85, 90, 95, 100, 105, 110, 115, and 120% of the standard estimates.

(Hint: When you are formulating for these four items, add a certainty factor to the formulas. The certainty factor should not be hardcoded in the formulas. Always keep the input assumptions in a separate area in your worksheet. Use the Create Data Table under Data to perform the sensitivity analysis.)

e)        Produce the following graphs to present to the management:

• Sales and total expenses over the five-year period. Mark where the business starts to make a profit;

• The impact of fluctuations of the initial estimates for the cost of goods sold percentage; sales revenue; and the general and administrative expenses on the NPV.

(Hint: The horizontal axis should be from 80% to 120% of the original estimates, whilst the vertical axis should be the NPV. Remember, the 100% point is your original estimates. The curves for sales, cost of goods sold and the general and administrative expenses should be in the same graph.)

Be sure to 1) give the null and alternative hypotheses, 2) label which hypothesis is the claim, 3) Identify whether the test is left-tailed, right-tailed, or two-tailed, 4) name the test being used, 5) find the P-value for the sample, 6) state whether you reject or fail to reject the null hypothesis, and 7) give the conclusion in the context of the problem.

3. A student at South Plains College claims that the average cost of textbook is more than $75 dollars. Test this student’s claim using a 0.05 level of significance. A random sample of 15 textbooks had an average price of $78.15 and a standard deviation of $8.80. (9 pts)

4. The health of the bear population in Yellowstone National Park is monitored by periodic measurements taken from anesthetized bears. A sample of 54 bears has a mean weight of 182.9 lbs and it has previously been found that the population standard deviation of bear weights is 81.8 lb. Use a 0.10 level of significance to test the claim that the population mean of all such bear weights is less than 200 lb. (9 pts)

5. It has been believed that one in four Americans (25%) relies on unconventional medicine. Test this claim using a 0.01 level of significance if a recent study reported in the New England Journal of Medicine found that in a random sample of 1,539 adults, 428 of them used some form of unconventional medicine. (9 pts)

6.   A medical researcher wishes to see whether the pulse rates of smokers are higher than the pulse rate of nonsmokers. Samples of 100 smokers and 100 nonsmokers are selected. The results are shown below. Using a level of significance of 0.05, test the claim that smokers have higher pulse rates than nonsmokers. (9 pts) Also, find the 90% confidence interval for the differences of the means. (3 pts)

       Smokers                                        Nonsmoker

x1 = 90 x2 = 88

s1 = 5 s2 = 6

n1 = 100 n2 = 100

Retained Earnings T-Account: Debit- 200 and 400/ Credit-700

Dividends T-Account: Debit-100, 100, and 200/ Credit-400

Income Summary T-Acount: Debit- 1,500/ Credit- 800, and 700

Find (a) the revenue for the period (b) Dividends for the period (c) Net income (d) Ending balance of retained earnings

Sara sells two types of cakes from home. The total fixed cost for every month is budgeted at 200 BD. The labor cost per unit is equal to 2 BD. Sara sells 150 cake every month. The selling prices is 15 BD. The ingredient cost is 4 BD per cake.
Sara wants to know what price she must charge to generate enough revenue to cover her costs. With Break-Even Analysis, Sara can compare different pricing options and calculate how many units sold will lead to profitability. She needs to calculate the contribution margin which equal to selling price minus the variable costs. Contribution margin shows the revenue earned per unit, after deducting variable costs and needs to be enough to cover the company's fixed costs. Sara needs to calculate the following:

1) Break-Even Price, to determine the price needs to be set to generate enough revenue to cover her costs. Break-Even Price equal to 1 / ((1 - Total Variable Costs Percent per Unit) * (Total Fixed Costs per Unit)). Where Variable Costs Percent per Unit = Total Variable Costs / (Total Variable + Total Fixed Costs). Then determine how changes in unit sold and cost per unit affect Break-Even Price, unit sold between 100 and 200 in 10 increments and Cost per unit between 3.5 and 6.5 in 0.5 increments.

2) Break-Even Units Sold, to determine the number of units that need to be sold to achieve the break-even point. To calculate the Break-Even Units Sold, we divide the total fixed costs by the contribution margin for each unit sold. Then determine how changes in unit sold and cost per unit affect Break-Even Unit, price between 7 and 17 in 1 increments and Cost per unit between 3.5 and 6.5 in 0.5 increments

I need it to be solved using modeling , sensitivity analysis and financial excel functions with excel or written.

York Airlines offers three classes of service on their flights between Toronto and Vancouver: economy, premium economy, and business class. Andrew, a student, is willing to pay a maximum of $300 for an economy class ticket, but is willing to pay up to $100 extra to travel in premium economy or in business class. Rebecca, a successful CEO, is willing to pay up to $500 for an economy class ticket, $800 for a premium economy ticket, and $1,000 for a business class ticket. For simplicity, suppose that York Airlines’ marginal costs are negligible (it has to pay a fixed cost to fly the airplane, but there are no marginal costs per seat sold, regardless of the class of service), so the management of the airline simply wants to maximize revenues.

(a) If the airline could perfectly price-discriminate, what class of service, and at what price, would it offer to each of the travelers? What would be the airline’s total revenues in such a case?

(b) Now suppose that the airline cannot perfectly price-discriminate; they must offer tickets in all classes of service to all customers, and tickets in a particular class of service must be sold at the same price to all customers. Suppose that the airline management decides to charge $300 for economy class tickets, $399.99 for premium economy tickets, and $1,000 for business class tickets. What kind of ticket will Andrew purchase? What kind will Rebecca purchase? What are York Airline’s total revenues?

(c) Suppose that business class tickets go on sale, and their price falls to $790. What kind of ticket will Andrew purchase? What kind will Rebecca purchase? What are York Airline’s total revenues?

(d) Suppose that the airline decides to reduce the cost of business class tickets even further, cutting the price down to $590. What kind of ticket will Andrew purchase? What kind will Rebecca purchase? Is this a good pricing strategy for the airline?

(e) Now suppose that York Airlines decides to get rid of the premium economy seats on their fleet. They maintain their $300 economy class tickets, but increase the price of business class tickets back up to $790. Is this a good strategy for the airline?

QUESTION 14

1. Table 24-1
   
   The table below pertains to Pieway, an economy in which the typical consumer’s basket consists of 10 bushels of peaches and 15 bushels of pecans.
   

   Year	Price of Peaches	Price of Pecans
   2005	$11 per bushel	$6 per bushel
   2006	$9 per bushel	$10 per bushel

   
   
   Refer to Table 24-1. If 2006 is the base year, then the CPI for 2006 was

   	a.	100.
   	b.	83.3.
   	c.	120.
   	d.	240.

QUESTION 15

1. Table 24-1
   
   The table below pertains to Pieway, an economy in which the typical consumer’s basket consists of 10 bushels of peaches and 15 bushels of pecans.
   

   Year	Price of Peaches	Price of Pecans
   2005	$11 per bushel	$6 per bushel
   2006	$9 per bushel	$10 per bushel

   
   
   Refer to Table 24-1. If 2005 is the base year, then the inflation rate in 2006 was

   	a.	16.7 percent.
   	b.	40 percent.
   	c.	20 percent.
   	d.	44.1 percent.

QUESTION 16

1. Table 24-1
   
   The table below pertains to Pieway, an economy in which the typical consumer’s basket consists of 10 bushels of peaches and 15 bushels of pecans.
   

   Year	Price of Peaches	Price of Pecans
   2005	$11 per bushel	$6 per bushel
   2006	$9 per bushel	$10 per bushel

   
   
   Refer to Table 24-1. If 2006 is the base year, then the inflation rate in 2006 was

   	a.	44.1 percent.
   	b.	16.7 percent.
   	c.	40 percent.
   	d.	20 percent

QUESTION 18

1. The substitution bias in the consumer price index refers to the

   	a.	substitution by consumers toward a smaller number of high-quality goods and away from a larger number of low-quality goods.
   	b.	substitution by consumers toward new goods and away from old goods.
   	c.	substitution by consumers toward goods that have become relatively less expensive and away from goods that have become relatively more expensive.
   	d.	substitution of new prices for old prices in the CPI basket of goods and services from one year to the next.

Instead of making an issue of stock to investors at large, companies sometimes give their existing shareholders the right of first refusal. Such issues are known as privileged subscription, or rights issues. Shareholders own one “right” for each share they held. Shareholders could sell, exercise, or throw away these rights. The following is an example.

The Bank of Notreal will offer a rights issue to its existing shareholders in order to increase its capital base by $58,750,000. The bank will allow shareholders to subscribe to new shares at a price which is $12.97 below the current market price of the common shares. Shareholders will receive one right for every share held. The bank's partial statements are shown below:

                                    Partial Income Statement ($000)      

Revenue                                  $213,500

Interest                                        91,000

Earnings Before Tax                122,500    

Taxes (@50%)                           61,250

Earnings After Tax                    61,250    

_________________________________________________

Earnings per Share                        3.50      

Price Earnings Ratio                      7.71

(a) How many common shares must be issued to finance the offering?

(b) How many rights are needed to subscribe to one new share?

(c) What is the ex-rights market value of the common shares?

Vaughn Enterprises is a boutique guitar manufacturer. The company produces both acoustic and electric guitars for rising and established professional musicians. Vanessa Aaron, the company’s sales manager, prepared the following sales forecast for 2018. The forecasted sales prices include a 5% increase in the acoustic guitar price and a 10% increase in the electric guitar price, to cover anticipated increases in raw materials prices.

1.AWK a public trade water utility company, has a Beta of .24. the most recent 10 year treasury yield is 3.15%. what is the required rate of return on this company? Please use historical average market premium to answer the question. 2.suppose AWK paid $2 dividend in the past 12 month and its dividend is supposed to grow at 2% per year for ever. what would be a reasonable estimate of AWK's stock price based on the dividend discount model? 3.now assume AWK's dividend will grow at 2.5% in the first 3 years and after 3 years it will grow at 2%. what would be a reasonable estimate of AWK's stock price based on the two stage dividend discount model?

4. instead of dropping suddenly from 2.5% to 2% in one year. lets assume that the growth rate AWK gradually decrease from 2.5% to 2%. please estimate AWK's stock price based on the H model.

5. AWK's earning per share in the past 12 month was $3.43. what would be a justified P/E ratio of AWK? used info from 1, 2 and 3

Dr. Matthew Friedman designed an experiment for unknown purposes. It consisted of showing research subjects two gambles, one at a time. The first gamble (”Gamble A”) offered participants an 80% chance of winning $5 (otherwise nothing). The second gamble (”Gamble B”) offered participants a 10% chance of winning $40 (otherwise nothing). They were shown to subjects who were asked which they preferred. Then the gambles were considered one at a time and subjects were asked to price them. In this part, the research subjects were told that they would ”own” the gamble in questions and could sell it back to the researcher for sure cash. Given those terms, they were asked to identify the minimum price they would accept to sell each gamble. Out of 789 test subjects, 653 always chose Gamble A as their preferred option, yet always assigned a higher price to Gamble B. These results were confirmed through repeated surveys of the same research participants. Are the prices participants quoted consistent with their choices? Are these choices consistent with accepted models of rational decision-making? Use concepts from behavioral economics to explain what is going on here and why.