Organic Muffins Investment-2020

Your mother made delicious Muffins from scratch, and she left you the recipe. Everyone raved about them. Now, you are also making Muffins just as good as moms. But, you dont want to divulge the recipe.

As a hobby you make a dozen Muffins every Friday and sell them to a local bakery. The bakerys customers have come to expect this, and all your Muffins are usually sold in a couple of hours. This success has made you and your spouse think that you could try the business on a larger scale.

Your spouse has business experience and an MBA from IUN and has figured out what resources are needed to produce and sell Muffins on a larger scale. Your spouse has found a likely production site in town, some refurbished Muffin making machinery, and delivery trucks. The plant, equipment, and beginning working capital could be financed by some of your own funds and a $50,000 loan that your local banker says would be available.

In addition, you have taken samples of your work to local bakeries and grocery stores, and many have offered to sell your Muffins. You think you could sell whatever number of Muffins you make each day. The question that is bothering you is whether you think a profit can be made.

Your Muffins feature delicious organic ingredients of high quality. You bake the Muffins for 30 minutes at 400 degrees.

A production oven can bake 10 Muffins at a time. Production would occur in 45-minute blocks: 2 or 3 minutes to load Muffins into the oven, 40 minutes for baking, and 2 or 3 minutes to put the baked Muffins into paper boxes and shelve them. A workday would last 12 hours, which is sixteen 45-minute production blocks. Thus, a single worker could produce 160 Muffins in a day (16*10).

You have five ovens, which is the maximum capacity of the premises. Thus, you could produce up to 800 Muffins a day using five production workers at a time.

You and your spouse would prepare the mix for days first batch of Muffins. Thereafter while one batch bakes, workers would create the mix for the next batch, and put the Muffins together for baking. At the end of the day, while the last batch of Muffins bakes, workers would thoroughly clean the premises.

Your production facility would be open 6 days a week for 50 weeks a year, which would be 300 production days per year. You would not produce Muffins on Sundays.

You think that a delivery truck can be rigged to hold 100-boxed Muffins. The truck driver would be required to load the truck and then drive to the bakery or grocery store. The driver would unload the truck and take the Muffins in. In some cases, the driver would shelve the Muffins; otherwise, store or bakery workers would perform this task.

On average, you think a truck driver could cover 150 miles per day; most of the mileage would be city driving. Your trucks would get 15 miles per gallon. You would lease the needed trucks for $50 per day each.

Production workers would be paid $15 an hour for 12-hour day. Truck drivers would be paid $20 per hour for a 12-hour day. Overtime rates would not apply for workdays in which employees worked more than 8 hours.

You and your spouse would take care of the office work. You expect fixed administrative expenses of $20,000 per year to pay for renting the premises, heat, power, and so on.

Although you expect demand for your Muffins to be stimulated by word of mouth and social media, you know that you will need an advertising campaign. Your research shows that such campaigns vary in cost and effectiveness. You expect advertising to affect demand and the price you can charge stores and bakeries. Two possible campaigns have been identified. The more expensive campaign should stimulate sales and selling prices more than other campaign. Each campaign will be more activeand thus more expensivein the first year.

As a result of bad weather around the world, the cost of food has increased in recent years. Thus, you are concerned about the rising cost of ingredients. These cost increases probably could not be fully recouped by increasing your selling prices. In the same way, you are concerned about the rising cost of gasoline, which has been about $4 a gallon recently.

You would open for business if you think you could make a profit by 2023, which would be the third year of operations. You want to use Excel to estimate profits for 2021-23. Your Excel model needs to account for possible sales levels and for changes in ingredient and gasoline costs. Your model will let you develop what-if scenarios with the inputs, see the financial results, and then help you decide if you should go into the cupcake-making business.

The spreadsheet skeleton is given for you Muffins-Investment-Data-2020.xlsx.

Details for completing the spreadsheet are as given below:

Constants:

Tax Rateapplied to income before taxes. The rate is expected to be the same each year.

Minimum cash needed to start yearYou want to have at least $10,000 in cash at the beginning of each year. Your banker will lend you the needed (if needed) amount at the end of a year to begin the New Year with $10,000.

Number of production daysYour production facility will operate 300 days a year.

Maximum number of Muffins produced per dayyou can make 800 Muffins per day at most.

Number of Muffins a truck can carryA truck can be set up to comfortably store 100 Muffins on racks.

Cost to lease a truck per dayA truck can be leased for $50 a day.

Number of truck miles per dayOn average, a driver can cover 150 miles per day in and around the city.

Truck miles per gallonA truck is expected to average 15 miles per gallon.

Production worker pay per hourThe rate is $15 and hour, with no overtime.

Truck driver pay per hourThe rate is $20 an hour, with no overtime.

Administrative costs in yearFixed costs are expected to be $20,000 per year for rent, maintenance, insurance, electricity, and so on. These costs do not fluctuate with changes in production.

High cost advertising campaignThe more expensive campaign will cost $20,000 in the first year, with costs declining as the years go on.

