PLEASE COMPLETE ALL STEPS, IM IN DESPATATE HELP :(
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 January 1, 2022. Lilly 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. 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). Lagin valoare Login Page & Welcome age Spec.com My Account Log 216 Chart One Monthly Product Revenue - thin 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 inom for the year to determine trends or projection in products Make sure both charts are formatted correctly be appropriate the ingend were appropriate, data series properly labeled and they are appropriate for business use Information needed to complete assignment Products Suwcones, koe cream cup, miestains, unde, toppings, soda and botte water Product Selling Prices: scoop Cup or Sugar Cone - $225 2 scoops Cup or Sugar Cone $3.25 3 scoops Cup or Sugar Cone-5.25 scoop Sugar Cone $2.25 2 scoops Sugar Cone-53.25 3000 Sugar C-1425 Smal Shake-52.95 Medium Shake - $375 Large Shalen - 50 mal Sundae (trapping - $255 Medium Sundae (ing) 19.55 Large Sundae oppia - $465 Additional toping 50 per topping Soda $1.25 Bonded Water - 5100 bottle 56F Clear A 6 7 8 9 0 Cost of Goods Sold: Ice Cream Conca (lo cream Ingredients and cone) cost $1.10 for 1 scoop and 20 cents extra for each additional scoop toe cream Cups (Ice cream Ingredients and cup) cost $1.25 and 20 cents per extra for each additional scoop Small Shake cost $1.40 Medium Shake coat $1.55 Large Shake cost $1.75 Small Sundae cost $1.35 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/Intemet will cost about $1000 per month. Electricity should cost about $1200 a month. Insurance will be $900 a month Advertising and promotion will be $300 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 - 8 pm on weekdaya (Monday - Thursday, Sunday). 11am-11pm Friday, Saturday Employees: Two hourly employees on Friday, Saturday One hourly employee needed during Monday - Thursday, Sunday Elther an assistant manager or manager la 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 time 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, Sunday the owner expects 10 customers per hour Friday - Saturday the owner expects an average of 20 customers per hour o 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 112 of all customers will buy 1 scoon Ice Cream.cung WOW WOW o 1/2 of all customer will buy 2 scoop Ice Cream Gups 2 o 1/3 of all customer will buy 3 scoop Ice Cream cups o 1/4 of all customers will buy Medium Shakes 1/4 of all customers will buy Large Shakes o 1/2 of all customers will buy Small shakes o 1M of all customers will buy a Large Sundae 1/3 of all customers will buy a Medium Sundee o 1/4 of all customers will buy Small Sundan o 3/4 of all customers will buy a Soda o 1/4 of all customers will buy a Bottled Water 1/4 of all customers will buy an additional topping Start-up costs Kitchen equipment: $10,250 Cash register and sales equipment: $1,350 Initial Inventory: $4,500 Pre-opening marketing: $1,500 Store fixtures (chairs, tables eta.): $4,500 Oil paintings of your client's momma and grandma to hang on the wall: $360 Licenses: $1,025 Security deposit $4,500 First Insurance Payment: $760 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 in greater than $13,500 Assume a tax rate of 13% BT is less than $13,500. You are to calculate the monthly tax payment using the appropriate logical function. Assume that soles 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 scenarlo 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. Scenarlo One: "What It" 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? # ca - G.A. Mergea Gent Alumn S - % Conditional Format Cell forming the way Delete- amma- Sortid Tried Lety A23 - B F G 1 C D Your current score: M N Sheets are apply labeled 5 points Al formation cells on cash flow referenced back to the sumpt 35 points Asumptions and startup worksheets and complete 15 points 6. Recommendations are based on the poor formation and what ani 15 ports 7 The charts present and each work that corn 10 punts Teclad corectly wig boolean loge 10 points Loan payuncated using a function 10 points 10 Score foot of 1001 11 12 13 14 15 16 13 2 3M 25 21 Grading mata het Shoot Alignment Nuntet Styles Reven: Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 3 Month 9 Month 10 Month 11 Month 12 Total con pe ce 3deoco tug Sunda Me Tudose Sharma Soda Bowled ting Marvence 22 Expenses COGS 26 15 Phone Zhe in Adreng Hager neon eine Catho Ording to Sheet1 Sheet Sheet *TU. - A - Z Mnge & Conte AN 5. % Conditionem Formatting Styles - Roma Tal C An Sundaes Medium COGS c H M 0 A Mader Products Coocant cupce coco come up ce Sudarso Sund Medium shemale Medium these TECH www opone con 2 CCOON coco scoop Up OOGS 20.co 1 cup COGS COGS LED SCORES ChaCO3 B MemCOGS Ses Large CO co Sheet Sheet of cutomets purchasing Medium Sundan customer purchasing Sunds of customers chasing sodas to purchasing bottled water of customers purchasing additional toping Manager Annual Stary Manager versal Swary Growth rate 67 Weeks Per Monch Lean penod was 70 Loon interest rate 71 2. Taxes income before taxis al or than All income before taxi than 15 Taxtate Taxe HT 1 79 2 + Ording to Sheet Sheet2 Sheet Rady A India instance payment G D Start Up Costs: > Kitchen equipment Sales equipment (cash register, etc.) Initial inventory Pre-opening marketing 2 Fixtures/Furniture (chairs, tables etc.) Oil painting Licenses 10 Security deposit 11 Initial insurance payment 12 Total Owner's Equity Cash Reserves 16 Loan Amount 17 1 ente 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 January 1, 2022. Lilly 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. 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). Lagin valoare Login Page & Welcome age Spec.com My Account Log 216 Chart One Monthly Product Revenue - thin 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 inom for the year to determine trends or projection in products Make sure both charts are formatted correctly be appropriate the ingend were appropriate, data series properly labeled and they are appropriate for business use Information needed to complete assignment Products Suwcones, koe cream cup, miestains, unde, toppings, soda and botte water Product Selling Prices: scoop Cup or Sugar Cone - $225 2 scoops Cup or Sugar Cone $3.25 3 scoops Cup or Sugar Cone-5.25 scoop Sugar Cone $2.25 2 scoops Sugar Cone-53.25 3000 Sugar C-1425 Smal Shake-52.95 Medium Shake - $375 Large Shalen - 50 mal Sundae (trapping - $255 Medium Sundae (ing) 19.55 Large Sundae oppia - $465 Additional toping 50 per topping Soda $1.25 Bonded Water - 5100 bottle 56F Clear A 6 7 8 9 0 Cost of Goods Sold: Ice Cream Conca (lo cream Ingredients and cone) cost $1.10 for 1 scoop and 20 cents extra for each additional scoop toe cream Cups (Ice cream Ingredients and cup) cost $1.25 and 20 cents per extra for each additional scoop Small Shake cost $1.40 Medium Shake coat $1.55 Large Shake cost $1.75 Small Sundae cost $1.35 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/Intemet will cost about $1000 per month. Electricity should cost about $1200 a month. Insurance will be $900 a month Advertising and promotion will be $300 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 - 8 pm on weekdaya (Monday - Thursday, Sunday). 11am-11pm Friday, Saturday Employees: Two hourly employees on Friday, Saturday One hourly employee needed during Monday - Thursday, Sunday Elther an assistant manager or manager la 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 time 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, Sunday the owner expects 10 customers per hour Friday - Saturday the owner expects an average of 20 customers per hour o 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 112 of all customers will buy 1 scoon Ice Cream.cung WOW WOW o 1/2 of all customer will buy 2 scoop Ice Cream Gups 2 o 1/3 of all customer will buy 3 scoop Ice Cream cups o 1/4 of all customers will buy Medium Shakes 1/4 of all customers will buy Large Shakes o 1/2 of all customers will buy Small shakes o 1M of all customers will buy a Large Sundae 1/3 of all customers will buy a Medium Sundee o 1/4 of all customers will buy Small Sundan o 3/4 of all customers will buy a Soda o 1/4 of all customers will buy a Bottled Water 1/4 of all customers will buy an additional topping Start-up costs Kitchen equipment: $10,250 Cash register and sales equipment: $1,350 Initial Inventory: $4,500 Pre-opening marketing: $1,500 Store fixtures (chairs, tables eta.): $4,500 Oil paintings of your client's momma and grandma to hang on the wall: $360 Licenses: $1,025 Security deposit $4,500 First Insurance Payment: $760 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 in greater than $13,500 Assume a tax rate of 13% BT is less than $13,500. You are to calculate the monthly tax payment using the appropriate logical function. Assume that soles 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 scenarlo 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. Scenarlo One: "What It" 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? # ca - G.A. Mergea Gent Alumn S - % Conditional Format Cell forming the way Delete- amma- Sortid Tried Lety A23 - B F G 1 C D Your current score: M N Sheets are apply labeled 5 points Al formation cells on cash flow referenced back to the sumpt 35 points Asumptions and startup worksheets and complete 15 points 6. Recommendations are based on the poor formation and what ani 15 ports 7 The charts present and each work that corn 10 punts Teclad corectly wig boolean loge 10 points Loan payuncated using a function 10 points 10 Score foot of 1001 11 12 13 14 15 16 13 2 3M 25 21 Grading mata het Shoot Alignment Nuntet Styles Reven: Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 3 Month 9 Month 10 Month 11 Month 12 Total con pe ce 3deoco tug Sunda Me Tudose Sharma Soda Bowled ting Marvence 22 Expenses COGS 26 15 Phone Zhe in Adreng Hager neon eine Catho Ording to Sheet1 Sheet Sheet *TU. - A - Z Mnge & Conte AN 5. % Conditionem Formatting Styles - Roma Tal C An Sundaes Medium COGS c H M 0 A Mader Products Coocant cupce coco come up ce Sudarso Sund Medium shemale Medium these TECH www opone con 2 CCOON coco scoop Up OOGS 20.co 1 cup COGS COGS LED SCORES ChaCO3 B MemCOGS Ses Large CO co Sheet Sheet of cutomets purchasing Medium Sundan customer purchasing Sunds of customers chasing sodas to purchasing bottled water of customers purchasing additional toping Manager Annual Stary Manager versal Swary Growth rate 67 Weeks Per Monch Lean penod was 70 Loon interest rate 71 2. Taxes income before taxis al or than All income before taxi than 15 Taxtate Taxe HT 1 79 2 + Ording to Sheet Sheet2 Sheet Rady A India instance payment G D Start Up Costs: > Kitchen equipment Sales equipment (cash register, etc.) Initial inventory Pre-opening marketing 2 Fixtures/Furniture (chairs, tables etc.) Oil painting Licenses 10 Security deposit 11 Initial insurance payment 12 Total Owner's Equity Cash Reserves 16 Loan Amount 17 1 ente
