Case Description:

Zeus owns and operates a large landscaping company in Tallahassee called Zeuss Landscaping, Inc. The sales of pallets of 5 different types of sod make up a large component of his business. He has a good feel for his costs and potential sales for the coming year and wants to get a reasonably close estimation of his profitability this year. Since he watched all of the Module 8 videos and found them very helpful, he needs to do the following in order to accurately predict his yearly profit this year.

The companys marketing department has determined that no matter what the marketing expenditures, the unit product mix among the five types of sods remains relatively constant:

• Bermuda pallet sales are approximately 88% of the sales amount of St. Augustine sales
• Centipede pallet sales are approximately 76% of the St. Augustine sales
• Zoysia pallet sales are approximately 63% of the St. Augustine sales
• Zeuss Special Mix sales are approximately 52% of the St. Augustine sales. ("Special Mix" because he creates his own unique fertilizer :)

I give you special permission to hard-code these percentages (just the percentages) into your formulas for this assignment!

By determining the probability of different sales volumes of pallets of the St. Augustine sod, your spreadsheet should automatically be able to calculate the approximate number of units sold of the other 4 varieties of sod based upon the above percentages. No partial pallets are allowed so make sure that your calculations round the calculated value up to the next higher integer.

Selling Price Calculations:

Through detailed analysis of the competition in Tallahassee, Zeus has decided that his selling price policy will be based upon the selling price of a pallet of St. Augustine ($262) with the following logic applied to calculate the other product prices:

The selling price of a pallet of Bermuda will be $8 more than the price of a pallet of St. Augustine.

Centipede grass is $12 more than a pallet of Bermuda

Zoysia will be $20 more than the Centipede

Zeuss Special Mix will be $21 more than Zoysia.

For each of the selling prices for these other 4 models, you should create a formula in cells D9:G9 that automatically calculates their prices based upon the St. Augustine price. We do this so that you only need to change 1 price in one cell and all of the other prices change appropriately along with it. There is no need to round this number.

Cost of Goods Sold (COGS) Calculations

Additionally, Zeus has learned from experience that the costs (the amount that Zeus PAYS the sod farm for the grass) of the 5 different sod varieties also track with each other. i.e. As the cost of St. Augustine rises, so to the others:

Bermuda is usually $8 more expensive to produce than St. Augustine.

Centipede is usually $8 more expensive to produce than Bermuda.

Zoysia is $8 more than Centipede.

Zeuss Special Mix is $8 more expensive to produce than Zoysia.

FIGURE 1

After you create the formulas for the Cost of Goods Sold (COGS) cells in your spreadsheet, it should automatically take this fact into account. To test it, when you vary the cost of St. Augustine, all of the other sod variety costs are automatically calculated to the values you should expect. Later down in this assignment, the cost of the St. Augustine sod will actually be determined by your expertise in your knowledge of how to simulate the variation in costs in a data table but for now just get your COGS logic in those cells correct. For the purposes of just testing out your COGS formulas, I have put $120 into the cost of St. Augustine and make sure that your other sod type cost formulas are working, that is fine. If you do this and you then get 128, 136, 144, and 152 for Bermuda, Centipede, Zoysia and Zeus's Special Mix respectively then you are good to proceed. Just like we learned in the videos and when you get to step 8 below, the data table will simulate and then overwrite this made up/temporary value of $120 for St. Augustine... so at the end of the day, it really does not matter what number you have in St. Augustine COGS cell. However, it's still always a great idea to test out your formulas! For instance, in the following example, if you manually place a value of 325 into the Units Sold for St. Augustine (cell C8) and a COGS of $120 (cell C11) your Assumptions (green) part of the simulation spreadsheet should look like Figure 1 at the right of this page. If you got the numbers that I did you are going to be in great shape moving forward to the next steps.

Projections: Cashflow directions

Please note that in cells in the "Projections" Region to the right of the page differ slightly from the book in that they correctly follow the cashflow guidelines that we learned back in Module 6. If we follow this convention then we don't have to worry about having to know when to add this number, subtract this number, add that number, subtract this number, etc. We can now just add all of the numbers together and they will work as long as our cashflow directions (positive or negative) are correct!

1. Please help with number 3 and 4 thank you!

