Please see attached
Microsoft Word - F20 Choc Shop Question.docx The Chocolate Shop Term Project - Spring 2021 (V1.2) Introduction This is an individual term project for all ENTR3025 students. The project is MS Excel based and is broken into two (2) parts, each having its own rubric, worth an aggregate total of 25% of your final grade. The completion of this project will require you to utilize a wide range of the varied business knowledge you have learned throughout your program. Also note that your final project solution, in MS Excel, will serve as the outline for the required financial model you will need to complete your mandatory ENTR3000 course next term. As a side note, this project is a great opportunity to learn, use or build upon your MS Excel skills which are always in high demand in the business world. The project scenario, all data points and a variety of functional considerations are included below. There is intentionally a lot of detail here, take your time to review the material and read it over carefully. You will need to incorporate all of these details in your final MS Excel solution! Organization will be crucial to your success. The Scenario Ms. Susan Sweet is planning on starting a new business, The Chocolate Shop (CS), which will be established as a sole proprietorship, specialty chocolate shop that retails delicious chocolate treats. She is planning on a soft opening at the beginning of July 2021. She has already acquired a lease on a small, quaint building in St. Jacobs that she shares with two other boutiques of complementary items. She does not plan to produce any chocolate herself but rather buy products from local suppliers with an emphasis on Fair Trade chocolate. She is looking to you to create a detailed MS Excel financial model that will help her analyze her start-up financial projections. * Please note that a good financial model allows for sensitivity analysis to be performed. Sensitivity analysis simply involves having the ability to change an embedded assumption or two within your model to see what the overall impact on the final numbers will be. In order to ensure your final MS Excel model has that ability, make certain that all of the various worksheets you create are “connected”, so that changing an input value in one worksheet will flow through to all other affected worksheets seamlessly. For example: changing a single assumption in your sales forecast worksheet will automatically update your overall revenue projections within your income statement worksheet. Hope that makes sense for now, but rest assured we will talk more about this requirement throughout the term. Sales Susan plans to sell her Chocolate Shop products through both her “brick & mortar” retail store as well as online using a Shopify solution. The Chocolate Shop will carry two (2) basic product categories: 1) premium, (or artisanal), chocolates that are hand manufactured based on her order 2) assorted chocolates which she purchases by the kilogram. The exact mix varies from order to order but will always include a combination of creams, caramels, crisps and crunches. Based on her preliminary forecasting techniques, Susan currently expects her first fiscal year sales to total $295,000 inclusive of seasonality projections listed below in Figure #1. Figure #1: Sales Seasonality Jul-21 Aug-21 Sep-21 Oct-21 Nov-21 Dec-21 Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 5% 5% 2% 13% 11% 16% 2% 25% 6% 10% 2% 3% Susan expects 70% of her overall sales will be through her retail store and 30% will be online. Susan will use the same pricing for both the retail and online storefronts. The proposed CS pricing list is: · Individual premium chocolate boxed and tied with ribbon: $3.35 · Gift basket of 20 premium chocolates: $75.95 · Bulk assorted chocolates: $5.10 for 100g · Box of 12 assorted chocolates: $10.95 For simplicity purposes, Susan will assume that her overall product mix will remain constant each month throughout the year, however she does expect the mix to be different between the retail and online channels. Her anticipated overall product mix is shown in Figure #2. Figure #2: Product Mix Product Retail % Online % Individual Premium Chocolate (ea.) 30% 35% Gift Basket of Premium Chocolates (ea.) 5% 10% Bulk Assorted Chocolates (100g) 40% 10% Box of Assorted Chocolates (ea.) 25% 45% From a payment processing perspective, Susan predicts that 40% of the store customers will pay by cash and the other 60% will pay by credit or debit card, (which on average costs the CS 3.0% of the sale). Note that Susan combines her credit card fees with Bank and Interest Charges for comparing to the Industry Financial reports. This means she is including this expense in her operating expenses. (i.e. it will not be broken out separately as a finance charge at the bottom of the Income Statement) Online sales will all be 100% credit card sales. Susan is using Shopify as her platform of choice, which charges 2.9% on total sales plus $0.30 per transaction. That covers the credit card or debit fees and the Shopify commission. Note that the credit card fee % is in the list of inputs and is calculated on the CFF statement Susan will be using the built-in interface to Canada Post for shipping and all shipping costs are billed to the customer at the time of purchase. Susan expects her average online sale will be $70. (therefore the number of transactions can be estimated by dividing total sales estimates by the average sales value. Use the Roundup Excel function to round up to the next highest whole number). Susan has assumed Year 2 total sales will increase by 10%. All other sales factors will remain the same as Year 1, noting that the July 2022 sales figures are required to forecast June 2022 inventory purchases. Cost of Sales (Direct Costs) Susan has located some wonderful suppliers who make the chocolate products for her. Turnaround is quick. She will place the order, receive it, and pay for it (COD) one month in advance. Her accountant has told her the Cost of Goods Sold figure is only stock, (no labour), and inventory adjustments. She calculates that her Cost of Goods Sold for her chocolate is as follows: · Individual premium chocolate (each): $1.69 · Bulk assorted Chocolates (per 100g): $2.82. · Note that a box of 12 assorted chocolates has an average weight of 200g. The Chocolate Shop packages each premium chocolate in a gift box and ties it with a ribbon. They also assemble the boxes of chocolate and the gift baskets when they are ordered. The cost of the packaging and baskets are considered part of her brand and are included in overall advertising expenses. Start-Up Costs During May and June 2021, Susan estimates she will pay out the following for start-up costs: · She will pay $80 to register her business. · She will join the Chamber of Commerce in June at an annual rate of $400. · She will buy opening inventory, in June, to cover her first month’s sales. (inventory) · Her share of the leasehold improvements to the St Jacobs store will be $6,000 (see Lease & Location section for more details). This is a depreciable item. · The landlord gave her two months of free rent to allow for these improvements. Rent will start July 2021. · Lawyer fees for examining her lease agreement will be $250. · Racking and display cases, purchased used, will cost $3,000. These are depreciable items. · Susan plans on setting up her Shopify site herself but will buy a theme for $200 to ensure the site is attractive and visually consistent. · Susan must purchase an Internet domain for her site for $20. · A computer with monitor and printer and all needed software for $3,000. The hardware, valued at · $2,500, is a depreciable item. The remaining value is for the software which is an expense. · A retail Point of Sale (POS) system for $2,000 (Computers). This is a depreciable item. · Her Internet Service Provider (ISP) has a one-time installation charge of $150. · All utility installation/setup charges will total $450. · Some initial marketing and advertising and logo design also will cost her $850 · Her outdoor signage and sign permit cost $1,500. Memberships & Professional Fees Susan decided to join the local Greater K-W Chamber of Commerce (already listed in Startup Costs) and some of its subcommittees and the renewal will be $400 per year for membership payable in July each year. Plus, she plans to join the Retail Confectioners International Association in September 2021 and its $350 payable in September each year—she hopes it will be well worth the cost. You tell her you will combine the membership fees (Chamber and Confectioners’ Assoc.) with professional fees (lawyer & accountant) for easy comparison to the Industry Canada reports. Her other professional fees are estimated at $450 per year. She decides you should put them in April ($450), as most of it will be for preparing her taxes. Depreciation (or Amortization) In Figure #3 Susan outlined her assets and depreciation. She wants to always show the original value of the items and accumulated depreciation on the Balance Sheet to show the Net Capital Asset Value. You advise her to use the CRA depreciation method, (https://www.canada.ca/en/revenue-agency/services/forms- publications/forms/t2125.html), as a guide. Susan’s accountant advises that depreciation should start in the year the asset was acquired. Figure #3: Asset Schedule (Start-up Purchases) Asset Description Original Value (FMV) Date Purchased Class # Class Rate (%) Estimated Life Computer equipment (hardware & POS) $4,500 May-2021 50 55% 2 years Fixtures & equipment $3,000 June-2021 8 20% 5 years Leasehold Improvements $6,000 May-2021 8 20% 5 years (term of lease) Advertising Susan uses various methods of marketing. She is hoping to increase the business side by setting up her display at the Chamber of Commerce meetings throughout the year starting in September. Total cost will be $180 per month and she indicated it should just be spread evenly over 10 months skipping July and August. Her web presence is anchored by her Shopify site. She has subscribed to the Basic Service which she is budgeting for $45 per month. The actual cost is in US$29 plus taxes, but she feels C$45 more than compensates for the taxes and exchange rate. While she setup the site herself she expects she will need professional help to improve her Search Engine Optimization (SEO) which she will do in October 2021, in preparation for the holiday season, at a cost of $500 (paid 50% upfront in October and 50% upon completion in November). She will also need to renew her Domain annually ($20 – advertising expense). In May 2021, she intends to get a partial vehicle wrap