Excel Spreadsheet Assignment
CIS54 - Systems Analysis and
Design
The Southworth Nursery in Stoughton, Massachusetts is preparing an income statement (my version, using my calculations which do not follow valid business formulas in many cases - I am more interested in having you do the spreadsheet). Your assignment is to prepare the statement shown below using formulas wherever possible, few literals should be coding into the formulas. See the specifications below.
|
|
|
|
Southworth Nursery |
|
|
|
||
|
|
|
|
Projected
Income Statement |
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
2003 |
2004 |
2005 |
2006 |
2007 |
2008 |
|
|
|
|
|
|
|
|
|
|
|
|
Sales |
|
|
|
|
|
|
|
|
|
|
Trees |
$ 112,586.25 |
$ 118,215.56 |
$ 124,126.34 |
$ 130,332.66 |
$ 136,849.29 |
$ 143,691.76 |
|
|
|
Bushes |
$ 135,103.50 |
$ 141,858.68 |
$ 148,951.61 |
$ 156,399.19 |
$ 164,219.15 |
$ 172,430.11 |
|
|
|
Annuals |
$
90,069.00 |
$
94,572.45 |
$
99,301.07 |
$ 104,266.13 |
$ 109,479.43 |
$ 114,953.40 |
|
|
|
Perennials |
$
67,551.75 |
$
70,929.34 |
$
74,475.80 |
$
78,199.59 |
$
82,109.57 |
$ 86,215.05 |
|
|
|
Bulbs |
$
45,034.50 |
$
47,286.23 |
$
49,650.54 |
$
52,133.06 |
$
54,739.72 |
$
57,476.70 |
|
|
Total Sales |
$ 450,345.00 |
$ 472,862.25 |
$ 496,505.36 |
$ 521,330.63 |
$ 547,397.16 |
$ 574,767.02 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Cost of Goods Sold |
|
|
|
|
|
|
|
|
|
|
Trees |
$
50,663.81 |
$
53,197.00 |
$
55,856.85 |
$
58,649.70 |
$
61,582.18 |
$
64,661.29 |
|
|
|
Bushes |
$
67,551.75 |
$
70,929.34 |
$
74,475.80 |
$
78,199.59 |
$
82,109.57 |
$
86,215.05 |
|
|
|
Annuals |
$
54,041.40 |
$
56,743.47 |
$
59,580.64 |
$
62,559.68 |
$
65,687.66 |
$
68,972.04 |
|
|
|
Perennials |
$
33,775.88 |
$
35,464.67 |
$
37,237.90 |
$
39,099.80 |
$
41,054.79 |
$
43,107.53 |
|
|
|
Bulbs |
$
18,013.80 |
$
18,914.49 |
$
19,860.21 |
$
20,853.23 |
$
21,895.89 |
$
22,990.68 |
|
|
Total Cost of Goods
Sold |
$ 224,046.64 |
$ 235,248.97 |
$ 247,011.42 |
$ 259,361.99 |
$ 272,330.09 |
$ 285,946.59 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Gross Profit (before
expenses) |
$ 226,298.36 |
$ 237,613.28 |
$ 249,493.94 |
$ 261,968.64 |
$ 275,067.07 |
$ 288,820.43 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Operating Expenses |
|
|
|
|
|
|
|
|
|
|
Advertising |
$
22,517.25 |
$
23,643.11 |
$
24,825.27 |
$
26,066.53 |
$
27,369.86 |
$
28,738.35 |
|
|
|
Fertilizer |
$
4,503.45 |
$
4,728.62 |
$
4,965.05 |
$
5,213.31 |
$
5,473.97 |
$
5,747.67 |
|
|
|
Utilities |
$
33,775.88 |
$
35,464.67 |
$
37,237.90 |
$
39,099.80 |
$
41,054.79 |
$
43,107.53 |
|
|
|
Maintenance |
$
22,517.25 |
$
23,643.11 |
$
24,825.27 |
$
26,066.53 |
$
27,369.86 |
$
28,738.35 |
|
|
|
Hourly
workers (seasonal) |
$
37,440.00 |
$
38,750.40 |
$
40,106.66 |
$
41,510.40 |
$
42,963.26 |
$
44,466.98 |
|
|
|
Hourly
workers (year) |
$
18,720.00 |
$
19,375.20 |
$
20,053.33 |
$
20,755.20 |
$
21,481.63 |
$
22,233.49 |
|
|
|
Manager
salary |
$
50,000.00 |
$
52,500.00 |
$
55,125.00 |
$
57,881.25 |
$
60,775.31 |
$
63,814.08 |
|
|
Total Operating
Expenses |
$ 189,473.83 |
$ 198,105.12 |
$ 207,138.49 |
$ 216,593.01 |
$ 226,488.68 |
$ 236,846.44 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Income Before Taxes |
$
36,824.54 |
$ 39,508.16 |
$
42,355.46 |
$
45,375.63 |
$
48,578.39 |
$
51,973.99 |
|
|
|
Income
Taxes |
$
12,152.10 |
$
13,037.69 |
$
13,977.30 |
$
14,973.96 |
$
16,030.87 |
$
17,151.42 |
|
|
|
Net
Profit |
$
24,672.44 |
$
26,470.47 |
$
28,378.16 |
$
30,401.67 |
$
32,547.52 |
$
34,822.57 |
|
|
The total sales for 2003 is given to you and the number is 450345.00. Using this as your base, most of the other information on the spreadsheet is calculated from there.
Calculating sales:
- Trees are 25% of the sales
- Bushes are 30% of the sales
- Annuals are 20% of the sales
- Perennials are 15% of the sales
- Bulbs are 10% of the sales
Each year the projected sales increases by 5%, but the projected percentage of sales for each product remains the same.
Calculating cost of goods sold:
- The cost of trees is 45% of the sales of trees
- The cost of bushes is 50% of the sales of bushes
- The cost of annuals is 60% of the sales of annuals
- The cost of perennials is 50% of the sales of perennials
- The cost of bulbs is 40% of the sales of bulbs
Calculating operating expenses: (I decided to use total sales as the basis…)
- Advertising is 5% of total sales
- Fertilizer is 1% of total sales
- Utilities is 7.5% of total sales
- Maintenance is 5% of total sales
- Hourly workers (seasonal) - we start out with 4 and a pay of $9. The cost of seasonal workers should be increased by 3.5% for every year past 2003. (Note that the 4 and the $9 should be declared as variables off to the side in the work area and should be used for the calculations).
- Hourly workers (year) - we start out with one using the $9 pay defined for the seasonal workers. This cost will also increase by 3.5% each year.
- Manager salary - we start out with 50000 and increase by 5% each year.
Taxes are 33% and the percent should be defined off to the side in the work area.
Other things you should do:
- Make the format look like mine (or close to it)
- Change the tab name from sheet 1 to Projected Income Statement
- Define the sales area and give it a name
- Go to sheet2 (change the name to chart) - create a chart of the sales information using the name of the sales area to convey the cell information for the chart
- Write a letter to the owner explaining what you did and including the spreadsheet and chart as part of the word document.
You should pass in the spreadsheet and the word document containing the spreadsheet to me via email. Note that I am looking for basic skills in the use of formulas etc so your grade will be determined by your effective use of formulas etc.