Please prepare Excel solution for the following cash flow problem with graphs
The Income
statement and balance sheet for Camelot Inc. are provided here. Note that firm’s capital expenditures are
expected to rise by $50,000 in the new year. This will lead to an increase of
$5,000 in accumulated depreciation.
Sales next year should be $4.3M
1. Using percentage of sales analysis techniques
prepare a pro forma income statement and balance sheet for the next year.
2. Create a
chart of sales by year for all years, including your pro forma estimate.
3. Add a
trend line.
4. Create a scatter plot of sales vs. cogs. Add a trend line.
5. Regress
cogs against sales.
6. Using
your sales trendline and annual sales data forecast the sales level in the next
3 years (3 years after the year with 4.3M in sales). Forecast using the trend line as well as at least
one of the following: trend, linest, regression.
|
Camelot |
||
|
Statement |
||
|
For the |
||
|
Cash |
|
|
|
Net |
$ |
|
|
Depreciation |
$ |
|
|
Change |
$ |
|
|
Change |
$ (1,20,800) |
|
|
Change |
$ |
|
|
Change |
$ |
|
|
Total Cash Flows from |
$ |
|
|
Cash |
|
|
|
Change |
$ |
|
|
Total Cash Flows from Investing |
$ |
|
|
Cash |
|
|
|
Change |
$ |
|
|
Change |
$ |
|
|
Change |
$ – |
|
|
Cash |
$ |
|
|
Total Cash Flows from Financing |
$ |
|
|
Net Change in Cash Balance |
|
$ |





