ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT

Case Study :Pink Lady Apple Orchard is a small family run orchard in Red Hill, Victoria. Jacinta and Pete own the property, which includes 50 hectares of apple plantings. As the market price of apples sold to supermarkets and fruit shops has decreased in the past few years, causing business to stagnate, Pete and Jacinta have decided to use some of their apple supply to produce their own range of apple based products. They have set up a small market stall at the orchard where they sell products directly to the public. They also have a regular stand at local markets. The number of customers is growing as word of mouth spreads. In an effort to further expand they have negotiated with a number of small retailers to stock their products. Recently they also employed a systems developer, who set up a website which enables them to market their products online and sell directly to the public. In a further effort to expand sales Jacinta organised to have their products advertised on a number of shopping deal sites. Each site charged an upfront fee of $500.00. The Pink Lady Apple Orchards deal was advertised throughout February. The orchard offered 30% off any sale made through the deal site. After deducting a further 5% commission on sales the deal site passed the order and the payment onto the organisation. Jacinta then processed the order and delivered the products to the customer. Jacinta hoped that by offering this deal more customers would be attracted to their products. Jacinta and Pete have always delivered goods free of charge to their wholesale customers. However, with increased sales they are concerned about the increased courier charges. Pete has decided that by charging 15% delivery charge on each retail order he can recoup all the delivery charges. As Jacinta and Pete are already busy attending to the apple orchard they have employed Ruby, a local resident, two mornings a week (8 hours) to attend to all online orders. Although the number of orders is increasing, Jacinta is concerned that maybe not all products sell well enough to continue production. She has been experimenting with different flavours and products, including a new recipe for Spicy Apple Chips. Maybe she should change products or delete some products. She does not want unwanted stock accumulating in the storage sheds. She believes that the special deals she has advertised for January/February will certainly help sales, but is concerned that she has made a bad decision with regard to profitability. Jacinta has set up a spreadsheet. She has asked you to complete any relevant calculations and to use excel tools to analyse the data she has given you. She then requires you to provide a business report with regards to the effectiveness of the recently implemented online ordering system, and in particular both the overall popularity and profitability of their products and the success of the recent deals campaigns. After analysing the relevant data she wants you to consider the options with regard to future sales and to alert her to other factors that need to be considered before a final decision can be made. She is interested in reading your recommendations with regard to online sales. A spreadsheet has been provided for you containing the sales data for January to April. The report include Introduction, analysis , results of your analysis ,concluding summary and two clear recommendations that Jacinta and Pete can adopt.

Pink Lady Orchards Description Retail Price Wholesale Price Cost Price Crafter from Johathon and Granny Smith Apples, lightly carbonated, dry, elegant finish (375 ml) - 12 bottle pack Crafted from Pink Lady and Granny Smith apples, lightly carbonated, not too sweet, not too dry (375 ml) 12 Bottle Pack Crafted from juices of Jonathon, Royal Gala and Pink Lady apples. An elegant, sweet refreshing cider (375 ml) 12 bottle pack Pack of 6 sweet and 6 classic Ciders. - 12 bottle pack Non alcoholic, made from organic apples filtered and lightly carbonated (375 ml) - 12 bottle Pack crafted from organic Pink Lady apples (375 ml) Dry Cider 65.00 37.50 25.00 Classic Apple Cider 65.00 $ 40.50 27.00 Sweet Cider 65.00 $ 40.50 27.00 Mixed Cider Pack 65.00 |$ 40.50 27.00 Sparkling Apple juice Organic Apple Juice Apple Cider Vinegar Apple Chips 12 bottle Pack unfiltered, unpasteurised, gluten free and GMO free (500 mls) 2 Bottle Pack made from organic pink lady apples, crisp, crunchy, air dried (250 g packet) Box of 4 45.00 $ 45.00 $ 24.00 $ 40.00 $ 30.00 20.00 30.00 20.00 15.00$ 10.00 37.50 30.00 Delivery Costs 15% Special Discount Discount Period 30% 1/02/2018 28/02/2018 Pink Lady Orchards Description Retail Price Wholesale Price Cost Price Crafter from Johathon and Granny Smith Apples, lightly carbonated, dry, elegant finish (375 ml) - 12 bottle pack Crafted from Pink Lady and Granny Smith apples, lightly carbonated, not too sweet, not too dry (375 ml) 12 Bottle Pack Crafted from juices of Jonathon, Royal Gala and Pink Lady apples. An elegant, sweet refreshing cider (375 ml) 12 bottle pack Pack of 6 sweet and 6 classic Ciders. - 12 bottle pack Non alcoholic, made from organic apples filtered and lightly carbonated (375 ml) - 12 bottle Pack crafted from organic Pink Lady apples (375 ml) Dry Cider 65.00 37.50 25.00 Classic Apple Cider 65.00 $ 40.50 27.00 Sweet Cider 65.00 $ 40.50 27.00 Mixed Cider Pack 65.00 |$ 40.50 27.00 Sparkling Apple juice Organic Apple Juice Apple Cider Vinegar Apple Chips 12 bottle Pack unfiltered, unpasteurised, gluten free and GMO free (500 mls) 2 Bottle Pack made from organic pink lady apples, crisp, crunchy, air dried (250 g packet) Box of 4 45.00 $ 45.00 $ 24.00 $ 40.00 $ 30.00 20.00 30.00 20.00 15.00$ 10.00 37.50 30.00 Delivery Costs 15% Special Discount Discount Period 30% 1/02/2018 28/02/2018