Assignment
A friend of yours wants to open a store that sells pencils.
You volunteer to help your friend develop a pro forma cash budget -- this is a document that helps forecast income and expenses over a period of time, and can be used to manage the business if done correctly.
Your friend tells you the following:
Pencils range in price from $2.50 - $4.50.
The average price is $3.00
The material costs about $1.00 per pencil.
The store rent is $1,600.00 per month.
Phone costs $60.00 per month.
Electricity should cost about $135.00 a month.
Insurance will be $80.00 a month.
Advertising and promotion is planned to start out at $1,200.00 a month and will change to 12% of sales in the sixth month.
Colored pencils will also be sold at the store, and the markup will be 100%.
The store will be open 12 hours a day and will need one hourly employee and an assistant manager during each of the hours that the store is open. On Fridays and Saturdays the store will need two hourly employees and an assistant manager. Your friend will be the manager and draw a salary of $27,000.00 per year (includes benefits). Your friend will also work in the store during the busiest times, and fill in for the assistant manager on days off and sick days. The assistant manager will receive a salary of $19,000.00 per year (includes benefits). The hourly workers will be paid $6.00 an hour.
The store will be open six days a week (closed on Sunday).
Sundays through Thursdays the owner expects an average of 8 customers an hour.
Fridays and Saturdays, the owner expects an average of 16 customers an hour.
Each customer will buy one pencil. 50% of the customers will purchase a colored pencil. Average purchase price for the trip is $2.00.
Start up costs for the store include:
Machinery $15,000.00
Initial health food inventory (replenished as used). - $1,300.00
Initial stock of pencil (replenished as used). - $1,800.00
Pre-opening advertising - $2,000.00
Store fixtures (counter, chairs, tables etc.) - $10,000.00.
Licenses - $500.00
Rent Deposit - $1,600.00
Fist Insurance Payment - $80.00
Your friend has $17,000.00 and plans to borrow the rest from the bank with a five year loan at 7% interest.
Assume that sales will grow at 5% per month.
1. Construct a monthly pro-forma cash budget for your friend for the first year of operations.
a. Place all your assumptions on one worksheet. Name the worksheet "Assumptions"
b. Place your start up costs on a second worksheet named "Startup Costs"
c. Place the cash proforma on another worksheet named Cash Pro-forma Budget"
2. How much money should your friend borrow? Explain your reasoning. Include your loan calculations.
3. What is the dollar breakeven point?
4. Graph the Revenue and the Net Income after Taxes. Name this worksheet "Graph"
5. Double the marketing and advertising. Assume this increases the growth rate to 8%. Would this be a good business decision? Why or why not? Name your worksheet "Double"
6. Suggest two reasonable business recommendations to your friend. Show your friend how these recommendations would effect the cash budget. Use a text box to explain your changes. Name the worksheet "Recommendations"
No constant numbers should be used on the worksheets.
Assume a 4.2 week month.
Assume a tax rate of 30%.