Rogers Party Planners

You are the sole proprietor of a small party planning company that specializes insmallcommunity and company events(average of 200 people per event).You have one employee who you pay $200 per party. You are also responsible for paying payroll taxes. You rent a small storage unit which you use for storing party materials and supplies.You have to pay for liability insurance. Because of the Seasonal Nature of Party planning, you may increase advertising during specific seasons. The advertising expense is based on an average throughout the year. The number of parties per month is also an average throughout the year.

You are concerned that you might have cash flow issues during slow months and want to analyze how the different seasonsmayaffectyour income. You also want to see how increasing you're pricing during busy seasons may affect your income.You are thinking about hiring a second employee and perhaps taking on larger parties.You decide to explore all these different avenues by using the tools provided by Excel.You are also considering purchasing or building a facility that can be used for your events and parties.

Part 1.Use the data file Rogers.xls.

Add a documentation sheet with author, date and purpose of workbook.

MAke an Income Statement with the following data:Enter formulas tocalculate amount in cells with asterisks.

In the same worksheet as your income statement, make asinglevariable data table to analyze howthe net income per month would change based on the number of parties per month.Use the range of parties from 1 per month to 16per month.You can refer to the PDF and Word doc to check your table results. These are posted in D2Lunder the content area and named monthly income data table.

make a CVP Chart using the results of your data table.Save this in the same worksheet at your data tableand income statement. Format according to the rubric on the last page of this Item.

Use Goal Seek to determine the break-even point for Net Income(Net Income will be equal to zero)with the current values for revenue and expenses by changing the monthlynumber of parties. Indicate the solution in the documentation worksheetlabeled: Break-Even Point Sales by Party Size.Please note the breakeven point in the documentation worksheet, and return the income statement back to the original values. Note that your data table should return to the original values as well.

d.)Create the following four scenarios based on the make a Scenario Summary with Total Monthly Revenue, Total Monthly Expenses and Net Monthly Income as the results cells. Please also name the Total Monthly Revenue, Total Monthly Expenses and Net Monthly Income cells accordingly.Name the cells used as changing cells and results cell in the scenario summary so that their names (and not the cell reference) will appear in thescenariosummary.Use the names, Size of Party, Number of Employees, Number of Parties, and Price per Head.Save the summary worksheet as Alternative Scenarios.

Set up four scenarios as follows: (use these names for each of the scenarios)

1) AverageOperations(Status Quo)

2)Reduced Number of Parties, Normal Price

3)Larger Parties, Additional Employee

4)More Parties, Additional Employee, Increased Pricing

Which Scenario do you recommend and why?Include your recommendations in the scenario summary worksheet.

Part 2.Loan Analysis Scenario

As another scenario you are consideringbuildinga facility in which you could hold parties and receptions.After researching your options, yourealized that youwould need to take outa loan for $500,000, which youwould like torepay over a ten year period. This includes kitchen equipment, furniture, and additional licensures.Thekitchen equipment and furniture will cost you 25% of your total investment.The current interest rate is the prime rate plus 3%. The prime rate is currently 3.25%, this means your annual interest rate would be 6.25%.You will useStraight Line Depreciation over a 6 year period to determine the cost of your depreciation.The estimated salvage value of your equipment is $75,000.

make a Loan Analysis worksheet andDepreciation Worksheetusing the following information:

1. What would your monthly payments be under the above scenario?

2. What is the future value of your loan after five years assuming you pay $2,500 per month?

3. How many total payments would it take to pay off this loan in years if you pay $3,000 per month?

4. How much could you borrow if you wanted to pay $3,000 per month overa 10 year period?

Revised Income Statement andAnalysis

make a revised income statement using the payment amount that you determined in step 1 above AND depreciation. Only interest payments would be included in the income statement.Does this scenario seem feasible given your estimated monthly net income? Determine how depreciation affects your net income and cash flow? Remember depreciation is an expense, but it is anon-cash expensethat you can addback in to increase your cash flow.Hint: see tutorial 9.

Anotherhint regarding depreciation and the income statements: you want to make a new income statement under each loan scenario and make sure to include depreciation The amount that you will use for depreciation is going to be 25% of $500,000 with a salvage value of $75,000.

What are some other considerations to take into account if you wish to own your facility? How might that affect youranalysis?Please save your responses tothis section a.), b.) and c.) to a worksheet named Solver Model.

b.)Interpreting Solver Models

Using your original income statement usesolver modelto make ananswer reportfor the following,return your original income statement to 8 parties per month.:

How many parties can you have per month that minimizes your monthly material costs and returnsno less than $4,000.00 per month.

If you could reduce your material cost according to this solver model, would it make sense to reduce your material cost. What factors would you consider in your decision?

Save your file as Item 2.