Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please help, also could you show the formulas as well. Please help. Does it look better now, the first excel sheet goes all the up

image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
Please help, also could you show the formulas as well.
Please help.
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
Does it look better now, the first excel sheet goes all the up to 12 & then the total.
please
Sprinkles Ice Cream Shoppe Please read the instructions carefully. A new dient of yours. Marsha Johnson 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 August 1, 2022. Marsha 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 plan 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 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. . Do not remove the Grading Criteria Worksheet. 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 coll on the 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 Do not remove the Grading Criteria Worksheet . 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 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 Not 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 (.e. appropriate title, legend where appropriate, data series property labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Sugar cones, ice cream cups, milkshakes, sundaes, toppings, soda and botte water. Product Selling Prices: scoop Cup-$3.25 2 scoops Cup-$4.25 3 scoops Cup-$5.25 1 scoop Sugar Cone - $3.25 2 scoops Sugar Cone-$4.25 3 scoops Sugar Cone-$5.25 Small Shake-$3.95 Medium Shake-$4.85 Large Shake-$5.50 Small Sundae (1 topping)-$3.60 Medium Sundae (1 topping)-$4.65 Large Sundae (1 topping)-$5.75 Additional topping: 60 per topping 1 scoop Cup-$3.25 2 scoops Cup-$4.25 3 scoops Cup-$5.25 I scoop Sugar Cone-$3.25 2 scoops Sugar Cone-$4.25 3 scoops Sugar Cone -$5,25 Small Shake-$3.95 Medium Shake-$4.85 Large Shake-$5.50 Small Sundae (1 topping) $3.00 Medium Sundae (1 topping)-$4.65 Large Sundae (1 topping)-$5.75 Additional topping: 60 per topping Soda -$1.85 a bottle Bottled Water-$1.25 a bottle Cost of Goods Sold: Ice Cream Cones (Ice cream ingredients and cone) cost $1.60 for 1 scoop and .30 cents extra for each additional scoop ice cream Cups (lce cream ingredients and cup) cost $1,75 and 30 cents per extra for each additional scoop Small Shake cost $1.65 Medium Shake cost $1.80 Large Shake cost $2.05 Small Sundae cost $1.65 0 Medium Sundae cost $1.85 Large Sundae cost $2.05 Additional Toppings cost $.20 Sodas cost about $.95 per 16 oz. bottle Water cost $.8 per 160 bottle The building rent is $2500 per month Phone/Internet will cost about $1000 per month Electricity should cost about $1300 a month. Insurance will be $1000 a month. Advertising and promotion will be $700 a month Operating Hours: Sprinkles will be open seven days a week. Sprinkles will serve ice cream products all day and will be open 11am-8pm on weekdays (Monday-Thursday, Sunday) 11am-11pm Friday, Saturday Employees Two hourly employees on Friday, Saturday One hourly employee needed during Monday-Thursday, Sunday . Either an assistant manager or manager is required during the Sprinkles Operating hours Your client will be the manager and draw a salary of $39,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 $27,400 per year(including benefits) Approximately 60% Medum Sundae cost $1.85 Large Sundae cost $2.05 Additional Toppings cost $.20 Sodas cost about $.95 per 16 oz. bole Water cost $8 per 1602 bottle The building rent is $2500 per month. Phone/internet will cost about $1000 per month Electricity should cost about $1300 a month. Insurance will be $1000 a month Advertising and promotion will be $700 a month. Operating Hours: Sprinkles will be open seven days a week. Sprinkles will serve ice cream products all day and will be open 11am-8pm on weekdays (Monday-Thursday, Sunday) 11am-11pm Friday, Saturday Employees Two hourly employees on Friday, Saturday One hourly employee needed during Monday-Thursday, Sunday Eer an assistant manager or manager is required during the Sprinkles Operating hours Your client will be the manager and draw a salary of $30,000 per year (including benef the assistant manager Approximately She will work during the busiest times and fill i 40% The assistant manager will receive a salary of $27.400 per year including benefits Approximately 60% Houdy workers al bend $12 an hour Approximately 60% Hourly workers will be paid $12 an hour. Demand Rate: Monday Thursday, Sunday the owner expects 12 customers per hour Friday-Saturday the owner expects an average of 22 customers per hour. Product demand on average: 1/2 of all customers will buy 1 scoop loe Cream Cones 1/2 of all customers will buy 2 scoop Ice Cream Cones 13 of a customer will buy 3 scoop Ice Cream Cones 1/2 of all customers will buy t sooop loe Cream cups of all customers will buy 2 scoop Ice Cream cups 1/2 1/3 of at customer will buy 3 scoop loe Cream ups 1/4 of all customers will buy Medium Shakes 20 19 1/4 of all customers will buy Large Shakes 12 of at customers will buy Smal shakes 14 of all customers will buy a Large Sundae # of all customers will buy a Medium Sundae 1/4 of all customers will buy Small Bundan 34 of all customers will buy a Soda 1/4 of at customers will buy a Bottled Water 1/4 of all customers will buy an additional topping Start-up costs Kitchen equipment: $10,250 Cash register and sales equipment $1,550 Customers wit buy a bomed Water o 1/4 of all customers will buy an additional topping Start-up costs Kitchen equipment: $10,250 Cash register and sales equipment: $1,550 Initial inventory: $5,000 Pre-opening marketing: $1,500 Store fixtures (chairs, tables etc.): $4,500 Artwork prints: $600 Security deposit $4,500 First Insurance Payment: $1,500 Your client has $15.000 and plans to borrow the rest from the bank with a five-year loan at 3.4% interest. You are to calculate the monthly loan payment using the appropriate financial function Assume a tax rate of 22% if Income Before Taxes (BT) is equal to or is greater than $28,500 Assume a tax rate of 15% f income Before Taxes is less than $28.499 You are to calculate the monthly sax 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 che forma. This will be approximately a 2-3 paragraph endeavor. Scenario One: "What if Analysis for adding flavored ice coffee. Your client is unsure if she should sel flavored ice coffees. She thinks she can sell a coffee to every second customer and it seems to be lucrative because the coffee sols for $3.75 each and costs him only $1.70 to purchase. Unfortunately your client is afraid that he would cannibalize his soft drink sales with the coffee purchase the customers (one soft drink less for every coffee sold), it will cost him $5,250 equipment and insurance costs would rise by another $175 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 soll 20 smal cakes a week and 25 large cakes a week. It seems to be lucrative because the small cake is $17.95 and the large coke is $24.95 Costs are 15 45 per small cake nod 140 per me con Scenario One: "What Ir Analysis for adding flavored ice coffees Your cent is unsure if she should sell favored every second customer and seems t costs him only purchase Unfortunately your client is afraid customers (one soft drink less equipment and insuran needed to What is your recommendation fihould y She thinks she because the and $40 thinks she can scoo coffee sets for $3.75 and Scenari Two: "What Analysis Your cllent would like to consider adding ice cream cakes to the list of items available for sale. She is wanting to c cakes with customized cake, ice cream decorations cannibalas his soft drink sales with the coffee will cost him $5.250 to purchase the month due to the not equipment to her custome $275 and 25 large cars a week targe cake is $24.95 Costs a asems to be turative $5.45 per make $3.500 to purchase the equipment and insurance costs would me by anothe equipment needed to make the on? Would it be posted the Se Grading Rute attached to assentor Grading Co Worksh Revenue: 1 scoop cone 2 scoop cone 73 scoop cone 1 scoop cup wwwwww scoop cup scoop cup Sundaes Small Sundsee Medium Sundaes Large 14 Shakes Smal 15 Shakes Medium M 19 3D 15 Shakes Large Soda 17 37 Botted Water 19 Extra toppings 20 Monthly Revenue 21 22 Expenses 23 COGS 24 Rent XSA 25 Phone 2 25 Electricity 27 Insurance Advertising 29 Hourly Wages 30 Salaries Loan Pement 9 0 Grading tele Sheet1 Sheet H 1 H Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Jo R Total D 20 Monthly Revenue 21 22 Expenses: 23 COGS 24 Rent 25 Phone 26 Electricity 27 insurance 25 Advertising 29 Hourly Wages 30 Salaries 31 Loan Payment 32 Total Expenses 33 34Income Before Tax 35 Tax 36 Net Income 37 Cash Flow 30 39 40 HE 46 47 48 La Maha B Grading crte Sheet 1 St 2 HO O D E N 0 548 WA -XVA Assumptions Made Products: 1 scoop consicup price 2 scoop cone/oup price 3 scoop cone/oup price Sundaes Small price 7 Sundaes Medium price Sundaes Large price Shakes Small price 10 Shakes Medium price 11 Shakes Large price 12 Extra toppings price 13 Soda price 14 Water price 15 161 scoop cone COGS 112 scoop cone COGS 183 scoop cone COGS 181 scoop oup COGS 202 scoop cup COGS 213 scoop cup COGS 22 Sundaes Small COGS 23 Sundaes Medium COGS 34 Sundaes Large COGS 25 Shakes Small COGS 26 Shakes Medium COGS 27 Shakes Large COGS 38 Extra toppings COGS 29 Soda COGS 12 Grading citera Shert 1 Sheet 2 Cestone Au B $3.25 54.25 $5.25 $3.95 $4.85 $5.50 $1.65 $1.80 12.05 $0.20 $1.85 $1.25 $1.60 $1.90 $2.20 $1.75 $2.05 $2.35 $1.65 $1.85 $2.05 $1.65 $1.00 $2.05 $0.20 50.05 C D Accounting E --- F A M - 7 D Meme 56365 KU 27 Shakes Large COGS 28 Extra toppings COGS Soda COGS 30 Water COGS 21: -XSA Fixed Costs 33 Rant Phone 35: Electricity 36 Insurance 37 Advertising 30 39 Operating Information of days open (weekdays) of days open (weekends) 42 Hours open (weekdays) 43 Hours open (weekends) Hourly wage Customers per hour weekdays Customers per hour weekends s 47 Hourly employees/day (weekdays) 4 Hourly employeesiday (weekends) 149 B 13% of customers purchasing Small Shakes 5% of customers purchasing Medium Shakes 33% of customers purchasing Large Shakes of Paali Grading oteta Sheet1 Sheet 2 MA $ tu du A 50% of customers purchasing 1 scoop Ice Cream Cones/cups 51% of customers purchasing 2 scopps Ice Gream Conesloupe 12% of customers purchasing 3 scoops loe Cream Cones/cups $2.05 50.20 $0.95 0.80 C D Accounting E P 3 G M - E-- D- NO WL 41 Customers per hour weekdays Customers per hour weekends 43 Hourly employees/day (weekdays) Hourly employees/day (weekends) 59 % of customers purchasing 1 scoop loe Cream Conesloups 31% of customers purchasing 2 scopps ice Cream Cones/cups 12% of customers purchasing 3 scoops Ice Cream Cones/cups 53% of customers purchasing Small Shakes 54% of customers purchasing Medium Shakes 55% of customers purchasing Large Shakes WEL of customers purchasing Smat Sundaes 5% of customers puchasing Medium Sundaes 36% of customers 3% of customers 10% of customers purchasing bottled water % of customers purchasing additional toppings purchasing Large Sundaes purchasing soda 63 Manager Annu Salary 64 Assistant Manager Annual Salary 65 Growth rate Weeks Per Month Loan period (years) 20 Loan interest rate 4 72 Taxes 73f income before tax is equal or greater than Grading criteria Sheet1 Sheet: de W Tax rate MY IN Manager Annual Salary Assistant Manager Annual Salary Growth rate Weeks Per Month erg ver Loan period (years) Loan interest rate Taxes: if income before tax is equal or greater than If income before tax is less than Sheet: Sheet 2 Sheet 3 Grading ofteria Mode Automa We a C Tax rate Tax rate= D H 1 Start Up Costs: a Kitchen equipment A Sales equipment (cash register, etc.) Initial inventory Pre-opening marketing >Fixtures/Furniture (chairs, tables etc.) Oil painting Licenses In Security deposit 11 Initial insurance payment 17 Total 13 Owner's Equity 15 Cash Reserves 16 Loan Amount 17 18 15 RAREAR 21 25 Grading criteria Sheet1 Short Sheet CMW $10,250.00 $1,550.00 $5,000.00 $1,500.00 $4,500.00 $ 600.00 $ 4.500.00 $1,500.00 D T F C T 1 4 Sprinkles Ice Cream Shoppe Please read the instructions carefully. A new client of yours, Marsha Johnson 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 August 1, 2022. Marsha 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 plan 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. Do not remove the Grading Criteria Worksheet. 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 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 properly labeled) and they are appropriate for business use 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 properly labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Sugarcones, ice cream cups, milkshakes, sundaes, toppings, soda and bottle water. Product Selling Prices: I scoop Cup -$3.25 2 scoops Cup-$4.25 3 scoops Cup-$5.25 I scoop Sugar Cone-$3.25 2 scoops Sugar Cone-$4.25 3 scoops Sugar Cone-$5.25 Small Shake - $3.95 Medium Shake - $4.85 Large Shake-$5.50 Small Sundae (1 topping)-$3.60 Medium Sundae (1 topping) - $4.65 Large Sundae (1 topping) - $5.75 Additional topping: 60 per topping Soda -$1.85 a bottle Bottled Water - $1.25 a bottle Cost of Goods Sold: Ice Cream Cones (Ice cream ingredients and cone) cost $1.60 for 1 scoop and .30 cents extra for each additional scoop Ice cream Cups (Ice cream ingredients and cup) cost $1.75 and 30 cents per extra for each additional scoop Small Shake cost $1.65 Medium Shake cost $1.80 Large Shake cost $2.05 Small Sundae cost $1.65 Medium Sundae cost $1.85 Large Sundae cost $2.05 Additional Toppings cost $.20 Sodas cost about $.95 per 16 oz. bottle Water cost $.8 per 16oz bottle The building rent is $2500 per month. Phone/Internet will cost about $1000 per month. Electricity should cost about $1300 a month. Insurance will be $1000 a month. Advertising and promotion will be $700 a month. $1.65 cost $1,80 3 of 6 $2.05 cost $1.65 Medium Sundae cost $1.85 Large Sundae cost $2.05 Additional Toppings cost $.20 Sodas cost about $ 95 per 16 oz. bottle Water cost $.8 per 160x bottle The building rent is $2500 per month. Phone/Internet will cost about $1000 per month Electricity should cost about $1300 a month. Insurance will be $1000 a month Advertising and promotion will be $700 a month. Operating Hours: Sprinkles will be open seven days a week Sprinkles will serve ice cream products all day and will be open 11am-8pm on weekdays (Monday-Thursday, Sunday). 11am-11pm Friday, Saturday Employees: . Two hourly employees on Friday, Saturday . One hourly employee needed during Monday-Thursday, Sunday . Either an assistant manager or manager is required during the Sprinkles Operating hours . Your client will be the manager and draw a salary of $39,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 $27,400 per year(including benefits) Approximately 60% Hourly workers will be paid $12 an hour. Demand Rate: Monday- Thursday, Sunday the owner expects 12 customers per hour. Friday-Saturday the owner expects an average of 22 customers per hour... 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 loe Cream cups 1/3 of all customer will buy 3 scoop 1/4 of all customers will buy Shakes 1/4 of all customers will buy Large Shakes D 1/2 of all customers will buy Small shakes 1/4 of all customers will buy a Large 1/3 of all customers will buy a Medium Sundae Sundae 1/4 of all customers will buy Small Sundae 314 of all customers will buy a Soda 1/4 of all customers will buy a Bottled Water 1/4 of all customers will buy an additional topping 6 Cream cups Monday-Thursday, 4 of 6 Sunday the owner expects 12 customers per hour. urday the owner expects an average of 22 customers per hour. 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 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 be ill buy 3 3 scoop lo 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 shakes Small 1/4 of all customers will buy a Large Sundae 1/3 of all customers will buy a Medium S Sundae 1/4 of all 3/4 of tomers wh 1/4 of all customers will buy a Bottled Water 1/4 of all customers will buy an additional topping Start-up costs Kitchen equipment: $10,250 Cash register and sales equipment: $1,550 $5,000 Initial inventory: t Pre-opening marketing: $1,500 Store fixtures (chairs, tables etc.): $4,500 Artwork prints: $600 Security deposit $4,500 First Insurance Payment: $1,500 Your client has $15,000 and plans to borrow the rest from the bank with a five-year loan at 3.4% interest. You are to calculate the monthly loan payment using the appropriate financial function Assume a tax rate of 22% if income Before Taxes (BT) is equal to or is greater than $28.500. Assume a tax rate of 15% if Income Before Taxes is less than $28.499. 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 favored 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 cnets him nnly $1 70 tonurchase. 5 of 6 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.70 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 $175 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 $17.95 and the large cake is $24.95. Costs are $5.45 per small cake and $8.40 per large cake. It will cost her $3,500 to purchase the equipment and insurance costs would rise by another $275 per month due to the hot equipment needed to make the cakes. What is your recommendation? Would it be profitable to sell the cakes? See Grading Rubric attached to assignment or Grading Criteria Worksheet in Excel file. Revenue: 1 scoop cone 2 scoop cone 3 scoop cone 1 scoop cup 2 scoop cup 3 scoop cup Sundaes Small Sundaes Medium Sundaes Large Shakes Small Shakes Medium Shakes Large Soda Bottled Water Extra toppings Monthly Revenue Expenses: COGS Rent Th Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 1 Sundaes Large Shakes Small Shakes Medium Shakes Large Soda Bottled Water Extra toppings Monthly Revenue Expenses: COGS Rent Phone Electricity Insurance Advertising Hourly Wages Salaries Loan Payment Total Expenses Income Before Tax Tax Start Up Costs: Kitchen equipment Sales equipment (cash register, etc.) Initial inventory Pre-opening marketing Fixtures/Furniture (chairs, tables etc.) Oil painting Licenses Security deposit Initial insurance payment Total Owner's Equity Cash Reserves Loan Amount Sprinkles Ice Cream Shoppe Please read the instructions carefully. A new dient of yours. Marsha Johnson 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 August 1, 2022. Marsha 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 plan 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 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. . Do not remove the Grading Criteria Worksheet. 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 coll on the 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 Do not remove the Grading Criteria Worksheet . 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 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 Not 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 (.e. appropriate title, legend where appropriate, data series property labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Sugar cones, ice cream cups, milkshakes, sundaes, toppings, soda and botte water. Product Selling Prices: scoop Cup-$3.25 2 scoops Cup-$4.25 3 scoops Cup-$5.25 1 scoop Sugar Cone - $3.25 2 scoops Sugar Cone-$4.25 3 scoops Sugar Cone-$5.25 Small Shake-$3.95 Medium Shake-$4.85 Large Shake-$5.50 Small Sundae (1 topping)-$3.60 Medium Sundae (1 topping)-$4.65 Large Sundae (1 topping)-$5.75 Additional topping: 60 per topping 1 scoop Cup-$3.25 2 scoops Cup-$4.25 3 scoops Cup-$5.25 I scoop Sugar Cone-$3.25 2 scoops Sugar Cone-$4.25 3 scoops Sugar Cone -$5,25 Small Shake-$3.95 Medium Shake-$4.85 Large Shake-$5.50 Small Sundae (1 topping) $3.00 Medium Sundae (1 topping)-$4.65 Large Sundae (1 topping)-$5.75 Additional topping: 60 per topping Soda -$1.85 a bottle Bottled Water-$1.25 a bottle Cost of Goods Sold: Ice Cream Cones (Ice cream ingredients and cone) cost $1.60 for 1 scoop and .30 cents extra for each additional scoop ice cream Cups (lce cream ingredients and cup) cost $1,75 and 30 cents per extra for each additional scoop Small Shake cost $1.65 Medium Shake cost $1.80 Large Shake cost $2.05 Small Sundae cost $1.65 0 Medium Sundae cost $1.85 Large Sundae cost $2.05 Additional Toppings cost $.20 Sodas cost about $.95 per 16 oz. bottle Water cost $.8 per 160 bottle The building rent is $2500 per month Phone/Internet will cost about $1000 per month Electricity should cost about $1300 a month. Insurance will be $1000 a month. Advertising and promotion will be $700 a month Operating Hours: Sprinkles will be open seven days a week. Sprinkles will serve ice cream products all day and will be open 11am-8pm on weekdays (Monday-Thursday, Sunday) 11am-11pm Friday, Saturday Employees Two hourly employees on Friday, Saturday One hourly employee needed during Monday-Thursday, Sunday . Either an assistant manager or manager is required during the Sprinkles Operating hours Your client will be the manager and draw a salary of $39,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 $27,400 per year(including benefits) Approximately 60% Medum Sundae cost $1.85 Large Sundae cost $2.05 Additional Toppings cost $.20 Sodas cost about $.95 per 16 oz. bole Water cost $8 per 1602 bottle The building rent is $2500 per month. Phone/internet will cost about $1000 per month Electricity should cost about $1300 a month. Insurance will be $1000 a month Advertising and promotion will be $700 a month. Operating Hours: Sprinkles will be open seven days a week. Sprinkles will serve ice cream products all day and will be open 11am-8pm on weekdays (Monday-Thursday, Sunday) 11am-11pm Friday, Saturday Employees Two hourly employees on Friday, Saturday One hourly employee needed during Monday-Thursday, Sunday Eer an assistant manager or manager is required during the Sprinkles Operating hours Your client will be the manager and draw a salary of $30,000 per year (including benef the assistant manager Approximately She will work during the busiest times and fill i 40% The assistant manager will receive a salary of $27.400 per year including benefits Approximately 60% Houdy workers al bend $12 an hour Approximately 60% Hourly workers will be paid $12 an hour. Demand Rate: Monday Thursday, Sunday the owner expects 12 customers per hour Friday-Saturday the owner expects an average of 22 customers per hour. Product demand on average: 1/2 of all customers will buy 1 scoop loe Cream Cones 1/2 of all customers will buy 2 scoop Ice Cream Cones 13 of a customer will buy 3 scoop Ice Cream Cones 1/2 of all customers will buy t sooop loe Cream cups of all customers will buy 2 scoop Ice Cream cups 1/2 1/3 of at customer will buy 3 scoop loe Cream ups 1/4 of all customers will buy Medium Shakes 20 19 1/4 of all customers will buy Large Shakes 12 of at customers will buy Smal shakes 14 of all customers will buy a Large Sundae # of all customers will buy a Medium Sundae 1/4 of all customers will buy Small Bundan 34 of all customers will buy a Soda 1/4 of at customers will buy a Bottled Water 1/4 of all customers will buy an additional topping Start-up costs Kitchen equipment: $10,250 Cash register and sales equipment $1,550 Customers wit buy a bomed Water o 1/4 of all customers will buy an additional topping Start-up costs Kitchen equipment: $10,250 Cash register and sales equipment: $1,550 Initial inventory: $5,000 Pre-opening marketing: $1,500 Store fixtures (chairs, tables etc.): $4,500 Artwork prints: $600 Security deposit $4,500 First Insurance Payment: $1,500 Your client has $15.000 and plans to borrow the rest from the bank with a five-year loan at 3.4% interest. You are to calculate the monthly loan payment using the appropriate financial function Assume a tax rate of 22% if Income Before Taxes (BT) is equal to or is greater than $28,500 Assume a tax rate of 15% f income Before Taxes is less than $28.499 You are to calculate the monthly sax 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 che forma. This will be approximately a 2-3 paragraph endeavor. Scenario One: "What if Analysis for adding flavored ice coffee. Your client is unsure if she should sel flavored ice coffees. She thinks she can sell a coffee to every second customer and it seems to be lucrative because the coffee sols for $3.75 each and costs him only $1.70 to purchase. Unfortunately your client is afraid that he would cannibalize his soft drink sales with the coffee purchase the customers (one soft drink less for every coffee sold), it will cost him $5,250 equipment and insurance costs would rise by another $175 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 soll 20 smal cakes a week and 25 large cakes a week. It seems to be lucrative because the small cake is $17.95 and the large coke is $24.95 Costs are 15 45 per small cake nod 140 per me con Scenario One: "What Ir Analysis for adding flavored ice coffees Your cent is unsure if she should sell favored every second customer and seems t costs him only purchase Unfortunately your client is afraid customers (one soft drink less equipment and insuran needed to What is your recommendation fihould y She thinks she because the and $40 thinks she can scoo coffee sets for $3.75 and Scenari Two: "What Analysis Your cllent would like to consider adding ice cream cakes to the list of items available for sale. She is wanting to c cakes with customized cake, ice cream decorations cannibalas his soft drink sales with the coffee will cost him $5.250 to purchase the month due to the not equipment to her custome $275 and 25 large cars a week targe cake is $24.95 Costs a asems to be turative $5.45 per make $3.500 to purchase the equipment and insurance costs would me by anothe equipment needed to make the on? Would it be posted the Se Grading Rute attached to assentor Grading Co Worksh Revenue: 1 scoop cone 2 scoop cone 73 scoop cone 1 scoop cup wwwwww scoop cup scoop cup Sundaes Small Sundsee Medium Sundaes Large 14 Shakes Smal 15 Shakes Medium M 19 3D 15 Shakes Large Soda 17 37 Botted Water 19 Extra toppings 20 Monthly Revenue 21 22 Expenses 23 COGS 24 Rent XSA 25 Phone 2 25 Electricity 27 Insurance Advertising 29 Hourly Wages 30 Salaries Loan Pement 9 0 Grading tele Sheet1 Sheet H 1 H Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Jo R Total D 20 Monthly Revenue 21 22 Expenses: 23 COGS 24 Rent 25 Phone 26 Electricity 27 insurance 25 Advertising 29 Hourly Wages 30 Salaries 31 Loan Payment 32 Total Expenses 33 34Income Before Tax 35 Tax 36 Net Income 37 Cash Flow 30 39 40 HE 46 47 48 La Maha B Grading crte Sheet 1 St 2 HO O D E N 0 548 WA -XVA Assumptions Made Products: 1 scoop consicup price 2 scoop cone/oup price 3 scoop cone/oup price Sundaes Small price 7 Sundaes Medium price Sundaes Large price Shakes Small price 10 Shakes Medium price 11 Shakes Large price 12 Extra toppings price 13 Soda price 14 Water price 15 161 scoop cone COGS 112 scoop cone COGS 183 scoop cone COGS 181 scoop oup COGS 202 scoop cup COGS 213 scoop cup COGS 22 Sundaes Small COGS 23 Sundaes Medium COGS 34 Sundaes Large COGS 25 Shakes Small COGS 26 Shakes Medium COGS 27 Shakes Large COGS 38 Extra toppings COGS 29 Soda COGS 12 Grading citera Shert 1 Sheet 2 Cestone Au B $3.25 54.25 $5.25 $3.95 $4.85 $5.50 $1.65 $1.80 12.05 $0.20 $1.85 $1.25 $1.60 $1.90 $2.20 $1.75 $2.05 $2.35 $1.65 $1.85 $2.05 $1.65 $1.00 $2.05 $0.20 50.05 C D Accounting E --- F A M - 7 D Meme 56365 KU 27 Shakes Large COGS 28 Extra toppings COGS Soda COGS 30 Water COGS 21: -XSA Fixed Costs 33 Rant Phone 35: Electricity 36 Insurance 37 Advertising 30 39 Operating Information of days open (weekdays) of days open (weekends) 42 Hours open (weekdays) 43 Hours open (weekends) Hourly wage Customers per hour weekdays Customers per hour weekends s 47 Hourly employees/day (weekdays) 4 Hourly employeesiday (weekends) 149 B 13% of customers purchasing Small Shakes 5% of customers purchasing Medium Shakes 33% of customers purchasing Large Shakes of Paali Grading oteta Sheet1 Sheet 2 MA $ tu du A 50% of customers purchasing 1 scoop Ice Cream Cones/cups 51% of customers purchasing 2 scopps Ice Gream Conesloupe 12% of customers purchasing 3 scoops loe Cream Cones/cups $2.05 50.20 $0.95 0.80 C D Accounting E P 3 G M - E-- D- NO WL 41 Customers per hour weekdays Customers per hour weekends 43 Hourly employees/day (weekdays) Hourly employees/day (weekends) 59 % of customers purchasing 1 scoop loe Cream Conesloups 31% of customers purchasing 2 scopps ice Cream Cones/cups 12% of customers purchasing 3 scoops Ice Cream Cones/cups 53% of customers purchasing Small Shakes 54% of customers purchasing Medium Shakes 55% of customers purchasing Large Shakes WEL of customers purchasing Smat Sundaes 5% of customers puchasing Medium Sundaes 36% of customers 3% of customers 10% of customers purchasing bottled water % of customers purchasing additional toppings purchasing Large Sundaes purchasing soda 63 Manager Annu Salary 64 Assistant Manager Annual Salary 65 Growth rate Weeks Per Month Loan period (years) 20 Loan interest rate 4 72 Taxes 73f income before tax is equal or greater than Grading criteria Sheet1 Sheet: de W Tax rate MY IN Manager Annual Salary Assistant Manager Annual Salary Growth rate Weeks Per Month erg ver Loan period (years) Loan interest rate Taxes: if income before tax is equal or greater than If income before tax is less than Sheet: Sheet 2 Sheet 3 Grading ofteria Mode Automa We a C Tax rate Tax rate= D H 1 Start Up Costs: a Kitchen equipment A Sales equipment (cash register, etc.) Initial inventory Pre-opening marketing >Fixtures/Furniture (chairs, tables etc.) Oil painting Licenses In Security deposit 11 Initial insurance payment 17 Total 13 Owner's Equity 15 Cash Reserves 16 Loan Amount 17 18 15 RAREAR 21 25 Grading criteria Sheet1 Short Sheet CMW $10,250.00 $1,550.00 $5,000.00 $1,500.00 $4,500.00 $ 600.00 $ 4.500.00 $1,500.00 D T F C T 1 4 Sprinkles Ice Cream Shoppe Please read the instructions carefully. A new client of yours, Marsha Johnson 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 August 1, 2022. Marsha 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 plan 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. Do not remove the Grading Criteria Worksheet. 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 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 properly labeled) and they are appropriate for business use 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 properly labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Sugarcones, ice cream cups, milkshakes, sundaes, toppings, soda and bottle water. Product Selling Prices: I scoop Cup -$3.25 2 scoops Cup-$4.25 3 scoops Cup-$5.25 I scoop Sugar Cone-$3.25 2 scoops Sugar Cone-$4.25 3 scoops Sugar Cone-$5.25 Small Shake - $3.95 Medium Shake - $4.85 Large Shake-$5.50 Small Sundae (1 topping)-$3.60 Medium Sundae (1 topping) - $4.65 Large Sundae (1 topping) - $5.75 Additional topping: 60 per topping Soda -$1.85 a bottle Bottled Water - $1.25 a bottle Cost of Goods Sold: Ice Cream Cones (Ice cream ingredients and cone) cost $1.60 for 1 scoop and .30 cents extra for each additional scoop Ice cream Cups (Ice cream ingredients and cup) cost $1.75 and 30 cents per extra for each additional scoop Small Shake cost $1.65 Medium Shake cost $1.80 Large Shake cost $2.05 Small Sundae cost $1.65 Medium Sundae cost $1.85 Large Sundae cost $2.05 Additional Toppings cost $.20 Sodas cost about $.95 per 16 oz. bottle Water cost $.8 per 16oz bottle The building rent is $2500 per month. Phone/Internet will cost about $1000 per month. Electricity should cost about $1300 a month. Insurance will be $1000 a month. Advertising and promotion will be $700 a month. $1.65 cost $1,80 3 of 6 $2.05 cost $1.65 Medium Sundae cost $1.85 Large Sundae cost $2.05 Additional Toppings cost $.20 Sodas cost about $ 95 per 16 oz. bottle Water cost $.8 per 160x bottle The building rent is $2500 per month. Phone/Internet will cost about $1000 per month Electricity should cost about $1300 a month. Insurance will be $1000 a month Advertising and promotion will be $700 a month. Operating Hours: Sprinkles will be open seven days a week Sprinkles will serve ice cream products all day and will be open 11am-8pm on weekdays (Monday-Thursday, Sunday). 11am-11pm Friday, Saturday Employees: . Two hourly employees on Friday, Saturday . One hourly employee needed during Monday-Thursday, Sunday . Either an assistant manager or manager is required during the Sprinkles Operating hours . Your client will be the manager and draw a salary of $39,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 $27,400 per year(including benefits) Approximately 60% Hourly workers will be paid $12 an hour. Demand Rate: Monday- Thursday, Sunday the owner expects 12 customers per hour. Friday-Saturday the owner expects an average of 22 customers per hour... 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 loe Cream cups 1/3 of all customer will buy 3 scoop 1/4 of all customers will buy Shakes 1/4 of all customers will buy Large Shakes D 1/2 of all customers will buy Small shakes 1/4 of all customers will buy a Large 1/3 of all customers will buy a Medium Sundae Sundae 1/4 of all customers will buy Small Sundae 314 of all customers will buy a Soda 1/4 of all customers will buy a Bottled Water 1/4 of all customers will buy an additional topping 6 Cream cups Monday-Thursday, 4 of 6 Sunday the owner expects 12 customers per hour. urday the owner expects an average of 22 customers per hour. 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 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 be ill buy 3 3 scoop lo 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 shakes Small 1/4 of all customers will buy a Large Sundae 1/3 of all customers will buy a Medium S Sundae 1/4 of all 3/4 of tomers wh 1/4 of all customers will buy a Bottled Water 1/4 of all customers will buy an additional topping Start-up costs Kitchen equipment: $10,250 Cash register and sales equipment: $1,550 $5,000 Initial inventory: t Pre-opening marketing: $1,500 Store fixtures (chairs, tables etc.): $4,500 Artwork prints: $600 Security deposit $4,500 First Insurance Payment: $1,500 Your client has $15,000 and plans to borrow the rest from the bank with a five-year loan at 3.4% interest. You are to calculate the monthly loan payment using the appropriate financial function Assume a tax rate of 22% if income Before Taxes (BT) is equal to or is greater than $28.500. Assume a tax rate of 15% if Income Before Taxes is less than $28.499. 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 favored 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 cnets him nnly $1 70 tonurchase. 5 of 6 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.70 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 $175 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 $17.95 and the large cake is $24.95. Costs are $5.45 per small cake and $8.40 per large cake. It will cost her $3,500 to purchase the equipment and insurance costs would rise by another $275 per month due to the hot equipment needed to make the cakes. What is your recommendation? Would it be profitable to sell the cakes? See Grading Rubric attached to assignment or Grading Criteria Worksheet in Excel file. Revenue: 1 scoop cone 2 scoop cone 3 scoop cone 1 scoop cup 2 scoop cup 3 scoop cup Sundaes Small Sundaes Medium Sundaes Large Shakes Small Shakes Medium Shakes Large Soda Bottled Water Extra toppings Monthly Revenue Expenses: COGS Rent Th Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 1 Sundaes Large Shakes Small Shakes Medium Shakes Large Soda Bottled Water Extra toppings Monthly Revenue Expenses: COGS Rent Phone Electricity Insurance Advertising Hourly Wages Salaries Loan Payment Total Expenses Income Before Tax Tax Start Up Costs: Kitchen equipment Sales equipment (cash register, etc.) Initial inventory Pre-opening marketing Fixtures/Furniture (chairs, tables etc.) Oil painting Licenses Security deposit Initial insurance payment Total Owner's Equity Cash Reserves Loan Amount

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Oxford Guide To Financial Modeling

Authors: Thomas S Y Ho, Sang Bin Lee

1st Edition

019516962X, 9780195169621

More Books

Students also viewed these Finance questions