Can you help me to create CVP base on these information, please.

Cat & Joe's Pig Rig: Should We Stay or Should We Go? Tony Bell Thompson Rivers University Andrew Fergus Thompson Rivers University INTRODUCTION When the invitation arrived, Joe Thompson and Cathy (Cat) Obertowitch were not sure what to do. The event looked promising, but the last time they agreed to attend a similar special event, they had barely broken even. They had left the event reminding themselves, \"We don't need to say 'yes' to every opportunity.\" Joe and Cat were an engaged couple who had been running their food truck, Cat & Joe's Pig Rig, for several months. Their truck specialized in pulled pork and southern-style barbecue. \"Slow and low1\" was the cooking philosophy of the food truck, which was based in Kamloops, British Columbia, Canada, a city of 100,000. Business had been brisk, the truck was outperforming projections, and their customer base was growing. They had also supplemented their day-to-day business by attending local events and doing catering jobs. The couple had just received a request to bring their truck to an event called \"Bullarama\"a rodeo held in the nearby town of Barriere, located 70 kilometers north of Kamloops (Exhibit 1). Bullarama looked great on paper: the promoters noted that 700 attendees were expected, Cat & Joe's Pig Rig would be the only food option, and rodeo fans would be a great market for the company's southern-style barbecue. Tempering their enthusiasm for the event were a few mitigating factors: (1) event promoters tended to be optimistic with promises and projections, (2) the 70-kilometer drive to Barriere added a number of costs that may be significant, and (3) perhaps most importantly, business was good in Kamloops, and if they did the Bullarama event, they would forgo one day's revenues in their home market. The couple couldn't be sure of what to do until they fully analyzed the opportunity. CAT AND JOE Cat and Joe came from the neighboring towns of Smithers and Houston in northern British Columbia. They knew each other growing up but never connected beyond the level of acquaintances. Cat recalled their relationship as teens: \"I was interested in Joe, and Joe was interested in hockey.\" The two lost touch, married other people, and started their own families. Cat had one daughter and two sons, while Joe had two sons of his own. Cat went to school for nursing and referred to her career as that of a \"gypsy nurse\" working for a wide variety of organizations. Her most recent jobs included a role at a pregnancy outreach center and an instructional post in the nursing program at the local university in Kamloops. Joe's career had been more stable. After a short time as a cook in a restaurant, he found a permanent career behind the wheel of a logging truck, first in northern British Columbia then moving to the city of Merritt, 100 kilometers south of Kamloops. Cat and Joe's marriages dissolved. Eventually, the newly single acquaintances reconnected on Facebook when the social media site suggested that they might know each other. The two began dating, and on one of their earliest dates, Joe cooked for Cat. On that night, Joe's pulled pork sandwich proved to be his way into Cat's heart. She was surprised to learn that one of Joe's hobbies was smoking meat. After high school, as soon as Joe could afford a smoker, he bought one, and in the two decades since, he had become an expert in the art of smoking and slow-cooking beef and pork. He also enjoyed making his own rubs and sauces. As their relationship became more serious, Joe moved from Merritt to Kamloops to live with Cat. Joe's workplace was still based an hour away in Merritt, leaving him little time for family after the commute and his long days driving the logging truck. Neither Joe nor Cat was happy with this arrangement, with Joe spending a lot more time on the road than at home. Something had to give, so Joe began to look for new opportunities closer to Kamloops. It was at a potluck dinner that Cat and Joe got the inspiration for their business. Joe brought a dish from his smoker, and it was a hit. Two of their friends who attended the party, Cye Delaney and Denise Leigh, were owner-operators of a popular local tattoo parlour. These experienced entrepreneurs suggested that Joe's pork was so good that he and Cat had a legitimate business opportunity. They agreed to give Cat and Joe advice if needed and put the couple in touch with an angel investor. When it came time to meet the potential investor, both Cat and Joe were nervous. They wanted the opportunity badly, but it was one thing to impress friends at a potluck and another thing entirely to impress a strangerand to impress him so much that he would be willing to invest tens of thousands of dollars in a business concept proposed by two inexperienced entrepreneurs. Joe and Cat brought the possible investor a sample of the items they planned to include on the menu, and perhaps more importantly, they also brought a conservative, but thorough, business plan. The angel investor was so excited by the food and the business plan that he wrote them a check on the spot. With that meeting, Cat & Joe's Pig Rig was born. FOOD TRUCKS During this time, food trucks were an emerging culinary trend in Canada and around the world. While mobile concessions and canteens had existed for decades, there was a new wave of food trucks, which focused on bringing higher-end fare to the marketplace. The old model for food trucks often involved selling frozen or nonperishable products, whereas the new model relied on technological improvements to miniaturize and mobilize full, gourmet kitchens, enabling vendors to offer a much broader array of dishes. As of 2014, Vancouver, British Columbia, had more than 100 active food trucks selling all types of dishes, including Indian, Korean, Japanese, seafood, Mexican, barbecue, crepes, Ukrainian, and more.2 Cat and Joe's pulled pork concept would be the first food truck attempted in the city of Kamloops. After meeting with local politicians and agreeing to some limitations3, Cat & Joe's Pig Rig was given the city's blessing to begin operating. They purchased and outfitted their truck and opened for business (Exhibit 2). THE BUSINESS Cat & Joe's Pig Rig saw immediate success. In the early months, the business outperformed its revenue and profit projections. But Cat and Joe did not wish to rest on their laurels. They knew that they were enjoying early success not only because they offered a good product but also because the food truck was a novelty in Kamloops. They were pleased to have a first-mover advantage, but they knew it would not last forever. They needed to continue to develop a loyal customer base and were also working hard to expand the event and private catering side of their business. The food truck's signature dish was its \"Ripped Pig\" pulled pork sandwich.4 The sandwich came in a combo with coleslaw, baked beans, and French fries and was priced at $12. The company had variable costs, which included the cost of the food, clamshell packaging, and variable overhead. Variable costs were 40% of the company's revenues. There was no labor cost as neither Joe nor Cat drew a wage or salary. Fixed costs included items such as gas for the generator, maintenance, business licenses, and truck depreciation. These costs totaled $10,000 per year. The operational year for the food truck was 180 days. Corporate income tax rates for small businesses in British Columbia were approximately 20% around that time. The pork needed to be put in the smoker at least 12 hours in advance of service, which created two challenges for Joe. First, it meant that he worked virtually 24 hours a day. Operating the truck meant setting up, serving, and cleaning up from 10 a.m. to 7 p.m. But when service was over, Joe's day was not done. He needed to smoke the pork overnightwhich involved putting the pork in the smoker late in the evening (with just the right blend of wood chips), and waking up to tend to the meat in two-hour intervals throughout the night, spraying the meat to ensure it would have the right consistency and tender quality when it was served the next day. Although it was exhausting work, Joe was willing; he had a great work ethic, he was his own boss, and smoking meat was one of his passions. The second challenge presented by the 12-hour cooking requirement was determining how much pork to smoketoo much or too little could be a disaster. If Joe did not prepare enough meat the night before, he could not simply go out and buy more if they were having an unusually busy day. Failure to project high demand meant the Pig Rig would be sold out for the day, and Joe and Cat would need to close the truck early, leaving customers unsatisfied. If Joe prepared too much meat, and they didn't sell out, the extra meat would be donated to a local soup kitchen. While Cat and Joe felt good about doing something generous in their community, donating pork meant inefficiency and significantly reduced their profits. Forecasting poorly was a huge risk for their business, and mistakes were costly. Fortunately, experience meant that Cat and Joe were getting better at predicting how many customers they could expect in a day. On a typical day, Cat and Joe served between 75 and 125 patrons, with an average of 100. The amount varied based on the weather, the day of the week, and other factors such as nearby local events. Joe also had a formula for when the truck was invited to special events: He expected 35% of attendees would purchase food, not necessarily from him, but from one of the food vendors at the event. He would use this ratio to estimate the number of potential customers. He would then divide his estimate for potential customers by the number of vendors serving the event. If he was the only vendor, he would get all of the potential customers, if there were two vendors, he expected to get 50% of the food-buying customers. This number would serve as his guideline for how many pounds of meat he would need to smoke the night before. It had proven to be accurate in the past, and Joe intended to use this formula for any special events going in the future. A crucial aspect of the company's success was its marketing strategy, which focused on social media. Because their food truck changed locations frequently, Cat and Joe wanted to ensure that customers knew where to find them, and the best way to do this was online. They were very active on Facebook, Instagram, and Twitter5 and, as of 2014, had not spent any money on traditional marketing. They had the largest social media presence of any restaurant or food truck in Kamloops. And it was through social media that the organizers of Bullarama contacted Cat and Joe. THE BULLARAMA DILEMMA The invitation was succinct. It explained that Cat & Joe's Pig Rig would be welcomed at Bullarama in Barriere, British Columbia. Bullarama was a charity rodeo event, where novice, junior, senior, and professional riders would compete. A handicapping system would be used to ensure all riders could expect competitive scores. According to event organizers, 700 tickets had been sold. When Joe and Cat brought their truck to special events they did not serve their usual pulled pork sandwich combo. They served only the sandwich, with no beans, coleslaw, or French fries. This enabled them to serve customers much more quickly and to reduce their price to $9 per serving. It also let them replace their expensive clamshell packaging with a much cheaper foil wrapping. With fewer side dishes and less expensive packaging, variable costs would be reduced by $1.90 per customer when compared to their normal menu. There were several other cost considerations related to the Bullarama event. First, the event organizers suggested a donation of $100.6 Second, their food truck ran on propane, and the 140 kilometer round trip to Barriere would add $100 to their typical fuel costs. Finally, in order to maximize space for the mobile cooking equipment, the truck only had one seat (for the driver), so if Joe drove the food truck, Cat would need to drive her car separately, with an expected extra gas cost of $30. All of these costs would be avoided if they stayed home in Kamloops. The couple had one other concern. The organizers promised that Cat & Joe's Pig Rig would be the only food option available to event attendees, but the entrepreneurs had heard IMA EDUCATIONAL CASE JOURNAL VOL. 7, NO. 3, ART. 3, SEPTEMBER 2014 such promises before and found they were not always reliable. While they trusted the event organizers, they were worried about the potential for other onsite competitors. They planned to do calculations for multiple scenarios. The couple reminded themselves that business was good in Kamloops, but this represented an opportunity to expose their product to a new, potentially lucrative market. As Joe opened the calculator app on his smartphone, he reminded himself that numbers were important, but this decision would not be based on numbers alone. There were a lot of other factors to consider. ENDNOTES 1. Slow and low is a cooking style synonymous with southern-style barbecue. It refers to the fact that meat is cooked slowly at a low temperature to achieve an extremely tender texture. 2. A full list of active food trucks in Vancouver can be found at http://vancouver.ca/peopleprograms/street-food-vending.aspx. 3. Limitations included meeting all health-code standards that apply to restaurants and adhering to operating boundaries to prevent the food truck from poaching customers from established restaurants. 4. Although there were other items on the menu, they had very similar prices and costs (margins were virtually identical). For the purposes of calculations in this case, assume there was only one item on the menu: the Ripped Pig sandwich. 5. Cat & Joe's Pig Rig had more than 1,700 \"Likes\" on Facebook, more than 600 Twitter followers, and nearly 200 followers on Instagram. 6. It was not unusual for charity events to request its vendors to make a donation, and although the donation was \"suggested,\" it was realistically a requirement if Cat and Joe wished to attend the event. IMA EDUCATIONAL CASE JOURNAL VOL. 7, NO. 3, ART. 3, SEPTEMBER 2014 ACC 3400: Fall 2017 Project 5: 30 points The purpose of this assignment is to provide an introduction to Excel's what-if analysis. You will then use this Excel skill to analyze the case: Cat & Joe's Pig Rig. In cost accounting determining the relationships between cost, volume and profit are very important. This assignment will have you use scenarios and goal seek to calculate breakeven and changes in cost and volume. Introduction to what-if analysis By using what-if analysis tools in Microsoft Office Excel, you can use several different sets of values in one or more formulas to explore all the various results. For example, you can do what-if analysis to build two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result. Excel provides several different tools to help you perform the type of analysis that fits your needs. Overview What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of what-if analysis tools come with Excel: scenarios, data tables, and Goal Seek. Scenarios and data tables take sets of input values and determine possible results. A data table works only with one or two variables, but it can accept many different values for those variables. A scenario can have multiple variables, but it can accommodate only up to 32 values. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result. Use scenarios to consider many different variables A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. For example, suppose you have two budget scenarios: a worstu case and a best case. You can use the Scenario Manager to create both scenarios on the same worksheet, and then switch between them. For each scenario, you specify the cells that change and the values to use for that scenario. When you switch between scenarios, the result cell changes to reflect the different changing cell values. Worst case scenario Changing cells Result cell Best case scenario Changing cells Result cell After you have created all the scenarios that you need, you can create a scenario summary report that incorporates information from those scenarios. A scenario report displays all the scenario information in one table on a new worksheet. Scenario summary report NOTE Scenario reports are not automatically recalculated. If you change the values of a scenario, those changes will not show up in an existing summary report. Instead, you must create a new summary report. Create a scenario Before you create a scenario, you should have an initial set of values already on the worksheet. To make scenario summary reports easier to read, you should also consider naming the cells that you plan to use in scenarios. 1. 2. 3. 4. 5. 6. 7. 8. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager. Click Add. In the Scenario name box, type a name for the scenario. In the Changing cells box, enter the references for the cells that you want to specify in your scenario. For example, if you want to see how changing the values of cells B1 and B2 will affect the outcome of a formula based on those cells, enter B1,B2. NOTE To preserve the initial values for the changing cells, add a scenario that uses those values before you create additional scenarios that use different values. Click OK. In the Scenario Values dialog box, type the values that you want to use in the changing cells for this scenario. To create the scenario, click OK. If you want to create additional scenarios, repeat steps 2 through 8. After you finish creating scenarios, click OK, and then click Close in the Scenario Manager dialog box. Display a scenario When you display a scenario, you switch to the set of values that are saved as part of that scenario. The scenario values are displayed in the cells that change from scenario to scenario, in addition to the results cells. For example, using the preceding scenarios, if you display the Best Case scenario, cell B1 displays 150000, cell B2 displays 26000, and cell B3 displays 124000. 1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager. 2. Click the name of the scenario that you want to display. 3. Click Show. NOTE After you close the Scenario Manager dialog box, the values from the last scenario that you displayed remain on the worksheet. If you saved your initial values as a scenario, you can display those values before you close the Scenario Manager dialog box. Create a scenario summary report 1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager. 2. Click Summary. 3. Click Scenario summary or Scenario PivotTable report. 4. In the Result cells box, enter the references for the cells that refer to cells whose values are changed by the scenarios. Separate multiple references with commas. Use Goal Seek to find out how to get a desired result If you know the result that you want from a formula, but you are not sure what input value the formula requires to get that result, you can use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long a period you want in which to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you must secure in order to meet your loan goal. NOTE Goal Seek works with only one variable input value. If you want to determine more than one input value, for example, the loan amount and the monthly payment amount for a loan, you should instead use the Solver add-in. Let's look at the example, step-by-step. Because you want to calculate the loan interest rate needed to meet your goal, you use the PMT function. The PMT function calculates a monthly payment amount. In this example, the monthly payment amount is the goal that you seek. PREPARE THE WORKSHEET 1. Open a new, blank worksheet. 2. First, add some labels in the first column to make it easier to read the worksheet. 1. In cell A1, type Loan Amount. 2. In cell A2, type Term in Months. 3. In cell A3, type Interest Rate. 4. In cell A4, type Payment. 3. Next, add the values that you know. 1. In cell B1, type 100000. This is the amount that you want to borrow. 2. In cell B2, type 180. This is the number of months that you want to pay off the loan. NOTE Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek. 4. Next, add the formula for which you have a goal. For the example, use the PMT function: In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this example, you want to pay $900 each month. You don't enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula. The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate. Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now. 5. Finally, format the target cell (B3) so that it displays the result as a percentage. 1. On the Home tab, in the Number group, click Percentage. 2. Click Increase Decimal or Decrease Decimal to set the number of decimal places. USE GOAL SEEK TO DETERMINE THE INTEREST RATE 1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek. 2. In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4. 3. In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment. 4. In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3. 1. NOTE The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box. 5. Click OK. Goal Seek runs and produces a result, as shown in the following illustration. Assignment Read Cat & Joe's Pig Rig: Should We Stay or Should We Go? Required: 1. Using the Excel Template given enter given information about the Ripped Pig Sandwich (price of $12 and variable cost are 40% of the company's revenues). Fixed cost are $10,000 per year and they operated 180 days a year so use $56 per day for fixed cost. A realistic typical day in Kamloops is to sell 100 sandwiches. 2. Set up formulas to calculate, Revenue, Cost, & Income 3. Use formulas to complete the contribution margin income statement. Remember from the case that the income tax rate is 20% 4. Use scenario manager to save this data that you added to the Data Table. [name this scenario: Kamloops Day Realistic] Hint: only save the four variables in the data table. If you put a formula in any of these cells, allow scenario manager to change these values to constants. 5. Use Goal Seek to find out how many passengers are necessary to breakeven. (Hint: you will need to set income to 0) 6. Use scenario manager to save this breakeven information [name this scenario: BE Kamloops] *note if sandwiches are not whole round up in all scenarios 7. On an optimistic day they sell 125 sandwiches (save this scenario as Kamloops Optimistic 8. On a pessimistic day they sell 75 sandwiches (save this scenario as Kamloops Pessimistic) 9. How many sandwiches do they need to sell on a typical day to make $560 in after-tax profit? (Hint: you will need to set net income to $560). Save this scenario as Kamloops Des Profit. 10. Evaluate the Bullarama Event. (You will need to update the price, variable cost and fixed cost. This event is in place of a typical Kamloops day) a. Optimistic sales for the event will be if no competitor shows up. Save this scenario as Bullarama Optimistic b. Conservative sales for the event will be if one competitor shows up (will split the sales in half with the other competitor). Save this scenario as Bullarama Conservative c. Pessimistic sales for the event will be if two competitors show up (will get 1/3 the sales). Save this scenario as Bullarama Pessimistic d. Use goal seek to find out how many sandwiches are necessary to breakeven at the event. 11. Create a scenario summary, include the items for the contribution margin in the results (set result cells as the cells that contain this numerical information) 12. Set up the scenario summary table to print on one page and edit the table so that it would be useable to make a decision. 13. In a memo (use word or other word document software) to Cat & Joe explain the nonfinancial advantages and disadvantages of attending the Bullarama event. Include in this memo your recommendation on attending the event or not. Justify your answer with both financial and nonfinancial data. 14. Submit your Excel file and memo through Blackboard Please remember that the work you submit is expected to be your own and created during this semester. Please review the syllabus and/or contact me if you have any questions