The Chocolate Shop - F23 Overview Susie is determined to start her own chocolate business. She has some experience in the industry and is now signed up for a "Professional Chocolatier Program" running from Sep 2023 to Dec 2024. She will operate The Chocolate Shop (CS) in KW as a sole proprietorship specialty chocolate shop that manufactures and retails delicious chocolate morsels. She is planning on a soft opening in January 2024. She lives in a small apartment so plans to rent a unit with a commercial kitchen. She will produce her chocolate delights with Fair Trade chocolate. She is looking to you to complete an Excel model based on her estimates and assumptions that will help her produce her start-up financial projections. You advise her to give you her estimates and you'll build a set of "input sheets" that record all the figures, do all the calculations and they will feed into the financial model of a Cash Flow Forecast (CFF), a Forecasted Income Statement (FIS) and a Forecasted Balance Sheet (FBS) for one year. All in one workbook. Remember, everything on the statements will be double-entry!! Note: A sole proprietorship MUST use the calendar year for tax purposes. Coincidentally this is convenient. Taxes will not be included in this plan. Your Task - Financial Model You are producing a customer focused, professional looking Excel based financial model for one year to be used for business planning. It is not just typing the data into a financial statement template-the input sheets provide space to collect the data, make any required calculations, state any assumptions or sources, and then feed into the three financial statements that contain no hardcoding. It must be formatted to be printed on 8 " 11 paper. Formatting will include clear indications as to where Susie can enter/change data to help her write her business plan. It will consist of the following tabs: - (1) Cover Sheet will include introductory information, plus: Table of Contents - explanation of what is on each tab Legend (what the different colours/formatting mean) (Statement of Proof of Work - as a student this is included) - (2,3,4) The three Financial Statements (CFF, FIS, FBS) each on a separate tab. - Input Sheets: (5) Startup Phase: Startup items should be divided into Expenses, Capital Expenditures, Inventory and Inflows of Monies. There should be an initial Balance Sheet. (6) Sales Projections, Inventory - Sales for each channel by month and customer payment method. Raw material inventory by month. Cost of Good Sold factor for materials. (7) Capital Assets \& Depreciation - straight-line method (8) HR expenses (9) Financing (loan details and horizontal calculated table showing principal and interest payments each month, line of credit rate and limit, and amount of desired monthly contingency) (10) Other Expenses - marketing, location, delivery etc. (11,12) Industry Data for benchmarking - \% of Sales and $ of sales ( 2 tabs) Other Case Information From Susie Susie envisions her business to be a small manufacturing site for chocolate treats from a commercial grade kitchen. She has set $250,000 as her ambitious sales goal for year 1 . She plans to distribute through the following channels: - Factory Storefront ( 20% of sales) - mostly pickup from a small counter ( 50% cash \& 50% credit cards) - Tradeshows/Popups/Markets ( 40% of sales)- ( 75% credit cards \& 25% cash) - Larger Preordered Orders- B2B / Weddings (40\% of sales) - corporate sales to other stores or businesses as gifts and Weddings as favours for guests . This channel is 80% credit cards \& 20% of sales are net 30 days paid by cheque or EFT with no additional costs) Note: She receives the credit card monies back in the same month but with a 2.5% fee. She is going to give trade credit to some of her bigger clients with net 30 days terms so that revenue will be received in the month after the initial sales. At this point, she is not expecting problems in collecting these funds but will need to send out electronic statements each month. Sales Patterns Susie's initial research is that fine chocolate sells for up to $30 a pound and lower quality for $7$10 a pound. She is going to use $15 as her average price for chocolate. Susie estimates her first-year sales, from all channels, will total $250,000. Figure 1 shows the estimated seasonality breakdown by sales channel for each month. She has also included Jan 2025 (month 13) seasonality since it is needed to calculate inventory purchases. Susie expects her second-year total sales will increase by 25% as people get to know about her fine products. She will assume the sales mix will also remain the same (20%/40%/40%) in the second year. (Note: You need the Jan. 2025 sales amount estimate to calculate purchases in Dec. 2024) Cost of Sales for Materials (direct costs) She has located some wonderful suppliers who have a full range of needed ingredients and packaging. Turnaround is quick. She will place the order, receive it, and pay for it (COD) one month in advance of her sales. She calculates that her average Cost of Goods for materials is 44%. Her accountant told her the inventory shrinkage and sampling costs are already included in her Cost of Goods Sold figure. She is assuming that all of the employee costs will be added to the Cost of Goods Sold. (stated separately) Susie fells she needs some safety stock. She will purchase an additional $1,500 in inventory during start-up. She will purchase an addition $2,000 of inventory in September 2024 in preparation for the busy season. F23 The Chocolate Shop v1.0 Page 2 of 10 During her start-up, Susie estimates she will pay out the following for start-up costs: - $60 to register her sole proprietorship business. - Membership during start-up to join the Chamber of Commerce will be $200. She will pay her renewal in July 2024. - Opening inventory, to cover her first month's sales (Jan) plus extra items (call it safety stock) for $1,500 in inventory to provide a nice selection of ingredients. - Her share of the leasehold improvements done during start-up will be $6,000 (see Lease and Location section for more details). This is a depreciable capital item. - Rent is waived during start-up. The landlord gave her two months of free rent to allow for improvements (leasehold improvements). She only pays her monthly rent starting January 2024-no first and last will be required. - Professional Fees: Lawyer fees for examining her lease agreement and making suggestions will be $300. The accountant/bookkeeper advice to help set up books will be $250. - Racking and other manufacturing items will cost $30,000. These are depreciable items. - Computer equipment (hardware and POS) will be $5,000. - Software will be expensed (not depreciated) at $400. - Internet Service Provider (ISP) one-time installation charge of $125. - Internet domain for $20. It is needed for a basic web presence. - All one-time utility installation/setup charges will total $450. - Utilities for the start-up period will be $400. - Some initial marketing and advertising and logo design will cost her $850 (expense) - Trade Booth equipment (tables, canopy, signage etc.) is estimated at $600 and will be expensed. - Her outdoor signage and sign permit will cost $1,500. - Training will be $1,500 (Chocolatier courses, food handling for her and her sister, etc.) - Miscellaneous Startup - $2,000 Depreciation (or Amortization) In Figure \#2, Suzie outlined the information to calculate her depreciation. On the Balance Sheet she wants to always show the original value of the items and then separately the accumulated depreciation to show the Net Capital Asset Value. You advise her to use the straight line depreciation method. Susie's accountant advises that depreciation should start in the year she starts the business operations NOT in the startup period. Figure \#2: Asset Schedule F23 The Chocolate Shop v1.0 Page 3 of 10 Memberships \& Professional Fees As stated, Susie is going to join the local Greater K-W Chamber of Commerce during her startup. Her next cost will be the $550 per year for membership payable in July 2024. Plus, she plans to renew her membership in the Fine Chocolate Industry Association (FCIA) for $200 in September 2024. She hopes the learning opportunities and networking will be well worth the cost. Her other ongoing professional fees (lawyer \& accountant) are estimated at $550 per year. She decides you should put them in April as most of it will be for preparing her taxes. You tell her you will add the membership fees (Chamber and FCIA) and professional fees together for easy comparison to the Industry Canada reports. Marketing Expenses She has decided to use various methods of marketing. She is hoping to increase the business (B2B) sales by setting up her display at the Chamber's meetings etc. throughout the year. She wants to pitch Employee Appreciation and new client gifts! The cost will be $100 per setup. She will set up her display in March, April, September, November, and December. She plans to attend local tradeshows and markets and she is estimating each one costs $100$200. That is the rental fee and any other incidentals to attend. Figure \#3 shows the Chamber and other tradeshow costs. Figure \#3: Tradeshow Costs Providing samples will help sell her product and she thinks that will be included in her Cost of Goods and will not be an additional marketing expense. Packaging will be included in the 44% Cost of Goods too! Salaries for Employees Susie will work in the production of the business for about 10 days per month. She is also the main sales and delivery person and also does the purchasing and accounting. She estimates it will be 50+ hours per week. She is now single (again) with no dependents but at 32 years of age, has the drive to start her own business. She does not have any government benefit costs during the year and has no health and dental benefits plans. She cannot expense a salary as it's a sole proprietorship but will take out $2,000 a month starting in August 2024. Any monies she takes out is a draw against the equity of the business and will appear on the Balance Sheet in the Equity section (and goes in CFF of course). She will pay her one "full-time" (FT) employee, her sister, \$22 per hour starting in March 2024 and her salary will be based on 40 hours per week. We assume she will work 4 weeks per month. All of her salary will be included in the Cost of Goods Sold. She is salaried so the hours or pay does not fluctuate. In addition, the payroll expenses and minimal benefits she pays to her add up to an additional 22% of her salary per month. She plans to hire part-time (PT) workers and pay them $20.00 per hour which is above the local living wage. They will work as needed and Figure 4 shows her projections for hours. Their payroll expenses are an additional 18% of their wages. All employee labour costs (wages + benefits) are to be included with Cost of Goods Sold. On the FIS, ensure the inventory/materials and HR are shown separately. Figure 4: Projected Hours Worked by Part-Time Employees for 2024 Lease and Location Expenses Susie has secured this location with a 5-year lease. When she moved in, she made a deal with the landlord to make the leasehold improvements. Her share to get the premises ready will be $6,000 in leasehold improvements all of which will be completed before her starting date. Depreciation will start with her projected opening date. Her rent is $23/square foot for her (1515)225sqft of space . In addition, she pays an additional $2.50/ square foot for the CAM. The CAM covers a common washroom, use of two labelled parking spots, and all interior and exterior maintenance. Her other monthly location costs are minor maintenance and cleaning costs of $200/ month and Content Insurance for $90 Delivery Expenses Instead of paying herself mileage, Susie is bringing her own van into the business during the start-up. She plans to deliver locally. She figures that way she can deduct vehicle expenses. It is an older white cargo van with a FMV of $5,000. Her accountant said to show it coming the beginning of January 2024. The van has a projected useful life of 5 years and no salvage value. She is going to put all other vehicle maintenance and van operating costs in a Delivery expense account. She figures that will amount to $300 per month plus extra delivery fees of $200 in Oct., Nov. and Dec. as they are her busy months. She will keep the "extra" delivery fees on a separate line on the input sheets to ensure she can easily adjust as needed. Other Monthly Expenses Other monthly cash expenses for 2024 are estimated to be: Financing Arrangements Susie has a good working relationship with her local bank. She already has a secured personal Line of Credit against her house with a $100,000 limit on it. She pays 8% interest on it anytime she uses it. For this forecasting, she assumes she pays back the FULL principal in the next month and again borrows anything she needs (as opposed to keeping a running total). The monthly interest incurred is also paid in that following month. She realizes this is slightly overestimating the interest as she may have only borrowed it for 5 or 6 days but finds it is the best way to estimate the cost of borrowing. She insists you build in a contingency (or "cushion") amount of $1,500 cash on hand at the end of each month on her CFF reconciliation. If her cash on hand plus the contingency falls below this $1,500, she will borrow against the line of credit. Susie has $60,000 saved to invest in the business. She puts it in during the start-up period. Her parents will give her a loan of $20,000. They are charging her 5% per year and she has agreed to pay them back over 18 months with equal monthly payments consisting of principal and interest (PMT). Susie will receive the money in January 2024 and will make her first payment the following month (February 2024). To make any comparisons easier later on, you tell her you will group financial costs together one after the other on the CFF and total them on the FIS (that will include: bank charges, interest on the loan and any interest incurred on the line of credit, and all credit card charges) and call them Financial Costs and put them in the Operating Expenses on the FIS rather than after the Net Income line. (Note: this is the way the Industry Financial Data does it) Benchmarking Against Industry Financial Performance Data You have also recommended that on her FIS, you setup a calculated "Expenses as a \% of Sales" column. She can use this to quickly benchmark to the Industry Canada's Financial Performance Data. You'll export corresponding sheets into the last tabs in her workbook for Rubric \#2 where you add the \% of Sales from NAICS for comparison. You recommend you include two in the workbook. Use the following parameters for the two reports: - Manufacturing Confectionery from purchased chocolate - NAICS 311352 - Revenue range $30K$5M - One shows \% of sales and the other one shows thousands $ of sales - ALL Businesses for ALL of Canada. - Distribute the Quartile Sales by Total Revenue not Profit Margins - Use the quartile that corresponds to the level of sales Other Notes - Sales taxes and input tax credits (ITCs for HST) are important factors in retail sales. However, for these projections, Susie has taken her accountant's advice and has excluded these from her estimated sales and expenses. She will not build in tax remittance estimates. - Government benefit expenses (CPP, etc.-the 18% and 22% ) for her employees, will show in the month incurred not the month in which they are remitted. Case Requirements Suzie realizes these numbers are all guesses but it's a start. She now wants to have you set up a set of Excel financial statements to allow her to do more analysis and planning. She is looking to you to create a Workbook consisting of the Worksheets in Figure \#5. I would use this chart as a checklist. She does not know Excel like you do so she has asked that you build the model so she can easily see where she can change input data and print it out for review. Each tab must be formatted to fit on 811 paper but can be either landscape or portrait orientation. She would prefer they don't flow onto a second page but they must be easily readable!! The tabs must all look professional. For example, monthly columns should have equal width; everything should be readable when printed in B\&W; and, of course, spell checked. F23 The Chocolate Shop v1.0 Page 6 of 10 You will be submitting the assignment in 2 parts and the two pieces are worth a total of 25% of your term mark. There is a bonus for successful completion of the 2nd submission that balances after ad hoc changes are made. F23 The Chocolate Shop v1.0 Page 7 of 10 The Chocolate Shop - F23 Overview Susie is determined to start her own chocolate business. She has some experience in the industry and is now signed up for a "Professional Chocolatier Program" running from Sep 2023 to Dec 2024. She will operate The Chocolate Shop (CS) in KW as a sole proprietorship specialty chocolate shop that manufactures and retails delicious chocolate morsels. She is planning on a soft opening in January 2024. She lives in a small apartment so plans to rent a unit with a commercial kitchen. She will produce her chocolate delights with Fair Trade chocolate. She is looking to you to complete an Excel model based on her estimates and assumptions that will help her produce her start-up financial projections. You advise her to give you her estimates and you'll build a set of "input sheets" that record all the figures, do all the calculations and they will feed into the financial model of a Cash Flow Forecast (CFF), a Forecasted Income Statement (FIS) and a Forecasted Balance Sheet (FBS) for one year. All in one workbook. Remember, everything on the statements will be double-entry!! Note: A sole proprietorship MUST use the calendar year for tax purposes. Coincidentally this is convenient. Taxes will not be included in this plan. Your Task - Financial Model You are producing a customer focused, professional looking Excel based financial model for one year to be used for business planning. It is not just typing the data into a financial statement template-the input sheets provide space to collect the data, make any required calculations, state any assumptions or sources, and then feed into the three financial statements that contain no hardcoding. It must be formatted to be printed on 8 " 11 paper. Formatting will include clear indications as to where Susie can enter/change data to help her write her business plan. It will consist of the following tabs: - (1) Cover Sheet will include introductory information, plus: Table of Contents - explanation of what is on each tab Legend (what the different colours/formatting mean) (Statement of Proof of Work - as a student this is included) - (2,3,4) The three Financial Statements (CFF, FIS, FBS) each on a separate tab. - Input Sheets: (5) Startup Phase: Startup items should be divided into Expenses, Capital Expenditures, Inventory and Inflows of Monies. There should be an initial Balance Sheet. (6) Sales Projections, Inventory - Sales for each channel by month and customer payment method. Raw material inventory by month. Cost of Good Sold factor for materials. (7) Capital Assets \& Depreciation - straight-line method (8) HR expenses (9) Financing (loan details and horizontal calculated table showing principal and interest payments each month, line of credit rate and limit, and amount of desired monthly contingency) (10) Other Expenses - marketing, location, delivery etc. (11,12) Industry Data for benchmarking - \% of Sales and $ of sales ( 2 tabs) Other Case Information From Susie Susie envisions her business to be a small manufacturing site for chocolate treats from a commercial grade kitchen. She has set $250,000 as her ambitious sales goal for year 1 . She plans to distribute through the following channels: - Factory Storefront ( 20% of sales) - mostly pickup from a small counter ( 50% cash \& 50% credit cards) - Tradeshows/Popups/Markets ( 40% of sales)- ( 75% credit cards \& 25% cash) - Larger Preordered Orders- B2B / Weddings (40\% of sales) - corporate sales to other stores or businesses as gifts and Weddings as favours for guests . This channel is 80% credit cards \& 20% of sales are net 30 days paid by cheque or EFT with no additional costs) Note: She receives the credit card monies back in the same month but with a 2.5% fee. She is going to give trade credit to some of her bigger clients with net 30 days terms so that revenue will be received in the month after the initial sales. At this point, she is not expecting problems in collecting these funds but will need to send out electronic statements each month. Sales Patterns Susie's initial research is that fine chocolate sells for up to $30 a pound and lower quality for $7$10 a pound. She is going to use $15 as her average price for chocolate. Susie estimates her first-year sales, from all channels, will total $250,000. Figure 1 shows the estimated seasonality breakdown by sales channel for each month. She has also included Jan 2025 (month 13) seasonality since it is needed to calculate inventory purchases. Susie expects her second-year total sales will increase by 25% as people get to know about her fine products. She will assume the sales mix will also remain the same (20%/40%/40%) in the second year. (Note: You need the Jan. 2025 sales amount estimate to calculate purchases in Dec. 2024) Cost of Sales for Materials (direct costs) She has located some wonderful suppliers who have a full range of needed ingredients and packaging. Turnaround is quick. She will place the order, receive it, and pay for it (COD) one month in advance of her sales. She calculates that her average Cost of Goods for materials is 44%. Her accountant told her the inventory shrinkage and sampling costs are already included in her Cost of Goods Sold figure. She is assuming that all of the employee costs will be added to the Cost of Goods Sold. (stated separately) Susie fells she needs some safety stock. She will purchase an additional $1,500 in inventory during start-up. She will purchase an addition $2,000 of inventory in September 2024 in preparation for the busy season. F23 The Chocolate Shop v1.0 Page 2 of 10 During her start-up, Susie estimates she will pay out the following for start-up costs: - $60 to register her sole proprietorship business. - Membership during start-up to join the Chamber of Commerce will be $200. She will pay her renewal in July 2024. - Opening inventory, to cover her first month's sales (Jan) plus extra items (call it safety stock) for $1,500 in inventory to provide a nice selection of ingredients. - Her share of the leasehold improvements done during start-up will be $6,000 (see Lease and Location section for more details). This is a depreciable capital item. - Rent is waived during start-up. The landlord gave her two months of free rent to allow for improvements (leasehold improvements). She only pays her monthly rent starting January 2024-no first and last will be required. - Professional Fees: Lawyer fees for examining her lease agreement and making suggestions will be $300. The accountant/bookkeeper advice to help set up books will be $250. - Racking and other manufacturing items will cost $30,000. These are depreciable items. - Computer equipment (hardware and POS) will be $5,000. - Software will be expensed (not depreciated) at $400. - Internet Service Provider (ISP) one-time installation charge of $125. - Internet domain for $20. It is needed for a basic web presence. - All one-time utility installation/setup charges will total $450. - Utilities for the start-up period will be $400. - Some initial marketing and advertising and logo design will cost her $850 (expense) - Trade Booth equipment (tables, canopy, signage etc.) is estimated at $600 and will be expensed. - Her outdoor signage and sign permit will cost $1,500. - Training will be $1,500 (Chocolatier courses, food handling for her and her sister, etc.) - Miscellaneous Startup - $2,000 Depreciation (or Amortization) In Figure \#2, Suzie outlined the information to calculate her depreciation. On the Balance Sheet she wants to always show the original value of the items and then separately the accumulated depreciation to show the Net Capital Asset Value. You advise her to use the straight line depreciation method. Susie's accountant advises that depreciation should start in the year she starts the business operations NOT in the startup period. Figure \#2: Asset Schedule F23 The Chocolate Shop v1.0 Page 3 of 10 Memberships \& Professional Fees As stated, Susie is going to join the local Greater K-W Chamber of Commerce during her startup. Her next cost will be the $550 per year for membership payable in July 2024. Plus, she plans to renew her membership in the Fine Chocolate Industry Association (FCIA) for $200 in September 2024. She hopes the learning opportunities and networking will be well worth the cost. Her other ongoing professional fees (lawyer \& accountant) are estimated at $550 per year. She decides you should put them in April as most of it will be for preparing her taxes. You tell her you will add the membership fees (Chamber and FCIA) and professional fees together for easy comparison to the Industry Canada reports. Marketing Expenses She has decided to use various methods of marketing. She is hoping to increase the business (B2B) sales by setting up her display at the Chamber's meetings etc. throughout the year. She wants to pitch Employee Appreciation and new client gifts! The cost will be $100 per setup. She will set up her display in March, April, September, November, and December. She plans to attend local tradeshows and markets and she is estimating each one costs $100$200. That is the rental fee and any other incidentals to attend. Figure \#3 shows the Chamber and other tradeshow costs. Figure \#3: Tradeshow Costs Providing samples will help sell her product and she thinks that will be included in her Cost of Goods and will not be an additional marketing expense. Packaging will be included in the 44% Cost of Goods too! Salaries for Employees Susie will work in the production of the business for about 10 days per month. She is also the main sales and delivery person and also does the purchasing and accounting. She estimates it will be 50+ hours per week. She is now single (again) with no dependents but at 32 years of age, has the drive to start her own business. She does not have any government benefit costs during the year and has no health and dental benefits plans. She cannot expense a salary as it's a sole proprietorship but will take out $2,000 a month starting in August 2024. Any monies she takes out is a draw against the equity of the business and will appear on the Balance Sheet in the Equity section (and goes in CFF of course). She will pay her one "full-time" (FT) employee, her sister, \$22 per hour starting in March 2024 and her salary will be based on 40 hours per week. We assume she will work 4 weeks per month. All of her salary will be included in the Cost of Goods Sold. She is salaried so the hours or pay does not fluctuate. In addition, the payroll expenses and minimal benefits she pays to her add up to an additional 22% of her salary per month. She plans to hire part-time (PT) workers and pay them $20.00 per hour which is above the local living wage. They will work as needed and Figure 4 shows her projections for hours. Their payroll expenses are an additional 18% of their wages. All employee labour costs (wages + benefits) are to be included with Cost of Goods Sold. On the FIS, ensure the inventory/materials and HR are shown separately. Figure 4: Projected Hours Worked by Part-Time Employees for 2024 Lease and Location Expenses Susie has secured this location with a 5-year lease. When she moved in, she made a deal with the landlord to make the leasehold improvements. Her share to get the premises ready will be $6,000 in leasehold improvements all of which will be completed before her starting date. Depreciation will start with her projected opening date. Her rent is $23/square foot for her (1515)225sqft of space . In addition, she pays an additional $2.50/ square foot for the CAM. The CAM covers a common washroom, use of two labelled parking spots, and all interior and exterior maintenance. Her other monthly location costs are minor maintenance and cleaning costs of $200/ month and Content Insurance for $90 Delivery Expenses Instead of paying herself mileage, Susie is bringing her own van into the business during the start-up. She plans to deliver locally. She figures that way she can deduct vehicle expenses. It is an older white cargo van with a FMV of $5,000. Her accountant said to show it coming the beginning of January 2024. The van has a projected useful life of 5 years and no salvage value. She is going to put all other vehicle maintenance and van operating costs in a Delivery expense account. She figures that will amount to $300 per month plus extra delivery fees of $200 in Oct., Nov. and Dec. as they are her busy months. She will keep the "extra" delivery fees on a separate line on the input sheets to ensure she can easily adjust as needed. Other Monthly Expenses Other monthly cash expenses for 2024 are estimated to be: Financing Arrangements Susie has a good working relationship with her local bank. She already has a secured personal Line of Credit against her house with a $100,000 limit on it. She pays 8% interest on it anytime she uses it. For this forecasting, she assumes she pays back the FULL principal in the next month and again borrows anything she needs (as opposed to keeping a running total). The monthly interest incurred is also paid in that following month. She realizes this is slightly overestimating the interest as she may have only borrowed it for 5 or 6 days but finds it is the best way to estimate the cost of borrowing. She insists you build in a contingency (or "cushion") amount of $1,500 cash on hand at the end of each month on her CFF reconciliation. If her cash on hand plus the contingency falls below this $1,500, she will borrow against the line of credit. Susie has $60,000 saved to invest in the business. She puts it in during the start-up period. Her parents will give her a loan of $20,000. They are charging her 5% per year and she has agreed to pay them back over 18 months with equal monthly payments consisting of principal and interest (PMT). Susie will receive the money in January 2024 and will make her first payment the following month (February 2024). To make any comparisons easier later on, you tell her you will group financial costs together one after the other on the CFF and total them on the FIS (that will include: bank charges, interest on the loan and any interest incurred on the line of credit, and all credit card charges) and call them Financial Costs and put them in the Operating Expenses on the FIS rather than after the Net Income line. (Note: this is the way the Industry Financial Data does it) Benchmarking Against Industry Financial Performance Data You have also recommended that on her FIS, you setup a calculated "Expenses as a \% of Sales" column. She can use this to quickly benchmark to the Industry Canada's Financial Performance Data. You'll export corresponding sheets into the last tabs in her workbook for Rubric \#2 where you add the \% of Sales from NAICS for comparison. You recommend you include two in the workbook. Use the following parameters for the two reports: - Manufacturing Confectionery from purchased chocolate - NAICS 311352 - Revenue range $30K$5M - One shows \% of sales and the other one shows thousands $ of sales - ALL Businesses for ALL of Canada. - Distribute the Quartile Sales by Total Revenue not Profit Margins - Use the quartile that corresponds to the level of sales Other Notes - Sales taxes and input tax credits (ITCs for HST) are important factors in retail sales. However, for these projections, Susie has taken her accountant's advice and has excluded these from her estimated sales and expenses. She will not build in tax remittance estimates. - Government benefit expenses (CPP, etc.-the 18% and 22% ) for her employees, will show in the month incurred not the month in which they are remitted. Case Requirements Suzie realizes these numbers are all guesses but it's a start. She now wants to have you set up a set of Excel financial statements to allow her to do more analysis and planning. She is looking to you to create a Workbook consisting of the Worksheets in Figure \#5. I would use this chart as a checklist. She does not know Excel like you do so she has asked that you build the model so she can easily see where she can change input data and print it out for review. Each tab must be formatted to fit on 811 paper but can be either landscape or portrait orientation. She would prefer they don't flow onto a second page but they must be easily readable!! The tabs must all look professional. For example, monthly columns should have equal width; everything should be readable when printed in B\&W; and, of course, spell checked. F23 The Chocolate Shop v1.0 Page 6 of 10 You will be submitting the assignment in 2 parts and the two pieces are worth a total of 25% of your term mark. There is a bonus for successful completion of the 2nd submission that balances after ad hoc changes are made. F23 The Chocolate Shop v1.0 Page 7 of 10