Low cost advertising campaignThe less expensive campaign will cost $10,000 in the first year, with costs declining as the years go on.

Interest rate on debt owedYour banker will charge interest for any debt owed in the year. The rate can vary; the banker says that interest rates are expected to rise as the economy continues to recover.

Summary:

For each year, the spreadsheet should show net income after taxes, cash on hand at the end of the year and debt owed to the bank at the end of the year. The net income, cash and debt cells should be formatted as currency with zero decimal places. These values are computed elsewhere in the spreadsheet and should be copied here.

Calculations:

Per day demand for MuffinsThis value is the number of Muffins that stores and bakeries want to buy each day. The amount is a function of advertising level. In 2021, if the more expensive campaign is used, 480 Muffins per day will be needed; otherwise, 320 Muffins per day will be needed. In 2022, if the more expensive campaign is used, the demand will be twice the 2021 demand; otherwise, the demand will be 1.5 times the 2021 demand. In 2023, if the more expensive campaign is used, the demand will be 1.5 times the 2022 demand; otherwise the demand will be 1.25 times the 2022 demand. However, the demand cannot exceed the constant 800; you can deal with this constraint by using the MIN() function or an IF() statement.

Muffins produced in a yearThis amount is a function of the number of Muffins needed per day and the number of production days, which is a value from the Constants section.

Number of production workers neededThis amount is a function of the daily demand and the output per worker, 160, a value you can hard-code in your formula. Use the Round() function with zero decimal places to avoid specifying a part of a worker.

Number of delivery trucks neededThis amount is a function of the daily demand and truck capacity, a constant value. Use the ROUNDUP() function with zero decimal places to avoid specifying a part of truck.

Truck miles per dayThis amount is a function of the number of trucks and truck mileage per day from the Constants section.

Gallons of gas used per dayThis amount is a function of truck mileage per day and miles per gallon from the Constants section. Use the ROUNDUP() function with zero decimal places to avoid specifying a part of a gallon.

Production worker payThis amount is the production worker pay for all workers during the year. The value is a function of the number of workers, the pay per hour, the number of hours per day (12, a constant you can hard-code), and the number of business days.

Truck driver payThis amount is the truck driver pay for all drivers during the year. The value is a function of the number of drivers, the pay per hour, the number of hours per day (12, a constant you can hard-code), and the number of business days.

Cost of gasolineThis amount is a function of the number of gallons used per day, the cost of a gallon of gasoline during the year (a value in the Inputs section), and the number of business days.

Selling price of a Muffin The price is $12 each year if the more expensive advertising campaign is used; otherwise, the price is $11.

Variable cost of a MuffinThis amount is the cost of ingredients, supplies, power, and other items used to make a cupcake. The cost is a function of food price increase during the year and the prior years variable cost. Thus, the 2021 cost is based on the 2020 cost and the 2020 food cost increase. The 2022 cost is based on the 2021 cost and the 2021 food cost increase, and so on.

Advertising costThis amount is a function of the campaign value from the Inputs section. Campaign costs are shown in the Constants section.

Truck leasing costThis is amount is a function of the number of trucks, the leasing cost per day and the number of business days.

Income and Cash Flow Statements:

Beginning-of-year cash on handThis value is the cash on hand at the end of the prior year.

Revenuethis amount is a function of the number of Muffins needed and the selling price. Both of these values are from the Calculations section.

Cost of Muffins producedthis value is a function of the number of Muffins produced during the year and the variable cost of a Muffin. Both of these values are taken from the Calculations section.

Production worker payFrom Calculations section can be copied here.

Truck driver payFrom Calculations section can be copied here.

Truck leasingFrom Calculations section.

AdvertisingFrom Calculations section.

GasolineFrom Calculations section.

AdministrativeFrom Constants section.

Total costsThis amount is the total cost of Muffin production, production worker pay, truck driver pay, truck leasing, advertising, gasoline, and administrative costs.

Income before interest and taxesThis amount is the difference between revenue and total costs.

Interest expenseThis amount is the product of the debt owed to the bank at the beginning the year and the interest rate for the year (a value from the Constants section).

Income before taxesThis amount is the difference between income before interest and taxes and interest expense.

Income tax expenseThis amount is zero if income before taxes is zero or less than zero (a loss). Otherwise, income tax expense is the product of the years tax rate (a value from the Constants section) and income before taxes.

Net income after taxesThis amount is the difference between income before taxes and income tax expense.

Net cash position(NCP)The NCP at the end of a year equals the cash at the beginning of the year plus the years net income after taxes.

Borrowing from bankAssume that your bank will lend enough money at the end of the year to reach the minimum cash needed to start the next year. If the NCP is less than this minimum, the company must borrow enough to start the next year with the minimum. Borrowing increases the cash on hand.

Repayment to bankIf the NCP is more than the minimum cash needed and some debt is owed at the beginning of the year, you must pay off as much debt as possible (without taking the cash below the minimum amount required to start the next year). Repayments reduce cash on hand.

