I CANNOT UPLOAD THE EXCEL TEMPLATE, SO I HAD TO INSERT SCREENSHOTS OF IT INSTEAD. LABEL EACH CATEGORY CORRECTLY AND USE CALCULATIONS AND FORMULAS IF NEEDED. I NEED THIS ASAP PLEASE :(

Sheet 1:

Sheet 2:

Sheet 3:

Grading Criteria:

Sprinkles Ice Cream Shoppe Please read the instructions carefully. A new client of yours, Lily Waters wants to open an ice cream shop near the downtown shopping center. She will sell ice cream cones or in a cup shakes, Sundars, and soda Her gravid opening will be January 1, 2022. Lily needs help figuring out how her business will perform the first year in business. She has hired you as an independent consultant, prepare a cash proforma budget for her business venture. This is a spreadsheet that helps forecast income and expenses over a period of time. It can be used to plans and manage the business it 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 "Sprinkless your starting point Download and use this fie 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 . 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 Ice Cream Cakes and leed Coffee Appropriate Charts graphs. You will be creating two separate charts so create and aber two additional worksheets for the charts (each chart will be in its own worksheet) Chart One - Monthly Product Revenue - This we show the monthly revenue for each of your five products for the endre year Chart Two-Total Product Net Income. You want to track the total product net income for the year to determine any trends or projections in product sales . Maive sure both charts are formatted correctly appropriate the legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Sugarcones, ice cream cups, milkshakes, sundimet, toppings, soda and bottle water Product Selling Prices 1 scoop Cup or Sugar Cone - $225 2 scoope Cup or Sugar Cone - 5325 3 scoops Cup or Sugar Cone - 54.25 Iscoop Sugar Cone - 52.25 2 scoops Sugar Cone $3.25 3 scoop Sugar Cone-5425 Small Shake - 52.05 Medium Shake - 53.75 Large Shake - $450 Small Sundae ( topping) - $2.55 Medium Sundae (1 topping) - 53.55 Large Sundae (1 topping) - 54.65 Additional topping 50 per topping Soda $1.25 a bottle Botled Water - $100 a botte Cost of Goods Sold: Ice Cream Cones ce cream ingredients and cone) cost 51.10 for scoop and 20 cents extra for each additional coop Ice Cream Cup (1ce cream Ingredients and cup cost $1.25 and 20 cents per extra for each additionat Smail Shake cost $1.40 Medium Shake cost $1.55 Large Shake cost $1.75 Small Sundae cost 51.35 Medium Sundae cost $1.50 Large Sundae cost $1.75 Additional Toppings cost $ 10 Sodas cost about 5.7 per 16 oz bottle Water cost $.6 per 16oz bottle The building rent is $2500 per month Phone/internet will cost about $1000 per month Electricity should cost about $1200 a month Insurance will be $900 a month Advertising and promotion wil be $800 a month Product demand on average 1/2 of all customers will buy 1 scoop Ice Cream Cones 12 of all customers will buy 2 scoop Ice Cream Cones 13 of all customer will buy 3 scoop Ice Cream Cones 12 of all customers wil buy 1 scoop Ice Cream cupe 12 of all customers wil buy 2 scoop Ice Cream cups 113 of customer will buy scoop Ice Cream cups 3 114 of alastomers wil buy Medium Shake 1/4 of all customers wil buy Large Shakes 12 of ml customers will buy Smal shakes 14 of all customers wil buy a Large Sundae 113 of wil cuorers wil buya Medium Sundae 114 of all customers will buy Small Sundae 34 of all customers will buy a Soda 1/4 of all customers will buy a Bottled Water 1/4 of all comes wil buy an additional topping Start-up costs Kitchen equipment $10,250 Cash register and sales equipment 51,380 Initi Inventory 14,500 Pre-opening marketing: 5.00 Store futures (chairs, tables et..$4,500 Ol paintings of your clients momma and grandma to hang on the wall $350 Licenses enses: 91,025 Secully deposit $4,500 First nuance Payment 5750 Operating Hours: Sprinkle wil be open seven days a week. Sprinkles will serve le cream products all day and will be open 11am-8pm on weekdays (Monday - Thursday, Sunday) 11am-11pm Friday, Saturday Employees The hously employees on Friday, Saturday One hourly employee needed during Monday. Thursday, Sunday Etheran sant manager manager is required during the Sprinies 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 it in for the assistant Manager. Approximately 40% The assistant manager will receive a matary et 522.00 per yearleroluding benetto) Approximately Hourly workers will be paid $8.75 an hou. Demand Rate: Monday, Thursday, Sunday the owner expects to customers per hour Friday Saturday the owner expects an average of 20 customers per hour Your client has 58.000 and plans to borrow the rest from the bank with a five-year loan at 25% interest. You are to calculate the monthly loan payment using the appropriate trancia function Asume a tax rate of 21st Income Before Taxes (IBT) is equal to or a greater than $13,500. Assume a total of 13% BT is less than $13,500. You are to calculate the monthly tax payment using the appropriate logical function Assume that she will grow at an average of 1.50% per month. Assume that each month contains 4.2 weeks Recommendations: Show your client how these recommendation would affect the bottom line by ring pro form for each scenario, and applying the data analysis to determine profitability You do not have to start from scratch, but note, these are completely independent pro formas. They must update accordingly from the data worksheets. Plan on showing your analysis and discussing the proforma changes that occur under each new scenario and how it affects profitability, Use a formatted text box (not a comment) to explain your recommendations under each new pro forma. This will be approximately a 2-3 paragraph endeavor. Scenario One: "What if" Analysis for adding flavored ice coffees. Your client is unsure it she should sell flavored ice coffees. She thinks she can sella coffee to every second customer and it seems to be lucrative because the coffee sells for $3.75 each and costs him only $1.60 to purchase. Unfortunately your client is afraid that he would cannibalize his soft drink sales with the coffee customers (one soft drink less for every coffee sold). It will cost him $5.250 to purchase the equipment and insurance costs would rise by another $155 per month due to the hot equipment needed to make the coffee. What is your recommendation: Should your client offer flavored coffee to her customers? Scenario Two: "What if Analysis Your client would like to consider adding ice cream cakes to the list of items available for sale. She is wanting to offer cakes with customized cake, ice cream decorations. She thinks she can sell 20 small cakes a week and 25 large cakes a week. It seems to be lucrative because the small cake is $16.95 and the large cake is $23.95. Costs are $5.45 per small cake and $7.40 per large cake. It will cost her $3.250 to purchase the equipment and insurance costs would rise by another $255 per month due to the hot equipment needed to make the cakes. What is your recommendation? Would it be profitable to sell the cakes? Alignment Nuntet 33 Revenue: op.com Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month & Month 9 Month 10 Month 11 Month 12 Total opone 3:00 Sunda Med Bunda Sna Medium Soda Bored Fing 20. Mange 22 Expenses COGS 20 Ft (20 Phone E 37 21 Adressing Per Toper Income Before at Cashflow Gracing criteria Sheet1 Sheet2 Sheet 56F Clear ER Type here to search Assume Pred Zone co Sunce une Sunda Share th price 1000 2008 308 0001 COGS COS Sul Sur 08 Sund Large COGS th0005 SCO Shes Large COGS 0005 Sot 0000 Fed Cushe Bech Operating information day de pe Hounced House Serenowe Competende How Howly played of purchasing San Wang Cream hing Suomen Medium So we do mal punching Band de pantang med pered Lane conto income tariewe 11 Start Up Costa Kitchen went Sales equipment (cash register, etc.) Initial inventory Preparing marating Pictures Furniture chairs, tables et Oil painting License Security deposit Intrance payment Total Owner's Equity Cash Reserves Leon Amount Criteria: Your current score: Sheets are appropriately labeled 5 points All information cells on cash profoma are referenced back to the assumption page, calcul 35 points Assumptions and startup worksheets setup and complete. 15 points Recommendations are based on cash profoma infomation and what if analysis Use separa 15 points The chart is present and reflects worksheet content 10 points Taxes are calculated correctly using boolean logic. 10 points Loan payment is calculated using a function.. 10 points Score (out of 100) 0 Sprinkles Ice Cream Shoppe Please read the instructions carefully. A new client of yours, Lily Waters wants to open an ice cream shop near the downtown shopping center. She will sell ice cream cones or in a cup shakes, Sundars, and soda Her gravid opening will be January 1, 2022. Lily needs help figuring out how her business will perform the first year in business. She has hired you as an independent consultant, prepare a cash proforma budget for her business venture. This is a spreadsheet that helps forecast income and expenses over a period of time. It can be used to plans and manage the business it 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 "Sprinkless your starting point Download and use this fie 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 . 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 Ice Cream Cakes and leed Coffee Appropriate Charts graphs. You will be creating two separate charts so create and aber two additional worksheets for the charts (each chart will be in its own worksheet) Chart One - Monthly Product Revenue - This we show the monthly revenue for each of your five products for the endre year Chart Two-Total Product Net Income. You want to track the total product net income for the year to determine any trends or projections in product sales . Maive sure both charts are formatted correctly appropriate the legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Sugarcones, ice cream cups, milkshakes, sundimet, toppings, soda and bottle water Product Selling Prices 1 scoop Cup or Sugar Cone - $225 2 scoope Cup or Sugar Cone - 5325 3 scoops Cup or Sugar Cone - 54.25 Iscoop Sugar Cone - 52.25 2 scoops Sugar Cone $3.25 3 scoop Sugar Cone-5425 Small Shake - 52.05 Medium Shake - 53.75 Large Shake - $450 Small Sundae ( topping) - $2.55 Medium Sundae (1 topping) - 53.55 Large Sundae (1 topping) - 54.65 Additional topping 50 per topping Soda $1.25 a bottle Botled Water - $100 a botte Cost of Goods Sold: Ice Cream Cones ce cream ingredients and cone) cost 51.10 for scoop and 20 cents extra for each additional coop Ice Cream Cup (1ce cream Ingredients and cup cost $1.25 and 20 cents per extra for each additionat Smail Shake cost $1.40 Medium Shake cost $1.55 Large Shake cost $1.75 Small Sundae cost 51.35 Medium Sundae cost $1.50 Large Sundae cost $1.75 Additional Toppings cost $ 10 Sodas cost about 5.7 per 16 oz bottle Water cost $.6 per 16oz bottle The building rent is $2500 per month Phone/internet will cost about $1000 per month Electricity should cost about $1200 a month Insurance will be $900 a month Advertising and promotion wil be $800 a month Product demand on average 1/2 of all customers will buy 1 scoop Ice Cream Cones 12 of all customers will buy 2 scoop Ice Cream Cones 13 of all customer will buy 3 scoop Ice Cream Cones 12 of all customers wil buy 1 scoop Ice Cream cupe 12 of all customers wil buy 2 scoop Ice Cream cups 113 of customer will buy scoop Ice Cream cups 3 114 of alastomers wil buy Medium Shake 1/4 of all customers wil buy Large Shakes 12 of ml customers will buy Smal shakes 14 of all customers wil buy a Large Sundae 113 of wil cuorers wil buya Medium Sundae 114 of all customers will buy Small Sundae 34 of all customers will buy a Soda 1/4 of all customers will buy a Bottled Water 1/4 of all comes wil buy an additional topping Start-up costs Kitchen equipment $10,250 Cash register and sales equipment 51,380 Initi Inventory 14,500 Pre-opening marketing: 5.00 Store futures (chairs, tables et..$4,500 Ol paintings of your clients momma and grandma to hang on the wall $350 Licenses enses: 91,025 Secully deposit $4,500 First nuance Payment 5750 Operating Hours: Sprinkle wil be open seven days a week. Sprinkles will serve le cream products all day and will be open 11am-8pm on weekdays (Monday - Thursday, Sunday) 11am-11pm Friday, Saturday Employees The hously employees on Friday, Saturday One hourly employee needed during Monday. Thursday, Sunday Etheran sant manager manager is required during the Sprinies 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 it in for the assistant Manager. Approximately 40% The assistant manager will receive a matary et 522.00 per yearleroluding benetto) Approximately Hourly workers will be paid $8.75 an hou. Demand Rate: Monday, Thursday, Sunday the owner expects to customers per hour Friday Saturday the owner expects an average of 20 customers per hour Your client has 58.000 and plans to borrow the rest from the bank with a five-year loan at 25% interest. You are to calculate the monthly loan payment using the appropriate trancia function Asume a tax rate of 21st Income Before Taxes (IBT) is equal to or a greater than $13,500. Assume a total of 13% BT is less than $13,500. You are to calculate the monthly tax payment using the appropriate logical function Assume that she will grow at an average of 1.50% per month. Assume that each month contains 4.2 weeks Recommendations: Show your client how these recommendation would affect the bottom line by ring pro form for each scenario, and applying the data analysis to determine profitability You do not have to start from scratch, but note, these are completely independent pro formas. They must update accordingly from the data worksheets. Plan on showing your analysis and discussing the proforma changes that occur under each new scenario and how it affects profitability, Use a formatted text box (not a comment) to explain your recommendations under each new pro forma. This will be approximately a 2-3 paragraph endeavor. Scenario One: "What if" Analysis for adding flavored ice coffees. Your client is unsure it she should sell flavored ice coffees. She thinks she can sella coffee to every second customer and it seems to be lucrative because the coffee sells for $3.75 each and costs him only $1.60 to purchase. Unfortunately your client is afraid that he would cannibalize his soft drink sales with the coffee customers (one soft drink less for every coffee sold). It will cost him $5.250 to purchase the equipment and insurance costs would rise by another $155 per month due to the hot equipment needed to make the coffee. What is your recommendation: Should your client offer flavored coffee to her customers? Scenario Two: "What if Analysis Your client would like to consider adding ice cream cakes to the list of items available for sale. She is wanting to offer cakes with customized cake, ice cream decorations. She thinks she can sell 20 small cakes a week and 25 large cakes a week. It seems to be lucrative because the small cake is $16.95 and the large cake is $23.95. Costs are $5.45 per small cake and $7.40 per large cake. It will cost her $3.250 to purchase the equipment and insurance costs would rise by another $255 per month due to the hot equipment needed to make the cakes. What is your recommendation? Would it be profitable to sell the cakes? Alignment Nuntet 33 Revenue: op.com Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month & Month 9 Month 10 Month 11 Month 12 Total opone 3:00 Sunda Med Bunda Sna Medium Soda Bored Fing 20. Mange 22 Expenses COGS 20 Ft (20 Phone E 37 21 Adressing Per Toper Income Before at Cashflow Gracing criteria Sheet1 Sheet2 Sheet 56F Clear ER Type here to search Assume Pred Zone co Sunce une Sunda Share th price 1000 2008 308 0001 COGS COS Sul Sur 08 Sund Large COGS th0005 SCO Shes Large COGS 0005 Sot 0000 Fed Cushe Bech Operating information day de pe Hounced House Serenowe Competende How Howly played of purchasing San Wang Cream hing Suomen Medium So we do mal punching Band de pantang med pered Lane conto income tariewe 11 Start Up Costa Kitchen went Sales equipment (cash register, etc.) Initial inventory Preparing marating Pictures Furniture chairs, tables et Oil painting License Security deposit Intrance payment Total Owner's Equity Cash Reserves Leon Amount Criteria: Your current score: Sheets are appropriately labeled 5 points All information cells on cash profoma are referenced back to the assumption page, calcul 35 points Assumptions and startup worksheets setup and complete. 15 points Recommendations are based on cash profoma infomation and what if analysis Use separa 15 points The chart is present and reflects worksheet content 10 points Taxes are calculated correctly using boolean logic. 10 points Loan payment is calculated using a function.. 10 points Score (out of 100) 0