Part A – Understanding the Case Study >>>make excel spreadsheet to…
QuestionAnswered step-by-stepPart A – Understanding the Case Study >>>make excel spreadsheet to…Part A – Understanding the Case Study >>> make excel spreadsheet to predict the income and cost of Fred’s Nursery: Fred’s Nursery, a flower grower, plants a bed of orchids in a shade house at a setup cost of$5000. Fixed annual operating costs are $1000 per bed, while variable costs during producing years are 30% of sales receipts. Due to changingweather conditions, water costs fluctuate each year: $2000, $3000, $5000, $5000, $5000, $6000, $6000 & $5000 respectively. The bed will not start producing during the first year. In the second through eighth years it should produce, respectively, 150, 500, 1900,2000, 2200, 2000, and 1200 dozen orchidsprays. If 2000 dozen or more orchids are produced in a year then an extra staff hand needs to be employed, costing $20000 for that year. Orchids currently sell for $12 per dozen, which has currently been increasing at 4% per year. Based on aforementioned case study, construct a spreadsheet model that shows a breakdown of anticipated annual income and costs over the eight years. Task 1a. Download Orchids.xlsx file Task 1b. Open the file. The file contains the information mentioned in the case study as shown in Figure 1. Figure 1. Fred’s Nursery Spreadsheet Part B – Operator, Formula and Function Task 2a. Project the orchid sales for next 8 years in row 22 to find the sales value from year provided in row 20. Use vlookup function on Anticipated Yearly Sales and Water Cost table to acquire anticipated orchid sales. The expected output is: Q2a: Construct a formula to calculate B22B22= Task 2b. Calculate the orchid price per year in row 23. Orchids currently sell for $12 per dozen, which has currently been increasing at 4% per year. The expected output is: Q2b: Construct a formula to calculate C23C23 = Task 2c. Calculate the income from orchidsales in row 25. The income is calculated as Orchid Sales (row 22) * Respective Year’s Price (row23). The expected output is: Q2c: Construct a formula to calculate B25B25= Task 2d. Calculate cumulative income of each year in row 27. The cumulative income shows total income since first year. The expected output is: Q2d: Construct a formula to calculate C27C27= Task 2e. Put the setup cost in cell B31 and set the fixed cost in row 32. Do not hard code or type the value. The information about setup cost and fixed cost are provided in cell B11 and B12 respectively. The expected output is: Q2e: Construct a formula to calculate B31 and B32 respectively B31=B32= Task 2f. Calculate yearly variable cost in row 33. The variable cost is calculated from yearly income. The percentage of variable cost is provided in cell B13. The expected output is: Q2f: Construct a formula to calculate B33B33= Task 2g. Use IF function to determine whether extra staff is needed in row 34. Extra staff is needed if 2000 dozen or more orchids are produced in a year. The cost of extra staff is$20,000.00 The expected output is: Q2g: Construct a formula to calculate B34B34= Task 2h. Project water costs for next 8 years in row 35. Use vlookup function on Anticipated Yearly Sales and Water Cost table to get anticipated water costs. The expected output is: Q2h: Construct a formula to calculate B35B35 = Task 2i. Calculate yearly total cost in row 25. Total cost is the sum of setup cost, fixed cost, variable cost, extra staff cost and water cost. Use SUM function to calculate total cost. The expected output is: Q2i: Construct a formula to calculate B36B36 = Task 2j. Calculate cumulative cost of each year in row 38. The cumulative cost shows total cost since first year. The expected output is: Q2j: Construct a formula to calculate C38C38 = Task 2k. Calculate yearly profit/loss in row 40. Profit/loss is calculated as Income From SalesTotal Cost. The expected output is: Q2k: Construct a formula to calculate B40B40 =Task 2l. Calculate yearly cumulative balance in row 42. Cumulative balance is calculated as Cumulative Income – Cumulative Cost. The expected output is: Q2l: Construct a formula to calculate B42B42 = Part C – Validation Task 3a. Add validation to Current Price/Dozen. The price must be between 8.00 and 19.99 inclusive. Use suitable error message for unacceptable value.Adding validation steps:Click on Current Price/Dozen cell (B8)Click Data tab -> Data Validation Pick decimal on Allow drop down box and between on Data drop down box. Put 8 in Minimum text box and 19.99 in Maximum text box. Click Input Message tab. Fill up the information as in following figure: Click Error Alert tab. Fill up the information as in following figure: Click OK. It show the input message if the cell is selected. Check whether the validation works properly. Key in 5 in B8. Following error message should appear Task 3b. Add validation to Price Increase. The increase must be between -20% and 20% inclusive. Remember to specify minimum and maximums as fractions of 1 not percentages. Use suitable error message for unacceptable value.Task 3b Sample Solution:`Task 3c. Add validation to Fixed Cost. The cost must be a whole number between 0 and 5000. Use suitable error message for unacceptable value. Task 3c Sample Solution: Task 3d. Add validation to Sales Requiring More Staff. Sales for more staff must be one of the values (a list): 1000, 1500, 2000, 2500, or 3000. Use suitable error message for unacceptable value. Fred’s Nursery New Orchid Shade House Budget Current Price / Dozen $12.00 Anticipated Yearly Sales and Water Costs Price Increase 4% Year Sales Water Costs 1 0 $2,000.00 Setup Cost $5,000.00 2 150 $3,000.00 Fixed Cost $1,000.00 3 500 $5,000.00 Variable Cost 30% 4 1900 $5,000.00 5 2000 $5,000.00 Sales Requiring More Staff 2000 6 2200 $6,000.00 Extra Wages / Year $20,000.00 7 2000 $6,000.00 8 1200 $5,000.00 Year 1 2 3 4 5 6 7 8 INCOME Orchid Sales Price Increase Income from Sales Cumulative Income COSTS Setup Fixed Yearly Variable Yearly Extra Staff Water Cost Total Cost Cumulative Cost Profit / Lost Cumulative Balance Computer ScienceEngineering & TechnologyNetworkingCSE 1902SCShare Question