End-of-year cash on handThis amount is the NCP plus any borrowing and minus any repayments.

Debt Owed Section:

Beginning-of-year debt owedDebt owed at the beginning of a year equals the debt owed at the end of the prior year.

Borrowing from bankThis amount has been calculated elsewhere and can be echoed to this section. Borrowing increases the amount of debt owed.

Repayment to bankThis amount has been calculated elsewhere and can be echoed to this section. Repayments reduce amount of debt owed.

End-of-year debt owedIn all years, this is the amount owed at the beginning of a year, plus borrowing during the year, and minus repayments during the year.

Deliverables

1. Using the data determine if you can make money in the cupcake business. Explain.

2. You want to model three economic combinations of food price inflation and gasoline price.

Favorable condition: Food inflation 1% each year; gallon of gasoline $3.00 each

SoSo condition: Food inflation 3% each year; gallon of gasoline $4.50 each

Unfavorable condition: Food inflation is 5% each year; a gallon of gasoline is $5.00

With these three conditions each of advertising campaigns need to be combined to get six scenarios:

Favorable-High---Favorable Economic Condition and High advertising campaign

Favorabe-Low---Favorable Economic Condition and Low advertising campaign

SoSo-High---SoSo Economic Condition and High advertising campaign

SoSo-Low--- SoSo Economic Condition and Low Advertising campaign

Unfavorable-HighUnfavorable Economic Condition and High advertising campaign

Unfavorable-Low---Unfavorable Economic condition and Low advertising campaign

Run what if scenarios using the Scenario Manager by using 6 sets of input values.

Put all the Scenario Manager results in a separate spreadsheet tab.

Submit your analysis. Explain.

1

Muffins-Investment-Data-2020.xlsx Sheet3 2011 20 10 20 so we 100 100 Muffins Sheet2 Mullins Investment Constants Minimum cash needed at started you Number of Maximum of Muffins de Number of Multirokcan carry Cestelweinik per day Number of day Takmiles per le Prawie wirker pay per hr Tack driver pay per hour Mive costs yow High cost hvertising campaign Lowcost advertising campai Interest rate o Inputs Ndvertising Level (1-High-Low) Food in your Coastal pathaal paya (SLAN) 2020 NA NA NA NA NA NA NA NA NA NA NA NA NA NA 10 16 15 30 IS 15 15 10 20 10 15 15 30 2 6.000 4.00 20 10 20.00 12.000 TA 59 2021 NA 2022 NA NA NA NA Summary of Key Results 2021 2022 2091 2020 NA NA NA 2011 2031 Indycar cash anhand Fadeyear debewed Calculations Per day demand for Muffins Muffins produced in year Number of production workers wedd Number of delivery truded day Galase Producten wekery Take . Selle pea Muffin Variable cost of a Mutin Acting.com To poder 2020 NA NA NA NA NA NA NA NA N NA per day NA NA 2022 Income and Cash Flow Statements Begge areas and NA NA Cast of Muffined Trackery Track Advertising Gucles | NA NA NA NA NA NU NA NA NA Total coste NA NA Income ci interest and an Treme before Net income after tages Net cash post NCP Borrowing from bank Repsol End of your cash and NA NA NA DeMoned Beginning of your wed Berring tombank Repubuk Ende-year dhewed NA NA NA S. Muffins-Investment-Data-2020.xlsx Sheet3 2011 20 10 20 so we 100 100 Muffins Sheet2 Mullins Investment Constants Minimum cash needed at started you Number of Maximum of Muffins de Number of Multirokcan carry Cestelweinik per day Number of day Takmiles per le Prawie wirker pay per hr Tack driver pay per hour Mive costs yow High cost hvertising campaign Lowcost advertising campai Interest rate o Inputs Ndvertising Level (1-High-Low) Food in your Coastal pathaal paya (SLAN) 2020 NA NA NA NA NA NA NA NA NA NA NA NA NA NA 10 16 15 30 IS 15 15 10 20 10 15 15 30 2 6.000 4.00 20 10 20.00 12.000 TA 59 2021 NA 2022 NA NA NA NA Summary of Key Results 2021 2022 2091 2020 NA NA NA 2011 2031 Indycar cash anhand Fadeyear debewed Calculations Per day demand for Muffins Muffins produced in year Number of production workers wedd Number of delivery truded day Galase Producten wekery Take . Selle pea Muffin Variable cost of a Mutin Acting.com To poder 2020 NA NA NA NA NA NA NA NA N NA per day NA NA 2022 Income and Cash Flow Statements Begge areas and NA NA Cast of Muffined Trackery Track Advertising Gucles | NA NA NA NA NA NU NA NA NA Total coste NA NA Income ci interest and an Treme before Net income after tages Net cash post NCP Borrowing from bank Repsol End of your cash and NA NA NA DeMoned Beginning of your wed Berring tombank Repubuk Ende-year dhewed NA NA NA S