(((The question looks long but it isn't, it tells a story and I thought its good to put it there for context))))

You are the owner of your own cookie company. Fast growth and the need to expand have made it necessary for you to seek financing from a local bank. However, you need to show the banker your plans for the coming year. Using your own, unique data, you will prepare a comprehensive master budget and pro forma financial statements in Excel to show the banker your projected earnings and financial position. Good luck getting that bank loan!

INTRODUCTION

You knew they were good, but you never thought Grandma's old cookie recipe would bring you this far! It all started about three years ago when you began using your Grandma's cookie recipe to bake, what most people consider, the "best tasting cookies they have ever put in their mouth." After finishing college, you started baking cookies as a little side business. You bake them right in your home and sell them to friends and local stores. Response has been great! People love the cookies, and you are making a little extra money.

Your fast sales growth has negatives in addition to positives. The volume of business has grown so much that you can no longer keep up with demand. Your desire to grow this hobby into a full-fledged business has led you to explore expanding. You have been investigating new facilities and equipment, and checking into the requirements of hiring a few employees. However, there is one problem; you don't have the money to expand!

On the advice of a friend, you meet with a local banker. You share your dreams and ideas, and your need to borrow some money. The banker is encouraging and helpful. However, she states that the bank cannot lend you any money without a business plan that describes your financial results, marketing strategy, and projections for the future. You show the banker your income statement and balance sheet as of the most recent year-end, and the banker is impressed! "Looks very promising," she states. "But what I really need to see is what you plan to do with the money that I will lend you and what your business will look like next year."

When you return home after the meeting, you pull out your old college accounting textbook. You realize that this is a master budget problem just like you did in college. After reviewing your class notes and reading the textbook, you settle in to produce a plan for next year. You also send an email to your old professor asking for his help in this project. He responds with an enthusiastic yes!

You immediately realize that you must gain an understanding of your cost structure and of the relationship between your revenues, costs, and profits. You pull out Grandmas recipe to see what ingredients it takes to make a dozen cookies. Next, you go to your invoice files to determine the cost of each of the ingredients. You brainstorm to develop a list of the new costs that you must incur when you expand your operations. After analyzing all of this data you are able to break out your costs into several categories. You realize that some costs are for raw materials while others are related to manufacturing overhead or operating expenses. You also realize that some costs appear to be fixed while other costs are variable. Now you have sufficient information to determine how much money you can make when you sell these cookies.

Note that you will package and sell these cookies by the dozen. This has several consequences to your math. First, one unit of cookies is a dozen. Second, your recipe may make 18, 24 or some other amount of cookies this is your batch amount. Some costs are per batch and others are per dozen. Please pay attention to this.

Requirements:

1. Think of your favorite cookie recipe or search the Internet and find a recipe for a cookie or some other type of similar dessert. This recipe will be your Grandmas recipe that you make and sell in your business. Save a copy of this recipe.

Recipe requirements:

-Must have at least five ingredients

You must consult your recipe to determine the amount of ingredients required for one batch of cookies. You also must consult Exhibits 1 and 2 for information regarding the costs of ingredients, manufacturing overhead, and operating expenses. If your cookies have minor ingredients (

|

|

V

my recipe would be ( 1 cup butter , 1 cup brown sugar, 2 eggs, 1 teaspoons baking soda, 1/2 teaspoon salt, 2 teaspoons vanilla, 3 cups all purpose flour, 2 cups semi sweet chocolate chips)

((REQUIRED CALCULATIONS))

2. Calculate the following (in Excel):
   1. Total variable costs per dozen cookies
      1. This includes
         1. DM
         2. DL
         3. VOpEx
         4. VMOH
   2. Sales price per dozen cookies
      1. See Exhibit 1 for calculation information
   3. Contribution margin per dozen cookies
   4. Breakeven point for the quarter (three months)
      1. in dollars and
      2. in units
   5. Calculate the number of units to sell to have an operating income of $10,000 per month
   6. Prepare a contribution margin income statement for the three months and the quarter. For your sales in units, use the following:
      1. January: First four digits of your uni ID : 2977
      2. February: Last four digits of your uni ID : 7802
      3. March: Last four digits of your Drivers License (8988)

Be sure to use formulas, cell references, and proper formatting. All data should be labeled and work should flow logically to be able to be followed by someone not involved in the creation of the document. Remember, you are submitting this to the bank for a loan! This means that all calculations should be explicitly laid out so that I can follow the process.

For example, when calculating sale price you could do this:

Sales price: $2.90

Or you could do this:

Total variable cost per dozen: $1.38

Markup: 210%

Sales Price: $2.90

In both cases, the math is correct. But in the second case it is apparent how the number was calculated. The latter approach is preferred.

Please share a screenshot of excel file with formulas if possible

Exhibit 1: Sales price, overhead, and operating expenses Sales Price: Mark-up on total variable cost per dozen: 225% Direct Labor Costs: Hourly wage for labor (includes taxes and benefits) Hours of labor per batch $12.25 0.15 Manufacturing Overhead: Fixed Costs (per month): Variable costs (per batch): $0.10 $0.15 Utilities Other indirect materials and labor Maintenance Depreciation Supervision Totals $600 $800 $1,600 $3,000 $0.25 Operating Expenses: Variable costs (per dozen): $0.12 $0.13 Fixed Costs (per month): Sales Commission Shipping Costs Salaries Depreciation Other Totals $2,000 $250 $1,750 $4,000 $0.25 Exhibit 2: Cost of ingredients Below is a listing of bulk ingredients available from the local warehouse club. The Unit Size and Cost per Bulk Unit columns are the packages that are sold at the warehouse club. I have translated these into cost per cup or individual cost, as appropriate. You will need to make further adjustments to match your recipe. Cost per Ingredient Cost per cup each $ 0.62 Unit Unit Size 3 lbs 60 OZ 15 lbs 4 lbs 60 OZ 72 OZ 23 OZ 2 lbs 48 OZ 180 each 25 lbs 1 gal 10 lbs OZ 1.5 gal 12 OZ 7 lbs 7 lbs 7 lbs 25 lbs Almonds, whole Baking powder Baking soda Butter (16 sticks) Chocolate chips, dark Chocolate chips, semi-sweet Cocoa powder Coconut, shredded Cream cheese Eggs Flour, standard all purpose Milk Oatmeal, instant Oil, coconut Oil, vegetable Raisins Sugar, confectioners Sugar, dark brown Sugar, light brown Sugar, white Vanilla, imitation Vanilla, pure extract Walnuts, chopped $ 1.46 $ 0.11 Cost per Bulk Unit $ 12.98 $ 5.78 $ 7.48 $ 9.99 $ 15.98 $ 9.98 $ 7.18 $ 19.49 $ 8.76 $ 19.36 $ 7.48 $ 2.79 $ 7.98 $ 14.98 $ 7.98 $ 5.11 $ 4.29 $ 4.29 $ 4.29 $ 11.59 $ 4.97 $ 20.98 $ 14.98 $ 0.95 $ 0.78 $ 0.57 $ 1.24 $ 1.64 $ 0.86 $ 1.38 $ 2.15 $ 0.77 N/A $ 0.08 $ 0.17 $ 0.14 $ 3.95 $ 0.33 $ 2.25 $ 0.17 $ 0.32 $ 0.29 $ 0.20 $ 1.14 $ 19.24 $ 1.38 30 32 OZ 8 OZ 3 lbs Exhibit 1: Sales price, overhead, and operating expenses Sales Price: Mark-up on total variable cost per dozen: 225% Direct Labor Costs: Hourly wage for labor (includes taxes and benefits) Hours of labor per batch $12.25 0.15 Manufacturing Overhead: Fixed Costs (per month): Variable costs (per batch): $0.10 $0.15 Utilities Other indirect materials and labor Maintenance Depreciation Supervision Totals $600 $800 $1,600 $3,000 $0.25 Operating Expenses: Variable costs (per dozen): $0.12 $0.13 Fixed Costs (per month): Sales Commission Shipping Costs Salaries Depreciation Other Totals $2,000 $250 $1,750 $4,000 $0.25 Exhibit 2: Cost of ingredients Below is a listing of bulk ingredients available from the local warehouse club. The Unit Size and Cost per Bulk Unit columns are the packages that are sold at the warehouse club. I have translated these into cost per cup or individual cost, as appropriate. You will need to make further adjustments to match your recipe. Cost per Ingredient Cost per cup each $ 0.62 Unit Unit Size 3 lbs 60 OZ 15 lbs 4 lbs 60 OZ 72 OZ 23 OZ 2 lbs 48 OZ 180 each 25 lbs 1 gal 10 lbs OZ 1.5 gal 12 OZ 7 lbs 7 lbs 7 lbs 25 lbs Almonds, whole Baking powder Baking soda Butter (16 sticks) Chocolate chips, dark Chocolate chips, semi-sweet Cocoa powder Coconut, shredded Cream cheese Eggs Flour, standard all purpose Milk Oatmeal, instant Oil, coconut Oil, vegetable Raisins Sugar, confectioners Sugar, dark brown Sugar, light brown Sugar, white Vanilla, imitation Vanilla, pure extract Walnuts, chopped $ 1.46 $ 0.11 Cost per Bulk Unit $ 12.98 $ 5.78 $ 7.48 $ 9.99 $ 15.98 $ 9.98 $ 7.18 $ 19.49 $ 8.76 $ 19.36 $ 7.48 $ 2.79 $ 7.98 $ 14.98 $ 7.98 $ 5.11 $ 4.29 $ 4.29 $ 4.29 $ 11.59 $ 4.97 $ 20.98 $ 14.98 $ 0.95 $ 0.78 $ 0.57 $ 1.24 $ 1.64 $ 0.86 $ 1.38 $ 2.15 $ 0.77 N/A $ 0.08 $ 0.17 $ 0.14 $ 3.95 $ 0.33 $ 2.25 $ 0.17 $ 0.32 $ 0.29 $ 0.20 $ 1.14 $ 19.24 $ 1.38 30 32 OZ 8 OZ 3 lbs