You have been hired by Pistols & Lilies

$($

P&L

$)$

$$to assist in a project decision. You have already worked with your team to download and organize some historical financial statements for a similar project. You believe that the similar project information will help to make an assessment of what might occur for this project. Your team has also done some growth analysis and moving average analysis from historical numbers to help with the projections.

Complete Financial Statements

You have been tasked with creating the projections for the company for its new RoboGuitar. The company has estimated that it will be able to sell

$1$

$2$

$,$

$5$

$0$

$0$

$$units the first year. It expects that it will be able to grow the number sold by

$1$

$5$

$\%$

$$in

$2$

$0$

$2$

$3$

$,$

$1$

$2$

$.$

$5$

$\%$

$$in

$2$

$0$

$2$

$4$

$,$

$$and

$1$

$0$

$\%$

$$in

$2$

$0$

$2$

$5$

; because they cannot sell partial units, round up to the whole unit

$($

using the

$$

roundup

$$

$$function

$)$

$.$

$$It plans to price the RoboGuitar at $

$1$

$3$

$9$

$.$

$9$

$9$

$$in the first year and to grow its price at expected inflation of

$2$

$\%$

$.$

Some of the items will be based upon the

$\%$

$$of sales from the similar project information with which you have been provided

$($

Historical Worksheet

$)$

$.$

$$Your team has decided to weight the three moving averages at

$2$

$5$

$\%$

$,$

$3$

$0$

$\%$

$,$

$$and

$4$

$5$

$\%$

$$to more heavily weight more recent history. You anticipate that the COS and SG&A will be similar to the other project, the RoboDrums. The company believes that working

capital will be very similar, too. You will total all the current assets accounts

$($

except for cash and marketable securities

$)$

$$as a

$\%$

$$of sales and do the same for current liabilities

$($

except for the line of credit

$)$

$.$

$$Assume that the unit sales in

$2$

$0$

$2$

$6$

$$grow by

$-$

$5$

$\%$

$$for the working capital calculations in

$2$

$0$

$2$

$5$

$.$

The project requires an investment in plant upgrades and equipment of $

$1$

$,$

$0$

$0$

$0$

$,$

$0$

$0$

$0$

$.$

$$This will be fully depreciated according to the

$7$

$-$

year MACRS schedule

$($

percentages given in the template

$)$

$.$

$$The company anticipates an additional equipment purchase of $

$2$

$0$

$0$

$,$

$0$

$0$

$0$

$$in

$2$

$0$

$2$

$4$

$$that is depreciated with MACRS over

$7$

$$years$,$ also.

The company will issue $

$8$

$0$

$0$

$,$

$0$

$0$

$0$

$$in debt to finance the project at a rate of

$5$

$\%$

$$with annual payments of $

$6$

$0$

$,$

$0$

$0$

$0$

$.$

$$The company will inject $

$4$

$0$

$0$

$,$

$0$

$0$

$0$

$$in equity and fund any initial shortfall with the line of credit or excess cash will be invested in marketable securities

$.$

The company stated that the project will require a minimum cash balance of

$5$

$\%$

$$of upcoming sales

$($

including at the beginning of the project

$)$

$.$

$$Any cash in excess of the

$5$

$\%$

$$will be invested in Marketable Securities with an expected return of

$2$

$.$

$5$

$\%$

$.$

$$Any shortfall in cash will be provided by borrowing through P&G

$$

s Line of Credit that carries a rate of

$3$

$.$

$5$

$\%$

$.$

$$Just use prior year

$-$

end for interest calculations rather

than averages.

The company

$$

s marginal tax rate is

$2$

$5$

$\%$

$,$

$$and it pays out

$2$

$0$

$\%$

$$of net income as dividends once it has sufficient cash and retained earnings to do so

$.$

$$This may mean a partial dividend

$($

not full

$2$

$0$

$\%$

$)$

$$if there are losses in early years. You need to input a formula for this; it will need to have an

$$

If

$$

$$statement and Overview