Perez POPS Please read the instructions carefully. A new client of yours, Katie Perez wants to...

Transcribed Image Text:

Perez POPS Please read the instructions carefully. A new client of yours, Katie Perez wants to open an ice cream shop near the downtown shopping center. She will selling Frozen POPS, Frozen Ices, Frozen POPS Packs, Soda and Bottled Water. Her grand opening will be June 1, 2023. Katie 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 - "Perezpops.xls" 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 additional worksheets for your What if Scenario recommendation. Events 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 (ie. appropriate title, legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Specialty POPS, Frozen Ice, Soda and Bottle water. Product Selling Prices: POPS Fruit Explosion $4.75 Vanilla Vanilla $3.75 Chocolate Mousse $4.25 Mango Tangerine Surprise $4.25 Strawberry Crme $3.75 Pina Colada $4.75 Strawberry Kiwi $4.25 Orange Crme $3.75 Chocolate sea salt $4.25 Cookies & Crme $3.25 Pop Packs 12pk $48.00 6pk $24.00 Frozen Ice Medium $3.75 Large $4.25 Soda -$1.95 a bottle Bottled Water-$1.25 a bottle Cost of Goods Sold: POPS Fruit Explosion $0.90 VanillaVanilla $0.40 Chocolate Mousse $0.95 Mango Tangerine $0.70 Strawberry Crme $0.50 Pina Colada $0.90 Strawberry Kiwi $0.70 Orange Crme $0.50 Chocolate sea salt $0.95 Cookies & Crme $0.70 Pop Packs 12pk $7.20 6pk $4.20 Frozen Ice Medium $0.95 Large $1.10 Sodas cost about $.90 per 16 oz. bottle Water cost $.08 per 16 oz. bottle Demand Rates: % of customers purchasing Fruit Explosion 25% % of customers purchasing Vanilla Vanilla 30% % of customers purchasing Chocolate Mousse 30% % of customers purchasing Mango Tangerine Surprise 15% % of customers purchasing Strawberry Crme 30% % of customers purchasing Pina Colada % of customers purchasing Strawberry Kiwi 15% 20% % of customers purchasing Orange Crme 15% % of customers purchasing Chocolate sea salt % of customers purchasing Cookies & Crme % of customers purchasing Frozen Ice Medium % of customers purchasing Frozen Ice Large % of customers purchasing Pop 6pk % of customers purchasing Pop 12pk % of customers purchasing sodas 40% 35% 20% 15% 25% 15% 10% 10% % of customer purchasing water Operating Hours: Weekdays (Monday - Thursday). Open 8 hours. Weekends Friday, Saturday Open 12 hours. Employees: One hourly employee needed weekdays. Two hourly employees needed weekends. Hourly employees are paid $12.00 per hour. Your client will be the manager and draw a salary of $40,000 per year. The assistant manager will receive a salary of $28,500 per year. Product demand on average: Weekdays the owner expects 20 customers per hour. Friday-Saturday the owner expects an average of 32 customers per hour. Start-up costs Kitchen equipment: $12,250 Cash register and sales equipment: $2,550 Initial inventory: $5,000 Pre-opening marketing: $1,000 Store setup (chairs, tables, decor etc.): $3,500 Security deposit: $4,500 First Insurance Payment: $1,500 and then Insurance will be $1000 a month. The building rent is 3600.00 per month. Phone/Internet will cost about $1000 per month. Electricity should cost about $1300 per month. Advertising and promotion will be $500 a month. Your client has $20,000 and plans to borrow $45,000 from the bank with a five-year loan at 6.1% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 22% if Income Before Taxes (IBT) is equal to or is greater than $35,500. Assume a tax rate of 15% if Income Before Taxes (IBT) is less than $35,499. You are to calculate the monthly tax payment using the appropriate logical function. Assume that sales will grow at an average of 2.40% 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. Cs A Arial 12 B B D G Number H I 3 K M Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Revenue: Month 1 5 Fruit Explosion POPS) 7021.69 6 Vanilla Vanilla POPS 6652.80 7 Chocolate Mousse POP: 7539.84 8 Mango Tangerine POPS 3769.92 9 Strawberry Crme POPE 6652.80 10 Pina Colada POPS 4213.44 5026.56 4435.20 10053.12 6726.72 42577.92 35481.60 11 Strawberry Kiwi POPS 12 Orange Crme POPS 13 Chocolate Sea Salt POP 14 Cookies & Crme POPS 15 12 Pack POPS 16 6 Pack POPS 17 Medium Frozen Ice 18 Large Frozen Ice 19 Soda 20 Bottled Water 4435.20 3769.92 1153.15 730 20 Grading criteria Sheet 1 Sheet 2 Sheet 3 Give Feedback to Marsh 100% + 2 Data Review View Automate Help Comments Catch op Editing Stare Page Layout Formulas Arial 12 BA... C D E 21 Monthly Revenue 150249.08 22 23 Expenses: 24 COGS 25 Rent 26 Phone 27 Electricity 26154.89 3600.00 1000.00 1300.00 28 Insurance 1000.00 29 Advertising 500.00 30 Hourly Wages 4032.00 31 Salaries 5708.33 32 Loan Payment 872.07 33 Total Expenses 44667.29 34 35 Income Before Tax 105581.79 36 Tax 37 Net Income 23228.00 82353.79 Grading criteria Sheet 1 Sheet 2 Sheet 3 BYRON Number G H I K L M 14 Arial 14 B 4 2 Start Up GOLD. Kitchen equipment Cash Register and Sales equipment 5 Initial inventory 5 Pre-opening marketing $12,250.00 $ 2,550.00 $ 5,000.00 $ 1,000.00 Store Setup (chairs, tables, decor etc.) $ 3,500.00 Security deposit $ 4,500.00 $ 1,500.00 $30,300.00 Initial insurance payment 10 Total 1 Owner's Equity Cash Reserves Loan Amount Sheet Sheet 2 Sheet 3 Accounting E F G H 1 100% Perez POPS Please read the instructions carefully. A new client of yours, Katie Perez wants to open an ice cream shop near the downtown shopping center. She will selling Frozen POPS, Frozen Ices, Frozen POPS Packs, Soda and Bottled Water. Her grand opening will be June 1, 2023. Katie 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 - "Perezpops.xls" 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 additional worksheets for your What if Scenario recommendation. Events 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 (ie. appropriate title, legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Specialty POPS, Frozen Ice, Soda and Bottle water. Product Selling Prices: POPS Fruit Explosion $4.75 Vanilla Vanilla $3.75 Chocolate Mousse $4.25 Mango Tangerine Surprise $4.25 Strawberry Crme $3.75 Pina Colada $4.75 Strawberry Kiwi $4.25 Orange Crme $3.75 Chocolate sea salt $4.25 Cookies & Crme $3.25 Pop Packs 12pk $48.00 6pk $24.00 Frozen Ice Medium $3.75 Large $4.25 Soda -$1.95 a bottle Bottled Water-$1.25 a bottle Cost of Goods Sold: POPS Fruit Explosion $0.90 VanillaVanilla $0.40 Chocolate Mousse $0.95 Mango Tangerine $0.70 Strawberry Crme $0.50 Pina Colada $0.90 Strawberry Kiwi $0.70 Orange Crme $0.50 Chocolate sea salt $0.95 Cookies & Crme $0.70 Pop Packs 12pk $7.20 6pk $4.20 Frozen Ice Medium $0.95 Large $1.10 Sodas cost about $.90 per 16 oz. bottle Water cost $.08 per 16 oz. bottle Demand Rates: % of customers purchasing Fruit Explosion 25% % of customers purchasing Vanilla Vanilla 30% % of customers purchasing Chocolate Mousse 30% % of customers purchasing Mango Tangerine Surprise 15% % of customers purchasing Strawberry Crme 30% % of customers purchasing Pina Colada % of customers purchasing Strawberry Kiwi 15% 20% % of customers purchasing Orange Crme 15% % of customers purchasing Chocolate sea salt % of customers purchasing Cookies & Crme % of customers purchasing Frozen Ice Medium % of customers purchasing Frozen Ice Large % of customers purchasing Pop 6pk % of customers purchasing Pop 12pk % of customers purchasing sodas 40% 35% 20% 15% 25% 15% 10% 10% % of customer purchasing water Operating Hours: Weekdays (Monday - Thursday). Open 8 hours. Weekends Friday, Saturday Open 12 hours. Employees: One hourly employee needed weekdays. Two hourly employees needed weekends. Hourly employees are paid $12.00 per hour. Your client will be the manager and draw a salary of $40,000 per year. The assistant manager will receive a salary of $28,500 per year. Product demand on average: Weekdays the owner expects 20 customers per hour. Friday-Saturday the owner expects an average of 32 customers per hour. Start-up costs Kitchen equipment: $12,250 Cash register and sales equipment: $2,550 Initial inventory: $5,000 Pre-opening marketing: $1,000 Store setup (chairs, tables, decor etc.): $3,500 Security deposit: $4,500 First Insurance Payment: $1,500 and then Insurance will be $1000 a month. The building rent is 3600.00 per month. Phone/Internet will cost about $1000 per month. Electricity should cost about $1300 per month. Advertising and promotion will be $500 a month. Your client has $20,000 and plans to borrow $45,000 from the bank with a five-year loan at 6.1% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 22% if Income Before Taxes (IBT) is equal to or is greater than $35,500. Assume a tax rate of 15% if Income Before Taxes (IBT) is less than $35,499. You are to calculate the monthly tax payment using the appropriate logical function. Assume that sales will grow at an average of 2.40% 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. Cs A Arial 12 B B D G Number H I 3 K M Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Revenue: Month 1 5 Fruit Explosion POPS) 7021.69 6 Vanilla Vanilla POPS 6652.80 7 Chocolate Mousse POP: 7539.84 8 Mango Tangerine POPS 3769.92 9 Strawberry Crme POPE 6652.80 10 Pina Colada POPS 4213.44 5026.56 4435.20 10053.12 6726.72 42577.92 35481.60 11 Strawberry Kiwi POPS 12 Orange Crme POPS 13 Chocolate Sea Salt POP 14 Cookies & Crme POPS 15 12 Pack POPS 16 6 Pack POPS 17 Medium Frozen Ice 18 Large Frozen Ice 19 Soda 20 Bottled Water 4435.20 3769.92 1153.15 730 20 Grading criteria Sheet 1 Sheet 2 Sheet 3 Give Feedback to Marsh 100% + 2 Data Review View Automate Help Comments Catch op Editing Stare Page Layout Formulas Arial 12 BA... C D E 21 Monthly Revenue 150249.08 22 23 Expenses: 24 COGS 25 Rent 26 Phone 27 Electricity 26154.89 3600.00 1000.00 1300.00 28 Insurance 1000.00 29 Advertising 500.00 30 Hourly Wages 4032.00 31 Salaries 5708.33 32 Loan Payment 872.07 33 Total Expenses 44667.29 34 35 Income Before Tax 105581.79 36 Tax 37 Net Income 23228.00 82353.79 Grading criteria Sheet 1 Sheet 2 Sheet 3 BYRON Number G H I K L M 14 Arial 14 B 4 2 Start Up GOLD. Kitchen equipment Cash Register and Sales equipment 5 Initial inventory 5 Pre-opening marketing $12,250.00 $ 2,550.00 $ 5,000.00 $ 1,000.00 Store Setup (chairs, tables, decor etc.) $ 3,500.00 Security deposit $ 4,500.00 $ 1,500.00 $30,300.00 Initial insurance payment 10 Total 1 Owner's Equity Cash Reserves Loan Amount Sheet Sheet 2 Sheet 3 Accounting E F G H 1 100%