Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

please help me :) A new client of yours, Olivia Johnson, wants to open an ice cream shop near the downtown. shopping center. She will

please help me :) 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
A new client of yours, Olivia Johnson, wants to open an ice cream shop near the downtown. shopping center. She will sell ice cream cones, cups, shakes, sundaes, and soda. Her grand opening will be lanuary 1,2023 . The name of her business is Sprinkles! Oliva needs help figuring out how her business will perform in the first year. She has hired you as an independent consultant to prepare a cash pro forma butget 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 the attached Microsoft Excel template (Sprinkles), construct a monthly pro forma cash budget for your dient for the first year of operations. Do not make any changes to this pre-designed template. You will need to add your information and any other information needed for the assignment. Do not use a template from a previous semester - this is considered academic dishonesty and will be subject to disciplinary action. 1. Place all your case assumptions data in column B of the "Sheet 1" worksheet. You will only be inputting data in cells with a light gray background. You will not need to perform any calculations (formulas) in this worksheet. Rename the worksheet "Assumptions". 2. Place your start-up costs in column B on the "Sheet 2n worksheet. You will only be inputting data in cells with a light gray background. You will perform three calculations in this worksheet. They will be entered in cells with a darker gray background. Rename this worksheet "Startup Costs". 3. Complete the Cash Pro forma on the Eheet 34 worksheet. Most of the calculations have been provided for you. You will perform some calculations in this worksheet. They will be entered in cells with a darker grow background. Rename this workstieet "Cash Pro Forma". 4. Create two charts. Create new chart sheets for each chart. 5. Recreate your cash pro forma and perform data anslysis for your recommendations. - Scenario Onei "iced Colfee fecommendation" worksheet. - Scenario One: "ice Cream Cakes Recommendation" worksheet. 1. ASSUMDTIONS MADE Enter the following data in Column B of "Sheet 1 worksheet. You will only be inputting data in cells with a light gray background. You will not need to perform amy calculations (formulas) in this worksheet. Rename the worksheet "Assumptions". Product Selling Prices: Cup or Sugar Cone ( 1 scoop) - $3.25 Cup or Sugar Cone (2 scoops) - $4.25 Cup or Sugar Cone (3 scoops) - \$5.25 Small Sundae (1 topping) - $3.60 Medium Sundae ( 1 topping) - $4.65 Large Sundae (1 topping) - $5.75 Small Shake - $3.95 Medium Shake - \$4.85 Large Shake - $5.50 Additional toppings $0.60 per topping Soda - \$1.85 Bottled water - $1.25 (Leave the celts related to Coffee, Small Cake, and Large Cake empty for now.) Cost of coods Sold: Ice Cream Cups (ice cream ingredients and cup) cost $1.75 for 1 scoop and $0.30 cents extra for each additional scoop Ice Cream Cones (ice cream ingredients and cone) cost $1.60 for 1 scoop and $0.30 cents extra for each additional scoop Small Sundae (1 topping) - $1.65 Medium Sundae (1 topping) - $1.85 Large Sundae ( 1 topping) - $2.05 Small Shake - $1.65 Medium Shake - \$1.80 Large Shake $2.05 Additional toppings - $0.20 per topping Soda - 50.95 Bottled water - so.so (teave the cells related to Coffee, Smalt Cake, and Large Cake empty for now.) Fined Costs: The bulling 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 ( 5 weekdays, 2 weekends). Sprinkles will be open from 11 am - 8pm (9 hours) on weekdays (Monday - Thursday and sundayl. Sprinkles will be open from 11 am - 11 pm (12 hours) on weekends (Friday and Saturday). Employees: Sprinkles will need one hourly employee on weekdays (Monday - Thursday and Sunday). Sprinkles will need two hourly employees on weekends (Friday and Saturday). Your client will be the manager and draw a salary of $40,000 per year (indudes benefits). She will also work in the store during the buslest times and nil in for the assistant manager on days off and sick days. The assistant manager will receive a salary of $28,000 per year (includes benefits). The hourly workers wil be paid $12.00 an hour, Demand Rate: On the weekdays (Monday - Thursday and Sunday), the owner expects an average of 12 customers an hour. On the weekends (friday and Saturday), the owner expects an average of 25 customers an hour. Product demand on averace (enter as perceotage - e g. enter =1/2 in cell ng4] 1/2 of all customers will buy 1 scoop lce Cream cups 1/2 of all customers will buy 2 scoop ice Cream cups 1/3 of all customers will buy 3 scoop lce Cream cups 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 customers will buy 3 scoop ice Cream cones 1/4 of all customers will buy a 5 mall sundae 1/3 of all customers will buy a Medium Sundae 1/4 of all customers will buy a Large Sundae 1/2 of all customers will buy a Small shake y/4 of all customers witl buy a Medium Shake 1/4 of all customers will buy a Large Shake 1/4 of all customers will buy an additional topping 3/4 of all customers will buy a Soda 1/4 of all customers will buy a Bottled Water Assume that sales will grow at an average of 1.50% per month. Assume that each month contains 4.2 weeks. Enter the following data in Column B of "Sheet 2 " worksheet. You will only be inputting data in celis with a light gray background. You will perform three calculations (SUM and PMT) in this worksheet. They will be entered in cells with a darker gray background. Aename the worksheet "Startup Costs". Kitchen equipment: \$12,250 Cash register and sales equipment: $2,550 Initial inventory: $5,000 Pre-opening marketing: $1,500 Store fixtures (chairs, tables etc.): $4,500 Artwork: $600 Security deposit: $4,500 First insurance payment: $1,500 Calculate the Total Start Up Costs in cell B10. Use the SUM function. Your client has $20,000 (Owner Contribution). Use a formula (not a function) to calculate the Loan Amount in cell 813. This is calculated by subtracting the Owner Contribution from the Total. Your client plans to borrow the rest from the bank with a five-year loan at 3.4% interest. In cell B16, calculate the Monthly Loan Payment using the PMT function in Excel. Use cell references. - Rate = Loan interest rate /12 - Nper = Loan period * 12 - Pre Loan Amount Assume a tax rate of 22% if income Before Taxes (i8T) is equal to or is greater than $25,500. Assume a tax rate of 15% if ist is less than $25,499. 3. CASH PRO FORMA Enter the following data in the "Sheet 3 " worksheet. Rename the worksheet "Cash Pro Forma". Most of the data has been entered for you. You will perform some calculations in this worksheet. They will be entered in cells with a darker gray background. You will use the SUM function to calculate the Monthly Revenue (row 23) and Total Expenses (row 36) each month. You will use the SUM function to calculate the Total (column N) for each category. You will use a formula to calculate the Income Before Tax (row 38) which is calculated by subtracting the Total Expenses from the Monthly Revenue. You will calculate the Tax (row 39) using the If function. Assume a tax rate of 22% (.22) if income Before Taxes (IBT) is greater than or equal to $25,500. Assume a tax rate of 15%(.15) if IBT is less than $25,499. Create two separate charts. Create a new chart sheet for each chart. Make sure both charts are formatted correctly (i.e., appropriate chart title, legend where appropriate, data series properly labeled, etc.) and they are appropriate for business. - Chart One: Using the Month 1 Revenue data from the Cash Pro Forma worksheet, create a chart that will show the monthly revenue for each of the products. (Use one of the Recommended Charts.) Move the chart to a new chart sheet named "Monthly Product Revenue". This will show the revenue for each of your products for the first month. - Chart Two: Using the "Net income" data from the Cash Pro Forma worksheet, create a chart that will track the total product net income for the year to determine any trends or projections in product sales. (Use one of the Recommended Charts.) Move the chart to a new chart sheet named "Total Product Net Income: Scenario One: Iced Coffee Recommendation Your client is unsure if she should sell flavored iced 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 alraid that he would cannibalize his soft drink sales with the colfee 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. Show your client how this recommendation would affect the bottom line by recreating the cash pro forma for the scenario and applying the data analysis to determine profitability. - Copy your Assumptions worksheet (create a copy and move it to the end). Rename the worksheet "Coffee" - Copy your Startup Costs worksheet (create a copy and move it to the end). Rename the worksheet "Startup Costs (Coffee)". - Copy your Cash Pro Forma worksheet (create a copy and move it to the end). Rename the worksheet "Cash Pro Forma (Coffee)". - Now we need to point to the new Assumptions worksheet to ensure the calculations are correct, In the Cash Pro forma (Colfee) worksheet, click on cell A1. Go to Replace. Find "Assumptions" and replace with "Coffee". Replace All. 312 replacements should be made. - Now we need to point to the new Startup Costs worksheet to ensure the calculations are correct. In the Cash Pro Forma (Coffee) worksheet, click on cell A1. Go to Replace. Find "Startup Costs" and replace with "Startup Costs (Coffee)". Replace All, 12 replacements should be made. - Go to the Startup Costs (Coffee) worksheet. Insert a new row above the Total (row 10). Name the row "Colfee Equipment" and enter the rate of $5,250. Notice how this increases the Total, as well as the Monthly Loan Payment? if it didn't, update your Total formula accordingly. - Go to the Cash Pro Forma (Coffeel worksheet. In the "Insurance - Hot Equigment" row (row 35), enter the rate of $175 for each month. - Go back to the Colfee worksheet. Now we need to account for the sales of cotfce, and the loss of sales in sodas. Enter the seling price of coffee ($3.75) in ceil B15. Enter the CoGs of coffee ($1.70) in cell n35. Enter the percentage of customers (1/2) who will purchase coffee in cell B79. Now, we need to lower the number of sodas sold per month. Go to cell E31. Since there will be one less soda sold for each coffee sold, add "F- 533 to the end of the farmula. The formula should be -SE515'B77-E33. - Compare the data in the original Cash Pro Forma worksheet with the data in this new Cash Pro Forma (Coffee) worksheet. What is your recommendation: Should your client offer flavored iced coffee to her customers? Discuss the cash pro forma changes that occur under this new scenario and how it affects profitability. Insert a text box (not a comment) to explain your recommendations and rationale. This will be approximately 1 paragraph. Your dient would the to consider adding ice cream cakes to the list of items avaiuble for sale. She is wanting to offer cakes with customiaed ice cream decorations. She thinks the can sell 20 small cakes a week and 25 targe cakes a week it seems to be lucrative because the small cake is 518.95, and the large cake is $72.95. Cost of Goods Sold are $6.45 per small cake and $9.40 per large cake. If will cost her 33,500 to purchaye the equipment and insurance costs would rise by another 5375 : per month due to the hot equipment needed to make, the cakes. Show your dlient how this recommendation would affect the bottom line by recreating the pro forma for the scenario and applying the data analysis to determine protitablity. The steps will be winitar to Scenario One above. - Copry your Assumptions workitheet (create a copy and move it to the end). Fename the worksheet "Cake" - Copy your Startup Costs worksheet (create a copy and move it to the end). Rename the workheet "Sartup Costs (Cake)" - Cogr your Cath Pro Forma wonktheet (oreate a copy and move it to the end). Aerame the worksheet "Cah Pro forma (Cabe)". - Now we need to point to the new Assumptions worksheet to ensure the calculations are correct, In the Cash fro forma (Cake) worksheet, elick on cell A.. Go to Replace. Find "Assurtiptions" and replace with "Cake". eplace All. 312. replacements should be made. - Now we need to point to the new 5 tartup Corts workheet to ensuce the calnulations are correct in the Cait Pro forma (Cabe) workcthent, rlick on crell at Go to Replace. Find "Startup Costs* and replace with "Startup Corts (Cake)". Replace All. 12 replacements should be made. - Go to the Startup Costs iCake) worksheet. Insert a new row above the Total (row 10). Name the row "Equipment" and enter the rate of 53,500 . Notice how this incrases the Total, as well as the Monthly Loan Payment? If it didn't update your Total formuls accordingly. - Go wo tre Cosh ficu forma (Cale) wurksheel ba the thour ance - Hut Equipirent" tow (row 35), enter the rate of $375 for each moech. - Cio back to the Cabe worksheet. Now we need to account for the sales af the lice cream cakes. Enter the selieg pike of the amall cake (S18.95) ta cell as6, and the lage cake ($2.95) in cell B17. Fnter the coGs of the small cake {$6.45} in cell 835 and the larke cake (59.40) in cet an?. Now, we reed to enter the unit sales per menth for the small cake (20 per week, which =20Bg] in cel E34 and the lange - Compare the data in the original Cash Pro Forma worksheet with the data in this new Cash Pro forma (Cake) worksheet. What is your recommendation? Would it be profitable to sell the ice cream cakes? Which is more profitable - selling coffee or ice cream cakes? Discuss the cash pro forma changes that occur under this new scenario and how it affects profitability. insert a text bok (not a comment) to explain your recommendations and rationale. This witi be approwimately 1 paragraph. A new client of yours, Olivia Johnson, wants to open an ice cream shop near the downtown. shopping center. She will sell ice cream cones, cups, shakes, sundaes, and soda. Her grand opening will be lanuary 1,2023 . The name of her business is Sprinkles! Oliva needs help figuring out how her business will perform in the first year. She has hired you as an independent consultant to prepare a cash pro forma butget 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 the attached Microsoft Excel template (Sprinkles), construct a monthly pro forma cash budget for your dient for the first year of operations. Do not make any changes to this pre-designed template. You will need to add your information and any other information needed for the assignment. Do not use a template from a previous semester - this is considered academic dishonesty and will be subject to disciplinary action. 1. Place all your case assumptions data in column B of the "Sheet 1" worksheet. You will only be inputting data in cells with a light gray background. You will not need to perform any calculations (formulas) in this worksheet. Rename the worksheet "Assumptions". 2. Place your start-up costs in column B on the "Sheet 2n worksheet. You will only be inputting data in cells with a light gray background. You will perform three calculations in this worksheet. They will be entered in cells with a darker gray background. Rename this worksheet "Startup Costs". 3. Complete the Cash Pro forma on the Eheet 34 worksheet. Most of the calculations have been provided for you. You will perform some calculations in this worksheet. They will be entered in cells with a darker grow background. Rename this workstieet "Cash Pro Forma". 4. Create two charts. Create new chart sheets for each chart. 5. Recreate your cash pro forma and perform data anslysis for your recommendations. - Scenario Onei "iced Colfee fecommendation" worksheet. - Scenario One: "ice Cream Cakes Recommendation" worksheet. 1. ASSUMDTIONS MADE Enter the following data in Column B of "Sheet 1 worksheet. You will only be inputting data in cells with a light gray background. You will not need to perform amy calculations (formulas) in this worksheet. Rename the worksheet "Assumptions". Product Selling Prices: Cup or Sugar Cone ( 1 scoop) - $3.25 Cup or Sugar Cone (2 scoops) - $4.25 Cup or Sugar Cone (3 scoops) - \$5.25 Small Sundae (1 topping) - $3.60 Medium Sundae ( 1 topping) - $4.65 Large Sundae (1 topping) - $5.75 Small Shake - $3.95 Medium Shake - \$4.85 Large Shake - $5.50 Additional toppings $0.60 per topping Soda - \$1.85 Bottled water - $1.25 (Leave the celts related to Coffee, Small Cake, and Large Cake empty for now.) Cost of coods Sold: Ice Cream Cups (ice cream ingredients and cup) cost $1.75 for 1 scoop and $0.30 cents extra for each additional scoop Ice Cream Cones (ice cream ingredients and cone) cost $1.60 for 1 scoop and $0.30 cents extra for each additional scoop Small Sundae (1 topping) - $1.65 Medium Sundae (1 topping) - $1.85 Large Sundae ( 1 topping) - $2.05 Small Shake - $1.65 Medium Shake - \$1.80 Large Shake $2.05 Additional toppings - $0.20 per topping Soda - 50.95 Bottled water - so.so (teave the cells related to Coffee, Smalt Cake, and Large Cake empty for now.) Fined Costs: The bulling 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 ( 5 weekdays, 2 weekends). Sprinkles will be open from 11 am - 8pm (9 hours) on weekdays (Monday - Thursday and sundayl. Sprinkles will be open from 11 am - 11 pm (12 hours) on weekends (Friday and Saturday). Employees: Sprinkles will need one hourly employee on weekdays (Monday - Thursday and Sunday). Sprinkles will need two hourly employees on weekends (Friday and Saturday). Your client will be the manager and draw a salary of $40,000 per year (indudes benefits). She will also work in the store during the buslest times and nil in for the assistant manager on days off and sick days. The assistant manager will receive a salary of $28,000 per year (includes benefits). The hourly workers wil be paid $12.00 an hour, Demand Rate: On the weekdays (Monday - Thursday and Sunday), the owner expects an average of 12 customers an hour. On the weekends (friday and Saturday), the owner expects an average of 25 customers an hour. Product demand on averace (enter as perceotage - e g. enter =1/2 in cell ng4] 1/2 of all customers will buy 1 scoop lce Cream cups 1/2 of all customers will buy 2 scoop ice Cream cups 1/3 of all customers will buy 3 scoop lce Cream cups 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 customers will buy 3 scoop ice Cream cones 1/4 of all customers will buy a 5 mall sundae 1/3 of all customers will buy a Medium Sundae 1/4 of all customers will buy a Large Sundae 1/2 of all customers will buy a Small shake y/4 of all customers witl buy a Medium Shake 1/4 of all customers will buy a Large Shake 1/4 of all customers will buy an additional topping 3/4 of all customers will buy a Soda 1/4 of all customers will buy a Bottled Water Assume that sales will grow at an average of 1.50% per month. Assume that each month contains 4.2 weeks. Enter the following data in Column B of "Sheet 2 " worksheet. You will only be inputting data in celis with a light gray background. You will perform three calculations (SUM and PMT) in this worksheet. They will be entered in cells with a darker gray background. Aename the worksheet "Startup Costs". Kitchen equipment: \$12,250 Cash register and sales equipment: $2,550 Initial inventory: $5,000 Pre-opening marketing: $1,500 Store fixtures (chairs, tables etc.): $4,500 Artwork: $600 Security deposit: $4,500 First insurance payment: $1,500 Calculate the Total Start Up Costs in cell B10. Use the SUM function. Your client has $20,000 (Owner Contribution). Use a formula (not a function) to calculate the Loan Amount in cell 813. This is calculated by subtracting the Owner Contribution from the Total. Your client plans to borrow the rest from the bank with a five-year loan at 3.4% interest. In cell B16, calculate the Monthly Loan Payment using the PMT function in Excel. Use cell references. - Rate = Loan interest rate /12 - Nper = Loan period * 12 - Pre Loan Amount Assume a tax rate of 22% if income Before Taxes (i8T) is equal to or is greater than $25,500. Assume a tax rate of 15% if ist is less than $25,499. 3. CASH PRO FORMA Enter the following data in the "Sheet 3 " worksheet. Rename the worksheet "Cash Pro Forma". Most of the data has been entered for you. You will perform some calculations in this worksheet. They will be entered in cells with a darker gray background. You will use the SUM function to calculate the Monthly Revenue (row 23) and Total Expenses (row 36) each month. You will use the SUM function to calculate the Total (column N) for each category. You will use a formula to calculate the Income Before Tax (row 38) which is calculated by subtracting the Total Expenses from the Monthly Revenue. You will calculate the Tax (row 39) using the If function. Assume a tax rate of 22% (.22) if income Before Taxes (IBT) is greater than or equal to $25,500. Assume a tax rate of 15%(.15) if IBT is less than $25,499. Create two separate charts. Create a new chart sheet for each chart. Make sure both charts are formatted correctly (i.e., appropriate chart title, legend where appropriate, data series properly labeled, etc.) and they are appropriate for business. - Chart One: Using the Month 1 Revenue data from the Cash Pro Forma worksheet, create a chart that will show the monthly revenue for each of the products. (Use one of the Recommended Charts.) Move the chart to a new chart sheet named "Monthly Product Revenue". This will show the revenue for each of your products for the first month. - Chart Two: Using the "Net income" data from the Cash Pro Forma worksheet, create a chart that will track the total product net income for the year to determine any trends or projections in product sales. (Use one of the Recommended Charts.) Move the chart to a new chart sheet named "Total Product Net Income: Scenario One: Iced Coffee Recommendation Your client is unsure if she should sell flavored iced 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 alraid that he would cannibalize his soft drink sales with the colfee 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. Show your client how this recommendation would affect the bottom line by recreating the cash pro forma for the scenario and applying the data analysis to determine profitability. - Copy your Assumptions worksheet (create a copy and move it to the end). Rename the worksheet "Coffee" - Copy your Startup Costs worksheet (create a copy and move it to the end). Rename the worksheet "Startup Costs (Coffee)". - Copy your Cash Pro Forma worksheet (create a copy and move it to the end). Rename the worksheet "Cash Pro Forma (Coffee)". - Now we need to point to the new Assumptions worksheet to ensure the calculations are correct, In the Cash Pro forma (Colfee) worksheet, click on cell A1. Go to Replace. Find "Assumptions" and replace with "Coffee". Replace All. 312 replacements should be made. - Now we need to point to the new Startup Costs worksheet to ensure the calculations are correct. In the Cash Pro Forma (Coffee) worksheet, click on cell A1. Go to Replace. Find "Startup Costs" and replace with "Startup Costs (Coffee)". Replace All, 12 replacements should be made. - Go to the Startup Costs (Coffee) worksheet. Insert a new row above the Total (row 10). Name the row "Colfee Equipment" and enter the rate of $5,250. Notice how this increases the Total, as well as the Monthly Loan Payment? if it didn't, update your Total formula accordingly. - Go to the Cash Pro Forma (Coffeel worksheet. In the "Insurance - Hot Equigment" row (row 35), enter the rate of $175 for each month. - Go back to the Colfee worksheet. Now we need to account for the sales of cotfce, and the loss of sales in sodas. Enter the seling price of coffee ($3.75) in ceil B15. Enter the CoGs of coffee ($1.70) in cell n35. Enter the percentage of customers (1/2) who will purchase coffee in cell B79. Now, we need to lower the number of sodas sold per month. Go to cell E31. Since there will be one less soda sold for each coffee sold, add "F- 533 to the end of the farmula. The formula should be -SE515'B77-E33. - Compare the data in the original Cash Pro Forma worksheet with the data in this new Cash Pro Forma (Coffee) worksheet. What is your recommendation: Should your client offer flavored iced coffee to her customers? Discuss the cash pro forma changes that occur under this new scenario and how it affects profitability. Insert a text box (not a comment) to explain your recommendations and rationale. This will be approximately 1 paragraph. Your dient would the to consider adding ice cream cakes to the list of items avaiuble for sale. She is wanting to offer cakes with customiaed ice cream decorations. She thinks the can sell 20 small cakes a week and 25 targe cakes a week it seems to be lucrative because the small cake is 518.95, and the large cake is $72.95. Cost of Goods Sold are $6.45 per small cake and $9.40 per large cake. If will cost her 33,500 to purchaye the equipment and insurance costs would rise by another 5375 : per month due to the hot equipment needed to make, the cakes. Show your dlient how this recommendation would affect the bottom line by recreating the pro forma for the scenario and applying the data analysis to determine protitablity. The steps will be winitar to Scenario One above. - Copry your Assumptions workitheet (create a copy and move it to the end). Fename the worksheet "Cake" - Copy your Startup Costs worksheet (create a copy and move it to the end). Rename the workheet "Sartup Costs (Cake)" - Cogr your Cath Pro Forma wonktheet (oreate a copy and move it to the end). Aerame the worksheet "Cah Pro forma (Cabe)". - Now we need to point to the new Assumptions worksheet to ensure the calculations are correct, In the Cash fro forma (Cake) worksheet, elick on cell A.. Go to Replace. Find "Assurtiptions" and replace with "Cake". eplace All. 312. replacements should be made. - Now we need to point to the new 5 tartup Corts workheet to ensuce the calnulations are correct in the Cait Pro forma (Cabe) workcthent, rlick on crell at Go to Replace. Find "Startup Costs* and replace with "Startup Corts (Cake)". Replace All. 12 replacements should be made. - Go to the Startup Costs iCake) worksheet. Insert a new row above the Total (row 10). Name the row "Equipment" and enter the rate of 53,500 . Notice how this incrases the Total, as well as the Monthly Loan Payment? If it didn't update your Total formuls accordingly. - Go wo tre Cosh ficu forma (Cale) wurksheel ba the thour ance - Hut Equipirent" tow (row 35), enter the rate of $375 for each moech. - Cio back to the Cabe worksheet. Now we need to account for the sales af the lice cream cakes. Enter the selieg pike of the amall cake (S18.95) ta cell as6, and the lage cake ($2.95) in cell B17. Fnter the coGs of the small cake {$6.45} in cell 835 and the larke cake (59.40) in cet an?. Now, we reed to enter the unit sales per menth for the small cake (20 per week, which =20Bg] in cel E34 and the lange - Compare the data in the original Cash Pro Forma worksheet with the data in this new Cash Pro forma (Cake) worksheet. What is your recommendation? Would it be profitable to sell the ice cream cakes? Which is more profitable - selling coffee or ice cream cakes? Discuss the cash pro forma changes that occur under this new scenario and how it affects profitability. insert a text bok (not a comment) to explain your recommendations and rationale. This witi be approwimately 1 paragraph

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

Auditing & Assurance Services

Authors: Timothy Louwers, Penelope Bagley, Allen Blay, Jerry Strawser, Jay Thibodeau

8th Edition

978-1260703733, 1260703738

More Books

Students also viewed these Accounting questions

Question

Discuss the various types of policies ?

Answered: 1 week ago

Question

Briefly explain the various types of leadership ?

Answered: 1 week ago

Question

Explain the need for and importance of co-ordination?

Answered: 1 week ago

Question

Explain the contribution of Peter F. Drucker to Management .

Answered: 1 week ago

Question

Design a training session to maximize learning. page 309

Answered: 1 week ago