Excel Project #1 Rose Meyer has decided to open an at-home bakery called Rosewater Baked Goods. Rose plans to sell cookies, cupcakes and specialty breads every weekend at a local farmer's market. After much consideration, Rose has decided to use the three recipes below for her business. Please review the recipe cards for information on ingredients and labor required as well as the yield from each batch. Chocolate Chip Cookies: Recipe Card #1 Chocolate Cupcakes: Recipe Card #2 Banana Bread: Recipe Card #3 After some research, Rose has determined the price of ingredients and compiled a list at the below link. Ingredient Prices Rose has determined she will need to buy new bakery equipment for a total of $6,000. The bakery equipment has a 5 year useful life and $1,000 salvage value. She also wants to buy a Point-of-Sale (POS) system so that she can accept credit cards. The hardware for the POS system costs $2,500 and has a 5 year useful life and no salvage value. Rose will also need to pay a $50 monthly fee for the POS software. Rose plans to spend $100 per month on advertising, $300 per month on insurance, and $200 per month on rent for a stand at the local farmer's market. Variable overhead (parchment paper, cooking spray, etc.) is $0.75 per unit. Rose plans to do all of the mixing, baking, decorating, and packaging herself at a rate of $15 per hour. She has hired her daughter, Sue, to run the stand at the farmer's market for her at a rate of $10 per hour. The farmer's market is open on Saturdays and Sundays from 8am to 3pm for 50 weeks during the year. Sue will need 30 minutes to set up the stand each day and 30 minutes to take down the stand each day. Sue's hours do not fluctuate based on customer activity or production. The farmer's market Rose plans to sell her baked goods at is in a high-traffic area and very popular, therefore Rose expects to sell 36,000 units during the year. One unit can be a cookie, a cupcake or a slice of bread. For her first year in operations, Rose has hired you to assist with the bookkeeping. She has come up with a list of tasks she would like your help with which you can find on the subsequent tabs. Please review and complete the tasks as requested. General Hints A "batch" is one recipe card, i.e. 24 cookies, 24 cupcakes or 12 slices of bread. Make sure your calculations are consistent, i.e. in batches or on a per unit basis. Make use of cell references and formulas wherever possible. Round calculations to 2 decimal points (typically only necessary when using division). You may find it helpful to go through the additional tabs of info first to do calculations ahead of the task tabs so you can reference back to them. Task #3 Rose has decided on the following sales prices for her baked goods: $2.50 per chocolate chip cookie, $4.00 per chocolate cupcake and $3.00 per slice of banana bread. Rose has asked you to calculate the break-even point in units if she expects the relative sales mix to be 50% chocolate chip cookies, 40% chocolate cupcakes and 10% banana bread. Rose also wants you to calculate the sales volume in units required to earn $25,000 in profit. Given that Rose expects to sell 36,000 total units this year, what is her margin of safety expressed as a percentage? "Reminder that contribution margin is sales minus variable costs. Break-Even Item Chocolate Chip Cookie Chocolate Cupcake Banana Bread Contribution Margin Relative Sales Mix WA Contribution Margin Total Weighted Average Contribution Margin Total Fixed Costs Weighted-Average Contribution Margin Total Break-Even (Units) Break-Even (Units) - Chocolate Chip Cookies Break-Even (Units) - Chocolate Cupcakes Break-Even (Units) - Banana Bread Sales Volume ($25k Profit) Item Contribution Margin Relative Sales Mix WA Contribution Margin Chocolate Chip Cookie Chocolate Cupcake Banana Bread Total Weighted Average Contribution Margin Total Fixed Costs & Profit Weighted-Average Contribution Margin Total Sales Volume (Units) Sales Volume (Units) - Chocolate Chip Cookies Sales Volume (Units) - Chocolate Cupcakes Sales Volume (Units) - Banana Bread Margin of Safety Budgeted Sales Break-Even Sales Margin of Safety Task #14 At the end of her first year of operations, Rase provided the following list of actual transactions. She has requested that you prepare a horizontal statements model (see below for example) including closing out any over or underapplied overhead to cost of goods sold. Assume all transactions use cash (where applicable). Year 1 Transactions A Rose invested $50,000 cash into the business. B. Purchased bakery equipment for $6,000 with a 5 year useful life and $1,000 salvage value. C. Purchased POS equipment for $2,500 with a 5 year useful life and no salvage value. D. Purchased $7,000 of ingredients throughout the year. E. Applied manufacturing overhead for 760 batches of chocolate chip cookies, 605 batches of chocolate cupcake batter, and 155 batches of banana bread. F. Used ingredients to make 760 batches of chocolate chip cookie dough, 605 batches of chocolate cupcake batter, and 155 batches of banana bread batter. G. Paid wages to Rose for her time mixing, baking, decorating, and packaging the baked goods; 760 batches of chocolate chip cookies, 605 batches of chocolate cupcakes and 155 batches of banana bread. H. Completed all batches of baked goods in the work in process inventory account. I. Sold 752 batches of chocolate chip cookies, 599 batches of chocolate cupcakes and 145 batches of banana bread. J. Actual variable manufacturing overhead for the year was $30,000. K. Paid $200 per month to rent a stand at the farmer's market. L. Paid $300 per month on insurance. M. Paid $100 per month on advertising. N. Paid $50 per month for the POS software. O. Paid wages to Sue for her time working the farmer's market stand. Event Cash MOH Raw Materials Assets WIP Equity Check #1 Revenue Expenses Net Income Check #12 Finished Goods Bakery Equip. POS Equip. A 50,000 Paid-In Capital 50,000 Ret.Earn. Balanced Balanced B1 Balanced Balanced B2 C1 Cz D E F G 11 12 . K L M N 0 Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced Balanced CLOSE TOTAL Balanced Balanced Try 50,000 Again 50,000 Balanced Balanced Correct! "Hint: Reference other tabs when possible and make sure to round any additional calculations to 2 decimal points