3. On the Sod worksheet, add the formulas in cells D8:G8 to automatically calculate the units sold of Bermuda, Centipede, Zoysia and Zeus's Special Mix based upon the pallets of St. Augustine sold (in C8) per the requirements stated in the assignment description above. a. In cell D8 create a formula that multiplies cell C8 by 88%. b. In cell E8 create a formula that multiplies cell C8 by 76%. c. In cell F8 create a formula that multiplies cell C8 by 63%. d. In cell G8 create a formula that multiplies cell C8 by 52%. Hint: Remember that if your formula renders a decimal value, this would be considered a partial pallet of sod which is not allowed. For instance, if you multiply 305 by 88% this would be 268.4 pallets. In order to have your logic correct you should sound this formula UP to the nearest whole number! 4. Add formulas in cells 09:G9 to calculate the Selling Price/Pallet for Bermuda, Centipede, Zoysia and Zeus's Special Mix based upon the pallets of St. Augustine sold (in C8) per the requirements stated in the assignment description above. a. In cell D9 create a formula that adds 8 to the value in cell 09. b. In cell E9 create a formula that adds 12 to the value in cell 09. c. In cell F9 create a formula that adds 20 to the value in cell E9. Reset Progress Back to top d. In cell G9 create a formula that adds 21 to the value in cell F9. Zeus's Landscaping - Product Category:Sod22/2019 Projected Profitability Analysis via Simulation For the Year Ending Dec 31, 2019 Simulated Profitability Statistics Given Various Sod Costs 120 1301 140 150 160 St Augustine Cost Average Income Standard Deviation Maximum Income Assumptions: Sod Varieties Projections: Fido's Special Mix St. Augustine Bermuda Centipede Zoysia Minimum Income % Chance of At Least Breaking Even 0.00 9.5 11 11.75 14.5 Regarding revenues Units (Pallets) sold Selling Price/Pallet 262 Regarding variable expenses (on a per unit basis) COGS 120 Delivery 10.25 Regarding marginal fixed expenses Marketing Retained Earnings (Savings for future equipment because Zeus doesn't stearn from his future!) Payroll Estimated overhead Other items Marginal tax rate 0.00 9,900 Sales COGS Gross profit Other variable expenses: Delivery expense Contribution margin Fixed expenses: Marketing Savings (aka retained earnings) Payroll Overhead Total fixed expenses Marginal income before taxes Estimated income tax expense $ $ $ $ Potential Costs of St. Augustine 17,500 81,000 29,400 $ 19,900.00) $ (17,500.00) $ (81,000.00) $ (29,400.00) 26.75% (137,800.00) (137,800.00) Marginal net income - 137800 Unit Sales Distribution Table Cumulative Probability Related Units St. Augustine Pallet Individual Units Probability 285 0.04 310 0.06 325 0.08 345 0.1 360 0.15 385 0.19 410 0.18 435 0.12 460 0.05 485 0.03 100% Random Number Sod + 3. On the Sod worksheet, add the formulas in cells D8:G8 to automatically calculate the units sold of Bermuda, Centipede, Zoysia and Zeus's Special Mix based upon the pallets of St. Augustine sold (in C8) per the requirements stated in the assignment description above. a. In cell D8 create a formula that multiplies cell C8 by 88%. b. In cell E8 create a formula that multiplies cell C8 by 76%. c. In cell F8 create a formula that multiplies cell C8 by 63%. d. In cell G8 create a formula that multiplies cell C8 by 52%. Hint: Remember that if your formula renders a decimal value, this would be considered a partial pallet of sod which is not allowed. For instance, if you multiply 305 by 88% this would be 268.4 pallets. In order to have your logic correct you should sound this formula UP to the nearest whole number! 4. Add formulas in cells 09:G9 to calculate the Selling Price/Pallet for Bermuda, Centipede, Zoysia and Zeus's Special Mix based upon the pallets of St. Augustine sold (in C8) per the requirements stated in the assignment description above. a. In cell D9 create a formula that adds 8 to the value in cell 09. b. In cell E9 create a formula that adds 12 to the value in cell 09. c. In cell F9 create a formula that adds 20 to the value in cell E9. Reset Progress Back to top d. In cell G9 create a formula that adds 21 to the value in cell F9. Zeus's Landscaping - Product Category:Sod22/2019 Projected Profitability Analysis via Simulation For the Year Ending Dec 31, 2019 Simulated Profitability Statistics Given Various Sod Costs 120 1301 140 150 160 St Augustine Cost Average Income Standard Deviation Maximum Income Assumptions: Sod Varieties Projections: Fido's Special Mix St. Augustine Bermuda Centipede Zoysia Minimum Income % Chance of At Least Breaking Even 0.00 9.5 11 11.75 14.5 Regarding revenues Units (Pallets) sold Selling Price/Pallet 262 Regarding variable expenses (on a per unit basis) COGS 120 Delivery 10.25 Regarding marginal fixed expenses Marketing Retained Earnings (Savings for future equipment because Zeus doesn't stearn from his future!) Payroll Estimated overhead Other items Marginal tax rate 0.00 9,900 Sales COGS Gross profit Other variable expenses: Delivery expense Contribution margin Fixed expenses: Marketing Savings (aka retained earnings) Payroll Overhead Total fixed expenses Marginal income before taxes Estimated income tax expense $ $ $ $ Potential Costs of St. Augustine 17,500 81,000 29,400 $ 19,900.00) $ (17,500.00) $ (81,000.00) $ (29,400.00) 26.75% (137,800.00) (137,800.00) Marginal net income - 137800 Unit Sales Distribution Table Cumulative Probability Related Units St. Augustine Pallet Individual Units Probability 285 0.04 310 0.06 325 0.08 345 0.1 360 0.15 385 0.19 410 0.18 435 0.12 460 0.05 485 0.03 100% Random Number Sod +