please help!
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! Ollva 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 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 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 assumption 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-we costs in column B on the "Sheet 2" worksheet. You will anily 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 "Sheet 3 worksheet. Most of the calculations have been provided for you. You wili perform some calculations in this worksheet. They will be entered in cells with a darker gray background. Fename this worksheet "Cash Pro Forma". 4. Create two charts. Create new chart sheets for each chart. 5. Aecreate your cash pro forma and perform data anslyis for your recommendatians. - Scenario One: "iced Coffee flecommendation" worksheet. - Scenario One: "Ice Cream Cakes flecommendation" worksheet. Enter the following data in Column 8 of "Sheet 2" worksheet. You will only be inputting data in cells with a light gray background. You will perform three caloulations (SUM and PMT) in this worksheet. They will be entered in cells with a darker gray background. Rename 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 poyment: $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 B13. 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 - Pr = Loan Amount. Assume a tax rate of 22% if income Before Taxes (iBT) is equal to of is greater than $25,500. Assume a tax rate of 15% if i i8T is less than $25,499. 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! Ollva 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 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 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 assumption 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-we costs in column B on the "Sheet 2" worksheet. You will anily 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 "Sheet 3 worksheet. Most of the calculations have been provided for you. You wili perform some calculations in this worksheet. They will be entered in cells with a darker gray background. Fename this worksheet "Cash Pro Forma". 4. Create two charts. Create new chart sheets for each chart. 5. Aecreate your cash pro forma and perform data anslyis for your recommendatians. - Scenario One: "iced Coffee flecommendation" worksheet. - Scenario One: "Ice Cream Cakes flecommendation" worksheet. Enter the following data in Column 8 of "Sheet 2" worksheet. You will only be inputting data in cells with a light gray background. You will perform three caloulations (SUM and PMT) in this worksheet. They will be entered in cells with a darker gray background. Rename 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 poyment: $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 B13. 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 - Pr = Loan Amount. Assume a tax rate of 22% if income Before Taxes (iBT) is equal to of is greater than $25,500. Assume a tax rate of 15% if i i8T is less than $25,499
