please answer fast student at uscb
Sprinkles Ice Cream Shoppe Please read the instructions carefully. 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. 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) Chart One -"Monthly Product Revenue" - this will show the monthly revenue for each of your five products for the entire 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. Make sure both charts are formatted correctly (i.e. appropriate title, legend where appropriate, data series property labeled) and they are appropriate for business use 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 - $4.25 I scoop Sugar Cone - $2 25 2 scoops Sugar Cone - $3.25 3 scoops Sugar Cone - $4.25 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) - $4.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 (loe 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 20 OCT 12 A CreamShoppev5f20.pdf 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 Insurance will be $850 a month. Advertising and promotion will be $500 a month. 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.. o o 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 1/3 of all customer will buy 3 scoop Ice Cream cups 1/4 of all customers will buy Medium Shakes o 1/4 of all customers will buy Large Shakes o 1/2 of all customers will buy Small shakes o 1/4 of all customers will buy a Large Sundae o 1/3 of all customers will buy a Medium Sundae o 1/4 of all customers will buy Small Sundae 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 Kitchen equipment: $10,250 Cash register and sales equipment: $1,350 Initial inventory: $4,500 Pre-opening marketing: $1,500 Store foctures (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 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 if she should sell flavored ice coffees. She thinks she can sell a 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? Bonus Scenario Three: "What if" Analysis - Optional 10pts. You may choose to complete this bonus scenario. It is optional. Your client would like to consider adding three specialty sundaes (Banana Split, Molten Lava Explosion, Nutty Chocolate Brownie. She thinks she can sell a specialty sundae to half the customers per day and it seems to be lucrative because these sundaes sell for 4.75 each and costs him only $1.85 to make. Unfortunately, your client is afraid that she would cannibalize his regular sundae sales. 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 brownies and molten cake. For this scenario create and use a pivot table to explain your recommendation along with the other scenario criteria instructions. Tour current score! Sheets and 5 points All information cols on cash profoma are referenced back to the assumptio page calculations are correct, al Molds have values, twelve months are presented and an annual totals are given as well as a monthly total Assumptions and startup worksheets setup and complete 15 points Recommendations are based on cash profoma information and what if analyse 15 points The chart is present and reflects worksheel content 10 points Taxes are calculated corectly using boolean logic 10 points Loan payment is calculated using a function 10 points Optional Bonus scenario - Pivot Table 10 points Score out of 100) 14 15 16 17 Wirap Test General Paste Insert S Delete EM ED Merge & Carter $ % 9 Conditional Format Cel Formatting as The Styles Ser Formal G63 f D . L N Month 1 Month 2 Month 3 Month 4 Month 5 Month Month 7 Month Month Month 10 Month 11 Revenue Month 12 Total 2 scoop cone 0 . scop con 1000 Up 10 2 cup 11 scoop op 12 Sundaes Small 13 Sundaes Medium 14 Sunda Large 15 Shakes Small 16 Shakes Medium 17 Shakes Large 18 Soda 19 Borsed War 20 Extra loopings 21 Monthly Revenue 23 Expenses 24 COGS 2 Rent 20 Phone 27 Electricity 28 Insurance 20 Advertising 30 Hourly Wages 31 Salaries 32 Loan Payment 33 Total Expenses 35 come Before Tax Tak Net income Cash Flow Sheet2 Sheet Gradigneria Ready Paste Merge & Center $ - % 91 Conditional Format Formatting as Table D26 f Assum 1 O concup price 2 scop concup price scoop con cup price Bundesme Sund Medium price Sundaes Large SM Medium price Shakes Lage price 12 Extra toppings 13 Soda price 1 sep cone COGS 19 2 sc cone COGS 183 scoop cone COGS scoop cup COGS op de COGS 21 CL 22 Sundaes SOOS 23 Sundaes Medium COGS 2 Sunders Large COGS Shakes SCOGS Shakes Medium COGS Shakes Large COGS Extra toppings Coos Soda COGS Water 0005 Pred Costs: Part Phone Insurance Advertising Operating normation of days open weekends Hours per weekdays) Hourg Customers per hour weekdays Customers per hour weekende Hounty employeesday weekdays) Hourly employeesday weekends) of customers purchasing 1 scoop Ice Cream Conescu of customers purchasing 2 scopos Ice Cream Cones/cups of customers purchasing 3 scoop Ice Cream Conesloups of customers purchasing SealShake of customers purchasing Medium Shakes of customers chasing Large Shakes Grading criteria Sheet1 Sheet2 Sheet 3 Ready Inse Advertising Operating information of days open weekdays) of days open weekends open weekdays) 40 Hours open weekends) 40 Heuty wage Customers per hour weekdays Customers per hour weekends Hourly employees/day weekdays) Moury employees/day weekende of customers purchasing scoop loe Cream Cones/cups 51 of customers purchasing 2 scoppalce a Cones of customers purchasing scoops Ice Cream Creeps of customers purchasing Sal Shakes 54 of customers purchasing Medium Shakes 38 of customers purchasing Large Shakes 5 of customers purchasing Small Sunda S of customers chasing Medium Sunda of customers purchasing Large Sundes of customers purchasing sodas of customers purchasing bottled water of customers purchasing additional toppings Manager Annual Salary Assistant Manager Annu Salary Growth rate Weeks Per Moth Leon period years Loan Interest rate income before taxis equal or greater than It income before tax is less than Tawe Taxe Grading criteria Sheet1 Sheet2 Sheet 3 + Ready Anal 14 15 Wrap Text General Paste B 1 Merge & Center $ % 9 899 Conditional Format Formatting as Table St A20 fx B C D E F 1 Start Up Costs: 2 3 Kitchen equipment 4 Sales equipment (cash register, etc.) 5 Initial inventory & Pre-opening marketing 7 Diner fixtures (chairs, tables etc.) Oil painting Licenses 10 Security deposit 11 Initial insurance payment 12 Total 13 14 Owner's Equity 15 Cash Reserves 16 Loan Amount 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 33 34 36 37 Sheet2 Sheet1 Sheet 3 Grading criteria Sprinkles Ice Cream Shoppe Please read the instructions carefully. 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. 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) Chart One -"Monthly Product Revenue" - this will show the monthly revenue for each of your five products for the entire 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. Make sure both charts are formatted correctly (i.e. appropriate title, legend where appropriate, data series property labeled) and they are appropriate for business use 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 - $4.25 I scoop Sugar Cone - $2 25 2 scoops Sugar Cone - $3.25 3 scoops Sugar Cone - $4.25 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) - $4.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 (loe 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 20 OCT 12 A CreamShoppev5f20.pdf 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 Insurance will be $850 a month. Advertising and promotion will be $500 a month. 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.. o o 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 1/3 of all customer will buy 3 scoop Ice Cream cups 1/4 of all customers will buy Medium Shakes o 1/4 of all customers will buy Large Shakes o 1/2 of all customers will buy Small shakes o 1/4 of all customers will buy a Large Sundae o 1/3 of all customers will buy a Medium Sundae o 1/4 of all customers will buy Small Sundae 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 Kitchen equipment: $10,250 Cash register and sales equipment: $1,350 Initial inventory: $4,500 Pre-opening marketing: $1,500 Store foctures (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 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 if she should sell flavored ice coffees. She thinks she can sell a 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? Bonus Scenario Three: "What if" Analysis - Optional 10pts. You may choose to complete this bonus scenario. It is optional. Your client would like to consider adding three specialty sundaes (Banana Split, Molten Lava Explosion, Nutty Chocolate Brownie. She thinks she can sell a specialty sundae to half the customers per day and it seems to be lucrative because these sundaes sell for 4.75 each and costs him only $1.85 to make. Unfortunately, your client is afraid that she would cannibalize his regular sundae sales. 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 brownies and molten cake. For this scenario create and use a pivot table to explain your recommendation along with the other scenario criteria instructions. Tour current score! Sheets and 5 points All information cols on cash profoma are referenced back to the assumptio page calculations are correct, al Molds have values, twelve months are presented and an annual totals are given as well as a monthly total Assumptions and startup worksheets setup and complete 15 points Recommendations are based on cash profoma information and what if analyse 15 points The chart is present and reflects worksheel content 10 points Taxes are calculated corectly using boolean logic 10 points Loan payment is calculated using a function 10 points Optional Bonus scenario - Pivot Table 10 points Score out of 100) 14 15 16 17 Wirap Test General Paste Insert S Delete EM ED Merge & Carter $ % 9 Conditional Format Cel Formatting as The Styles Ser Formal G63 f D . L N Month 1 Month 2 Month 3 Month 4 Month 5 Month Month 7 Month Month Month 10 Month 11 Revenue Month 12 Total 2 scoop cone 0 . scop con 1000 Up 10 2 cup 11 scoop op 12 Sundaes Small 13 Sundaes Medium 14 Sunda Large 15 Shakes Small 16 Shakes Medium 17 Shakes Large 18 Soda 19 Borsed War 20 Extra loopings 21 Monthly Revenue 23 Expenses 24 COGS 2 Rent 20 Phone 27 Electricity 28 Insurance 20 Advertising 30 Hourly Wages 31 Salaries 32 Loan Payment 33 Total Expenses 35 come Before Tax Tak Net income Cash Flow Sheet2 Sheet Gradigneria Ready Paste Merge & Center $ - % 91 Conditional Format Formatting as Table D26 f Assum 1 O concup price 2 scop concup price scoop con cup price Bundesme Sund Medium price Sundaes Large SM Medium price Shakes Lage price 12 Extra toppings 13 Soda price 1 sep cone COGS 19 2 sc cone COGS 183 scoop cone COGS scoop cup COGS op de COGS 21 CL 22 Sundaes SOOS 23 Sundaes Medium COGS 2 Sunders Large COGS Shakes SCOGS Shakes Medium COGS Shakes Large COGS Extra toppings Coos Soda COGS Water 0005 Pred Costs: Part Phone Insurance Advertising Operating normation of days open weekends Hours per weekdays) Hourg Customers per hour weekdays Customers per hour weekende Hounty employeesday weekdays) Hourly employeesday weekends) of customers purchasing 1 scoop Ice Cream Conescu of customers purchasing 2 scopos Ice Cream Cones/cups of customers purchasing 3 scoop Ice Cream Conesloups of customers purchasing SealShake of customers purchasing Medium Shakes of customers chasing Large Shakes Grading criteria Sheet1 Sheet2 Sheet 3 Ready Inse Advertising Operating information of days open weekdays) of days open weekends open weekdays) 40 Hours open weekends) 40 Heuty wage Customers per hour weekdays Customers per hour weekends Hourly employees/day weekdays) Moury employees/day weekende of customers purchasing scoop loe Cream Cones/cups 51 of customers purchasing 2 scoppalce a Cones of customers purchasing scoops Ice Cream Creeps of customers purchasing Sal Shakes 54 of customers purchasing Medium Shakes 38 of customers purchasing Large Shakes 5 of customers purchasing Small Sunda S of customers chasing Medium Sunda of customers purchasing Large Sundes of customers purchasing sodas of customers purchasing bottled water of customers purchasing additional toppings Manager Annual Salary Assistant Manager Annu Salary Growth rate Weeks Per Moth Leon period years Loan Interest rate income before taxis equal or greater than It income before tax is less than Tawe Taxe Grading criteria Sheet1 Sheet2 Sheet 3 + Ready Anal 14 15 Wrap Text General Paste B 1 Merge & Center $ % 9 899 Conditional Format Formatting as Table St A20 fx B C D E F 1 Start Up Costs: 2 3 Kitchen equipment 4 Sales equipment (cash register, etc.) 5 Initial inventory & Pre-opening marketing 7 Diner fixtures (chairs, tables etc.) Oil painting Licenses 10 Security deposit 11 Initial insurance payment 12 Total 13 14 Owner's Equity 15 Cash Reserves 16 Loan Amount 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 33 34 36 37 Sheet2 Sheet1 Sheet 3 Grading criteria
