A new client of yours, Lilly Waters wants to open an ice cream shop near the downtown shopping center. She will sell Ice cream cones or in a cup, shakes, sundaes, and soda. Her grand opening will be September 30, 2020. The name of her business is Sprinkles! You, as an independent consultant, have been hired to develop a cash pro forma budget for her business venture (note: this is a spreadsheet that helps forecast income and expenses over a period of time). It used to plan for and manage the business if done correctly. Assignment: Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year of operations. Use the file attached Excel Template - "Sprinkles" as your starting point. Download and use this file as the basis for your assignment. Do not make any changes to this pre-defined template Items (this means start with the items that already included and that are expected in the spreadsheet). You may add your information to the existing sheets. You may add extra worksheets as needed as well update the template. Do not use a template from a previous semester - this is academic dishonesty and will be subject to disciplinary action I Place the finished cash pro forma on a worksheet labeled "Cash ProForma". Place all your case assumptions data on a separate worksheet Label the worksheet "Assumptions" (note: each piece of data must appear in its own cell on the Assumption sheet). Place your start-up costs on a third worksheet labeled "Startup Costs Create two additional worksheets for your recommendations. You will choose 2 of the 3 recommendations defined at the end of this document. Label one worksheet Specialty Desserts" (this will include: Banana Splits, Double Delight Brownie Fudge and Molten Lava Spectacular), or "Ice Cream Cakes" or Iced Coffee Appropriate Charts (graphs): You will be creating two separate charts so create and label two additional worksheets for the charts (each chart will be in its own worksheet). MacBo Information needed to complete assignment: Sugar cones, milkshakes, sundaes, toppings, soda and bottle water. Product Selling Prices: I scoop Cup or Sugar Cone - $2.25 2 scoops Cup or Sugar Cone - $3.25 3 scoops Cup or Sugar Cone - $425 I scoop Sugar Cone - $2.25 2 scoops Sugar Cone - $325 3 scoops Sugar Cone - $425 Small Shake - $2.95 Medium Shake - $3.75 Large Shake - $4.50 Small Sundae (1 topping) - $2.55 Medium Sundae (1 topping) - $3.55 Large Sundae (1 topping) - 34.65 Additional topping: 50 per topping Soda $1.25 a bottle Bottled Water - $1.00 a bottle Cost of Goods Sold: Ice Cream Cones (Ice cream ingredients and cone) cost $1.10 for 1 scoop and 20 cents extra for each additional scoop Ice cream Cups (Ice cream ingredients and cup) cost $1.25 and 20 cents per extra for each additional scoop Small Shake cost $1.40 Medium Shake cost $1.55 Large Shake cost $1.75 Small Sundae cost $1.20 Medium Sundeco 51 50 Large Sund cost 51 75 Addons Toppers con 10 Sodobe Podbe Medium Sundae cost $1.50 Large Sundae cost $1.75 Additional Toppings cost $. 10 Sodas cost about $.7 per 16 oz. bottle Water cost $.6 per 16oz bottle The building rent is $2500 per month Phone will cost about $100 per month. Electricity should cost about $800 a month Insurance will be $850 a month. Advertising and promotion will be $500 a month. Operating Hours: Sprinkles will be open seven days a week. Sprinkles will serve ice cream products all day and will be open from 11am - 8 pm on weekdays (Monday - Thursday, Sunday) Friday Saturday 11am - 11pm Employees: Two hourly employees on Friday, Saturday and Sunday. One hourly employee needed during Monday - Thursday. Either an assistant manager or manager is required during the Sprinkles Operating hours. Your client will be the manager and draw a salary of $35.000 per year (including benefits). She will work during the busiest times and fill in for the assistant manager. Approximately 40% The assistant manager will receive a salary of $22.400 per year(including benefits). Approximately 60% Hourly workers will be paid $8.75 an hour. Demand Rate Monday - Thursday the owner expects 10 customers per hour Friday - Sunday the owner expects an average of 20 customers per hour . Additional topping: .50 per fopping Soda -$1.25 a bottle Bottled Water - $1.00 a bottle Cost of Goods Sold: Ice Cream Cones (Ice cream ingredients and cone) cost $1.10 for 1 scoop and 20 cents extra for each additional scoop Ice cream Cups (Ice cream ingredients and cup) cost $1.25 and 20 cents per extra for each additional scoop Small Shake cost $1.40 Medium Shake cost $1.55 Large Shake cost $1.75 Small Sundae cost $1.20 Medium Sundae cost $1.50 Large Sundae cost $1.75 Additional Toppings cost 5.10 Sodas cost about $.7 per 16 oz. bottle Water cost $.6 per 16oz bottle The building rent is $2500 per month Phone will cost about $100 per month. Electricity should cost about $800 a month Insurance will be $850 a month Advertising and promotion will be $500 a month Operating Hours: Sprides will be open seven days a week Spides will serve ice cream products all day and will be open from 11am-8pm on weekdays (Monday - Thursday, Sunday) Friday Saturday 11am-11pm Employees Two hourly employees on Friday Saturday and Sunday One hourly employee needed during Monday - Thursday Ether an assistant manager or manager is required dunng the Spanies Operaeng hours salary of $35.000 per year including Marc Employees: Two hourly employees on Friday, Saturday and Sunday. One hourly employee needed during Monday - Thursday. . Either an assistant manager or manager is required during the Sprinkles Operating hours. Your client will be the manager and draw a salary of $35,000 per year (including benefits). She will work during the busiest times and fill in for the assistant manager. Approximately 40%. The assistant manager will receive a salary of $22.400 per year(including benefits). Approximately 60%. Hourly workers will be paid $8.75 an hour. Demand Rate: Monday - Thursday the owner expects 10 customers per hour. Friday - Sunday the owner expects an average of 20 customers per hour.. 90 2 Product demand on average: 1/2 of all customers will buy 1 scoop Ice Cream Cones 1/2 of all customers will buy 2 scoop Ice Cream Cones 1/3 of all customer will buy 3 scoop Ice Cream Cones 1/2 of all customers will buy 1 scoop Ice Cream Cups 1/2 of all customers will buy 2 scoop Ice Crear cups 1/3 of all customer will buy 3 scoop Ice Cream cups 1/4 of all customers will buy Medium Shakes 1/4 of all customers will buy Large Shakes 12 of all customers will buy Small shakes 1/4 of all customers will buy a Largo Sundae 1/3 of all customers will buy a Medium Sunda 1/4 of all customers will buy Small Sunda 314 of all customers will buy a Soda 1/4 of all customers will buy a Bottled Water 114 of all customers will buy an additional topping MacBook Ai Product demand on average: o 1/2 of all customers will buy 1 scoop Ice Cream Cones o 1/2 of all customers will buy 2 scoop Ice Cream Cones o 1/3 of all customer will buy 3 scoop Ice Cream Cones o 1/2 of all customers will buy 1 scoop Ice Cream cups o 1/2 of all customers will buy 2 scoop Ice Cream cups o 1/3 of all customer will buy 3 scoop Ice Cream cups o 1/4 of all customers will buy Medium Shakes 1/4 of all customers will buy Large Shakes o 1/2 of all customers will buy Small shakes 1/4 of all customers will buy a Large Sundae 1/3 of all customers will buy a Medium Sundae 1/4 of all customers will buy Small Sundae o 3/4 of all customers will buy a Soda o 1/4 of all customers will buy a Bottled Water 1/4 of all customers will buy an additional topping Start-up costs O O Kitchen equipment: $10,250 Cash register and sales equipment: $1,350 Initial inventory: $4,500 Pre-opening marketing: $1,500 Store fixtures (chairs, tables etc.): $4,500 Oil paintings of your client's momma and grandma to hang on the wall: $350 Licenses: $1,025 Security deposit $4,500 First Insurance Payment: $750 Your client has $8,000 and plans to borrow the rest from the bank with a five-year loan at 2.5% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 21% if Income Before Taxes (IBT) is equal to or is greater than $13,500. Assume a tax rate of 13% if IBT is less than $13,500. You are to calculate the monthly tax payment using the appropriate logical function. Assume that sales will grow at an average of 1.50% per month. Assume that each month contains 4.2 weeks. Recommendations: Show your client how these recommendations would affect the bottom line by recreating the pro forma for each scenario, and applying the data analysis to determine profitability A new client of yours, Lilly Waters wants to open an ice cream shop near the downtown shopping center. She will sell Ice cream cones or in a cup, shakes, sundaes, and soda. Her grand opening will be September 30, 2020. The name of her business is Sprinkles! You, as an independent consultant, have been hired to develop a cash pro forma budget for her business venture (note: this is a spreadsheet that helps forecast income and expenses over a period of time). It used to plan for and manage the business if done correctly. Assignment: Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year of operations. Use the file attached Excel Template - "Sprinkles" as your starting point. Download and use this file as the basis for your assignment. Do not make any changes to this pre-defined template Items (this means start with the items that already included and that are expected in the spreadsheet). You may add your information to the existing sheets. You may add extra worksheets as needed as well update the template. Do not use a template from a previous semester - this is academic dishonesty and will be subject to disciplinary action I Place the finished cash pro forma on a worksheet labeled "Cash ProForma". Place all your case assumptions data on a separate worksheet Label the worksheet "Assumptions" (note: each piece of data must appear in its own cell on the Assumption sheet). Place your start-up costs on a third worksheet labeled "Startup Costs Create two additional worksheets for your recommendations. You will choose 2 of the 3 recommendations defined at the end of this document. Label one worksheet Specialty Desserts" (this will include: Banana Splits, Double Delight Brownie Fudge and Molten Lava Spectacular), or "Ice Cream Cakes" or Iced Coffee Appropriate Charts (graphs): You will be creating two separate charts so create and label two additional worksheets for the charts (each chart will be in its own worksheet). MacBo Information needed to complete assignment: Sugar cones, milkshakes, sundaes, toppings, soda and bottle water. Product Selling Prices: I scoop Cup or Sugar Cone - $2.25 2 scoops Cup or Sugar Cone - $3.25 3 scoops Cup or Sugar Cone - $425 I scoop Sugar Cone - $2.25 2 scoops Sugar Cone - $325 3 scoops Sugar Cone - $425 Small Shake - $2.95 Medium Shake - $3.75 Large Shake - $4.50 Small Sundae (1 topping) - $2.55 Medium Sundae (1 topping) - $3.55 Large Sundae (1 topping) - 34.65 Additional topping: 50 per topping Soda $1.25 a bottle Bottled Water - $1.00 a bottle Cost of Goods Sold: Ice Cream Cones (Ice cream ingredients and cone) cost $1.10 for 1 scoop and 20 cents extra for each additional scoop Ice cream Cups (Ice cream ingredients and cup) cost $1.25 and 20 cents per extra for each additional scoop Small Shake cost $1.40 Medium Shake cost $1.55 Large Shake cost $1.75 Small Sundae cost $1.20 Medium Sundeco 51 50 Large Sund cost 51 75 Addons Toppers con 10 Sodobe Podbe Medium Sundae cost $1.50 Large Sundae cost $1.75 Additional Toppings cost $. 10 Sodas cost about $.7 per 16 oz. bottle Water cost $.6 per 16oz bottle The building rent is $2500 per month Phone will cost about $100 per month. Electricity should cost about $800 a month Insurance will be $850 a month. Advertising and promotion will be $500 a month. Operating Hours: Sprinkles will be open seven days a week. Sprinkles will serve ice cream products all day and will be open from 11am - 8 pm on weekdays (Monday - Thursday, Sunday) Friday Saturday 11am - 11pm Employees: Two hourly employees on Friday, Saturday and Sunday. One hourly employee needed during Monday - Thursday. Either an assistant manager or manager is required during the Sprinkles Operating hours. Your client will be the manager and draw a salary of $35.000 per year (including benefits). She will work during the busiest times and fill in for the assistant manager. Approximately 40% The assistant manager will receive a salary of $22.400 per year(including benefits). Approximately 60% Hourly workers will be paid $8.75 an hour. Demand Rate Monday - Thursday the owner expects 10 customers per hour Friday - Sunday the owner expects an average of 20 customers per hour . Additional topping: .50 per fopping Soda -$1.25 a bottle Bottled Water - $1.00 a bottle Cost of Goods Sold: Ice Cream Cones (Ice cream ingredients and cone) cost $1.10 for 1 scoop and 20 cents extra for each additional scoop Ice cream Cups (Ice cream ingredients and cup) cost $1.25 and 20 cents per extra for each additional scoop Small Shake cost $1.40 Medium Shake cost $1.55 Large Shake cost $1.75 Small Sundae cost $1.20 Medium Sundae cost $1.50 Large Sundae cost $1.75 Additional Toppings cost 5.10 Sodas cost about $.7 per 16 oz. bottle Water cost $.6 per 16oz bottle The building rent is $2500 per month Phone will cost about $100 per month. Electricity should cost about $800 a month Insurance will be $850 a month Advertising and promotion will be $500 a month Operating Hours: Sprides will be open seven days a week Spides will serve ice cream products all day and will be open from 11am-8pm on weekdays (Monday - Thursday, Sunday) Friday Saturday 11am-11pm Employees Two hourly employees on Friday Saturday and Sunday One hourly employee needed during Monday - Thursday Ether an assistant manager or manager is required dunng the Spanies Operaeng hours salary of $35.000 per year including Marc Employees: Two hourly employees on Friday, Saturday and Sunday. One hourly employee needed during Monday - Thursday. . Either an assistant manager or manager is required during the Sprinkles Operating hours. Your client will be the manager and draw a salary of $35,000 per year (including benefits). She will work during the busiest times and fill in for the assistant manager. Approximately 40%. The assistant manager will receive a salary of $22.400 per year(including benefits). Approximately 60%. Hourly workers will be paid $8.75 an hour. Demand Rate: Monday - Thursday the owner expects 10 customers per hour. Friday - Sunday the owner expects an average of 20 customers per hour.. 90 2 Product demand on average: 1/2 of all customers will buy 1 scoop Ice Cream Cones 1/2 of all customers will buy 2 scoop Ice Cream Cones 1/3 of all customer will buy 3 scoop Ice Cream Cones 1/2 of all customers will buy 1 scoop Ice Cream Cups 1/2 of all customers will buy 2 scoop Ice Crear cups 1/3 of all customer will buy 3 scoop Ice Cream cups 1/4 of all customers will buy Medium Shakes 1/4 of all customers will buy Large Shakes 12 of all customers will buy Small shakes 1/4 of all customers will buy a Largo Sundae 1/3 of all customers will buy a Medium Sunda 1/4 of all customers will buy Small Sunda 314 of all customers will buy a Soda 1/4 of all customers will buy a Bottled Water 114 of all customers will buy an additional topping MacBook Ai Product demand on average: o 1/2 of all customers will buy 1 scoop Ice Cream Cones o 1/2 of all customers will buy 2 scoop Ice Cream Cones o 1/3 of all customer will buy 3 scoop Ice Cream Cones o 1/2 of all customers will buy 1 scoop Ice Cream cups o 1/2 of all customers will buy 2 scoop Ice Cream cups o 1/3 of all customer will buy 3 scoop Ice Cream cups o 1/4 of all customers will buy Medium Shakes 1/4 of all customers will buy Large Shakes o 1/2 of all customers will buy Small shakes 1/4 of all customers will buy a Large Sundae 1/3 of all customers will buy a Medium Sundae 1/4 of all customers will buy Small Sundae o 3/4 of all customers will buy a Soda o 1/4 of all customers will buy a Bottled Water 1/4 of all customers will buy an additional topping Start-up costs O O Kitchen equipment: $10,250 Cash register and sales equipment: $1,350 Initial inventory: $4,500 Pre-opening marketing: $1,500 Store fixtures (chairs, tables etc.): $4,500 Oil paintings of your client's momma and grandma to hang on the wall: $350 Licenses: $1,025 Security deposit $4,500 First Insurance Payment: $750 Your client has $8,000 and plans to borrow the rest from the bank with a five-year loan at 2.5% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 21% if Income Before Taxes (IBT) is equal to or is greater than $13,500. Assume a tax rate of 13% if IBT is less than $13,500. You are to calculate the monthly tax payment using the appropriate logical function. Assume that sales will grow at an average of 1.50% per month. Assume that each month contains 4.2 weeks. Recommendations: Show your client how these recommendations would affect the bottom line by recreating the pro forma for each scenario, and applying the data analysis to determine profitability