Open the tutorial.xls Excel Workbook which is in the Hort4050 Table of Contents on the course home page. Select the POINSETTIA worksheet.
During the time you use this tutorial you will be making entries into a prepared Excel spreadsheet. You will use the mouse, the cursor keys, or the Page up and Page Down keys to move around the spreadsheet.
Spreadsheets are computer programs which perform mathematical operations. They are divided into a grid of columns and rows. Columns are labeled with alphabetic letters and rows are labeled with numbers. There are 256 columns and 16,384 rows in an Excel spreadsheet. Each cell has an address composed of column and row. For instance cell B25 is in column B and row 25. When you are asked to make an entry onto the spreadsheet move to the indicated cell, type the entry and press the Enter key or a Cursor key. If you make a typing error, either press Enter and then retype the entry, or simply press Esc and retype the entry. You also could enter the edit mode for that cell by pressing Function Key 2, make the corrections in the entry, and then press the Enter key.
There are 3 types of entries you will be making onto the spreadsheets. The first are "Label" entries. These are composed of character or alphabetic symbols. The second are "Numeric" entries, which are simply numbers. The third type are "Formula" entries. These are equations which can perform mathematical operations on label entries, numeric entries, and other formula entries. In the poinsettia tutorial you will be making all three types of entries.
This tutorial is designed to give your experience with spreadsheets as well as allow you to compare and contrast production costs for growing 6.5" and 5" poinsettias. Note that this spreadsheet is designed to take into account respacing (as we did with one of our 6.5" poinsettia treatments).
The first thing you should do is type your name in cell A1 so you can identify your printouts. Next, type in the crop description in cell A2. For our first crop, type in: 5" pinched 'Winter Rose Dark Red'. Make the following entries into the indicated cells: TYPE THE ENTRIES EXACTLY AS SHOWN BELOW.
| GO TO CELL | EXACT ENTRY TO BE TYPED | TYPE OF ENTRY |
|---|---|---|
| A6 | Space value (sq.ft.) | Label |
| A7 | # weeks at this spacing | Label |
| A8 | Cost/sq.ft./wk ($) | Label |
| A9 | Overhead cost ($) | Label |
| A12 | Pot cost ($) | Label |
| A13 | Substrate cost ($) | Label |
| A14 | Plant cost ($) | Label |
| A15 | Chemicals cost ($) | Label |
| A16 | Sleeve cost ($) | Label |
| A17 | Variable materials cost ($) | Label |
| C6 | 0.875 | Numeric |
| C7 | 13 | Numeric |
| C8 | 0.13 | Numeric |
| F9 | =(C6*C7*C8)+(D6*D7*D8)+(E6*E7*E8) | Formulaz |
| zA calculation will be performed which multiplies the contents of cells C6, C7 and C8, and places the answer in cell F9 (it also "sets up" the respacing cost calculations that will be entered later into cells D6, D7, D8, E6, E7, and E8). If you have done this correctly you should see a value of $1.4788 in cell F9. | ||
You already have made the first four data entries for 5" 'Winter Rose Dark Red'. Now move to cell C12 and use the table below to continue entering the data for the 5" 'Winter Rose Dark Red' pots into the indicated cells down through the sale price of $2.84. All data entry will be into the C column.
| PARAMETER | CELL | DATA FOR 5" POTS | DATA FOR 6.5" POTS--PINCHED | DATA FOR 6.5" POTS--PINCHED AND RESPACED | DATA FOR 6.5" POTS--3 PER POT |
|---|---|---|---|---|---|
| Space value (sq.ft.) | C6 | 0.875 | 1.3611 | 0.3403 | 1.3611 |
| Space value (sq.ft.) | D6 | 0 | 0 | 0.7656 | 0 |
| Space value (sq.ft.) | E6 | 0 | 0 | 1.3611 | 0 |
| # weeks | C7 | 11 (for 'Freedom Red') OR 13 (for Winter Rose Dark Red') | 13 | 4 | 12 |
| # weeks | D7 | 0 | 0 | 2 | 0 |
| # weeks | E7 | 0 | 0 | 7 | 0 |
| Cost/sq.ft./wk ($) | C8 | 0.13 | 0.13 | 0.13 | 0.13 |
| Cost/sq.ft./wk ($) | D8 | 0.13 | 0.13 | 0.13 | 0.13 |
| Cost/sq.ft./wk ($) | E8 | 0.13 | 0.13 | 0.13 | 0.13 |
| Pot cost ($) | C12 | 0.14 | 0.17 | 0.17 | 0.17 |
| Substrate cost ($) | C13 | 0.11 | 0.20 | 0.20 | 0.20 |
| Plant cost ($) | C14 | 0.60 | 0.60 | 0.60 | 1.80 |
| Chemicals cost ($) | C15 | 0.048 | 0.095 | 0.095 | 0.095 |
| Sleeve cost ($) | C16 | 0.022 | 0.044 | 0.044 | 0.044 |
| Planting time (sec) | C20 | 30 | 49 | 49 | 66 |
| Hand watering time (sec) | C21 | 10 | 19 | 19 | 19 |
| Pinching time (sec) | C22 | 2 | 2 | 2 | 0 |
| Chemicals application time (sec) | C23 | 3 | 6 | 6 | 6 |
| Automat. water & fert. time (sec) | C24 | 15 | 31 | 31 | 31 |
| Respacing time | C25 | 0 | 0 | 16 | 0 |
| Pesticide application time (sec) | C26 | 3 | 6 | 6 | 6 |
| Sleeving and harvest time (sec) | C27 | 11 | 14 | 14 | 14 |
| Hourly wage ($) | C32 | 5.30 | 5.30 | 5.30 | 5.30 |
| % loss of crop | C37 | 5 | 5 | 5 | 5 |
| Sale price ($) | C46 | 2.84 | 4.72 | 4.72 | 6.27 |
Next, move the cursor to cell F17 and write the following formula: =SUM(C12:C16)
This formula will sum the entries in cells C12 through C16 and place the answer in cell F17. If you have written the formula correctly you should see $0.9200 in cell F17. The cell content is a formula but you see the answer of the formula on the video display and also on your printouts. The spreadsheet is now complete. Formulas were already placed in other cells which performed calculations. Some of the calculations are using the two formulas which you wrote. Formulas may use data which has been entered and also may use other formulas in order to perform their calculations. If you have entered everything correctly, you should see a PLP (in F47) of $0.182. Print the spreadsheet.
Now, let's complete the spreadsheet for the 5" pinched 'Freedom Red'. First, change the crop description in A2 to 5" pinched 'Freedom Red'. Next, change the # weeks at this spacing in C7 to the correct number. If you have made the correct changes, your PLP (in F47) should be $0.421. Note that by just changing the cultivar we are growing (and reducing the required cropping time), we have more than doubled the profit per pot! Print the spreadsheet.
In the next part of the exercise, data will be entered for the 6.5" pot pinched and grown at final spacing treatment. Write in the correct crop description in A2 (6.5" pinched poinsettias). Move the cursor to cell C6 and enter 1.3611. Continue with data entry for the 6.5" pots down through the sale price. Your PLP should be $0.901. Print the spreadsheet for the 6.5" pinched pots.
Now, let's compare the "spaced at final spacing" plants with those that are to be respaced twice during production. The benefit of respacing is more efficient space use; but you must keep the space filled with something else to realize an economic benefit from respacing. Change the space value and # weeks at this spacing data in C6 and C7. Enter the additional information into D6, D7, D8, E6, E7, and E8 for the respaced pinched 6.5" pots. Make all other necessary changes (i.e. entering the required respacing labor time). Your PLP should be $1.591. By respacing (and using the space for something else), you've increased your PLP by 76%! Print the spreadsheet.
The last comparison we will make is the 6.5" non-pinched crop. Change A2 to: 6.5" single stem (3 per pot) poinsettias. Carefully change all the input data for this crop, making sure to clear out unneeded values, such as D6, D7, D8, E6, E7, and E8. Your PLP should be $1.348. Print the spreadsheet.
Now use the computer to answer the following six "What-If" questions plus question number 7. (Leave the 6.5 inch non-pinched pot crop data in place while answering the questions.)
1. If hourly wage increases 10% what will happen to PROFIT OR LOSS PER POT (PLP) in a 6.5" pot crop? (Hint: You will need to change one entry into the spreadsheet to answer this question; note that whenever you change an entry in the spreadsheet, many of the other values immediately change.) How much would you have to raise your selling price to cover the increased expense?
PLP __________ Required increase in sales price. __________
*(Change the hourly wage value back to 5.30 and continue.)
2. If crop loss doubles due to root rot, how much will your PRODUCTION COST PER POT (PCP) for 6.5" pot poinsettias rise? PCP __________
*(Change crop loss value back to 5 and continue.)
3. If plant cost increases 10% what will happen to PRODUCTION COST PER POT (PCP) for 6.5" pot poinsettias? PCP __________
*(Change the plant cost back to $1.80 and continue.)
4. If cost/sq.ft./wk increased 10% what would happen to PRODUCTION COST PER POT (PCP)? PCP _________
5. What conclusions can you now make about the effects of a 10% increase in hourly wage vs. a 10% increase in plant cost on profit?
6. What conclusions can you now make about the effects of a 10% increase in plant cost vs. a 10% increase in space (fixed overhead) cost?
7. Calculate the profit per square foot for both the 5" 'Winter Rose Dark Red' and the 6.5" pinched (placed at final spacing) poinsettias. Which crop would give you the greatest amount of profit per square foot?
Please turn in the five spreadsheets you printed along with the answers to the above seven questions. You can write the answers to these questions on the back side of your spreadsheet printouts.
Return to: Home Page
Last Modified: 27 July 2000 pathomas@uga.edu