Create the following items from the information below:

Cash Flows Budget

Data Input Sheet

Production Budget

Projected Balance Sheet

Projected Income Statement

Sales Budget

Hamilton Bakery: Master Budgeting Background Hamilton Bakery is a medium-sized regional bakery that specializes in providing orders to grocery and convenience stores. Because of the popularity of its brand, it has also opened a small caf for walk-in business In order to maintain its high quality standard, Hamilton produces only three products: breakfast muffins, fresh bread, and chocolate chip cookies. Although business has been good in the past few years, a lucky contact with a large chain has recently allowed it to expand its brand out of the local region. Growth has been high since the new contract went into effect. Tom Spear, the chief executive officer (CEO) and founder, has arranged a meeting with a venture capital firm next week. Hopefully the meeting will result in the sale of some of Hamilton' stock and an opportunity to establish a significant line of credit with the venture capital firm. These extra funds, if Tom can secure them, should provide sufficient money to meet Hamilton' growth targets for the next few years. The venture capital firm's assessment team has asked Tom to provide a quarterly master budget for the coming year complete with pro forma financial statements, at the meeting. They have expressed special interest in Hamilton' earnings per share (EPS), cash flow from operations, and profit margins, indicating that good numbers in these areas will be essential for final approval In typical managerial style, Tom immediately assigned the task of creating the budget to Elaine Olsen, who has just been hired as Hamilton controller. Since this project is her first assignment, Elaine started by making appointments with each of the divisional managers to gather information for the budget and also to learn more about the company Creating the Budget: Meetings with Divisional Managers Meeting with the Sales Department Walking down the hallway towards the office of Frank Byron, the sales manager, Elaine read the results for last quarter. Hamilton Bakery sold 45,000 one-dozen packages of muffins for $5.50 each, 65,000 one- dozen packages of cookies for $4.75 each, and 85,000 loaves of bread for S5.25 each. When Elaine got to Frank's office, he motioned her in to have a seat "Is it time for our meeting already?" he asked. "Where does the day go?" "Who knows? It seems like one minute I'm having my morning muffin and the next I'm saying good-bye to everyone" Elaine said with a sigh. "There's never time to get everything done. And now I get to do the budget." Frank started to laugh. "Thanks," she muttered. "I knew I could count on your support." "T'm sorry. I just have to laugh at the amount of time you are going to put into something that isn't really used anyway, except for setting bonuses, of course." "Not really used? I don't know how it's been around here in the past, but this year, at least, the budget will prove to be a valuable tool." Elaine waved away Frank's retort. "Anyway, one way or another I have to create one and, as you know, the process always starts with projected sales. Do you have a copy of last quarter's results?" "Yes, right here somewhere," Frank said, shuffling papers around on his desk. "Got it!" he exclaimed, waving it gently as he pulled it from under a stack of other papers. "Now, what do you want to know exactly?" "Well, Tom thinks that since we have established a strong following both locally and in our new markets, we can raise our prices slightly next year without a sharp drop in sales. He was thinking $6.00 for muffins, $5.25 for cookies, and S5.75 for bread. What do you think?" "I agree," Frank said eagerly. "I've been pushing that for years. Of course, I think that sales will drop some in the first quarter of next year. They always drop off a bit after the holidays anyway, but with the increase in sales price. . . I'd say a 20 percent drop from the fourth quarter results we have here." He looked up questioningly and raised an eyebrow Elaine frowned. "That sounds kind of high. Based on what I saw in the dairy industry, I was thinking the drop would only be about 10 percent." Frank looked a little uncomfortable and shuffled around in his chair. "Well, it's a little different for a bakery Our price is a little more elastic than dairy products. Besides, 20 percent is a more conservative estimate and in the past we wanted no surprises." He looked at her and challenged, "Are you going to change that?" "Yeah, but we'll be using this master budget to create a cash flow budget and pro forma financial statements to show our new investor. We need to look good, not bad." Elaine frowned. She didn't want to start making changes and enemies in her first few months. "I guess so. But, look-my bonus is tied to how well I meet my estimates. If we estimate low results and then go up..." Seeing the look on her face, he quickly changed direction. "Besides, Elaine, we are raising the prices. A 10 percent drop is normal after Christmas, but couple that with the increased prices, and 20 percent is reasonable Elaine frowned, and then sighed. She didn't quite accept his reasoning, but it would be better to have him on her side until she understood the company politics a b We'll use 20 percent. After all, you're the expert." "You've got that right!" Frank said, trying to hide his relief. He was obviously really counting on that bonus. He looked at a couple of sales reports and market projections on the desk in front of him. "After that, I think sales will grow steadily at about 5 percent a quarter with these new prices. Fourth-quarter sales it better. "Okay, Frank. I'll take your word for it. "Do you happen to know what we are paying each group of employees?" Bill grabbed a piece of paper. "We pay the mixers $7.50 an hour, the bakers $8.00 an hour, and the packers $6.50 an hour." "Perfect. Then I just have one more question." Let me guess. You want a breakdown of ingredients for each item we bake." "You must be psychic, Bill" "No, I just remember being bugged about this by the last controller." He handed Elaine a piece of paper with a table on it. "Here they all are. Just make sure you don't let it out of the building! I don't want my secret recipes to get out." "Don't worry. I'll be careful." Elaine glanced down at the price sheet. "Wow. I wish I could buy my groceries at these prices Bill chuckled. "So do I. You have to remember, though, that we buy in bulk, lots and lots of bulk. That lets us get some great deals from our local vendors." I guess that makes sense. Thanks for taking time to see me." "Just make sure you don't leave without taking a cookie or two." Bill held out a plate loaded with perfect, if two-day old, cookies. "If we don't eat them, they go into the trash!" "My pleasure!" Meeting with the Accounting Department Elaine hurried back to her own office. She had a staff meeting in 15 minutes. She should be able to get most of the information she still needed from Sarah, since she wrote the checks. Even though Sarah only worked part-time, she'd been with the bakery from the beginning and seemed to know just about everything about the accounting system. Anything Sarah didn't know, Bob, their new summer intern, would have found out for her by now. He was very good at digging up information once he was pointed in the right direction. "We thought you were going to stand us up," Sarah said as Elaine hurried into the office "Actually, we hoped you were," Bob quipped. "We don't want to get stuck doing the budget, so we hoped that you would forget to come." "Don't worry," Elaine said with a sigh. "Tom wants me to take care of it personally. He seems to think it would be good for me to get to know the company or s that I asked for?" "Of course," Sarah said. "Where do you want us to start?" Let's start with our accounts payable." "That's me," Bob said. "Most of our vendors require that we pay for everything within 30 days of making our purchase. That means that 85 percent of our purchases are paid omething. So, have you gathered all the information for within the quarter they are made And, before you ask, we ordered $210,984 worth of inventory during the last quarter last year, so we still owe 15 percent of that, or $31,648." "Thanks, Bob, but I actually knew that last part. After all, it's right there in the balance sheet." "Oh, yeah," Bob said turning pink. "I forgot about that." Sarah laughed. "So, you calculated it by hand?" "Well, yeah. I wanted to be prepared for the meeting today." "All right, you two," said Elaine, jumping in before Sarah could pick on the young man any more. "Let's move on to our overhead assumptions "Sure," Sarah said. "Last year we allocated variable overhead at S1.50 for each direct labor hour. This year I think that we're going to need to increase that to $2.00 to cover increases in security fees, utility rates, and energy prices. We also spend about $160,000 a quarter in fixed overhead. Also, don't forget that we usually use total direct labor hours to calculate a predetermined overhead rate when calculating the unit cost." "Unit cost?" asked Bob. "Oh, wait," he said nodding, "I remember. We have to include direct materials, direct labor, and manufacturing overhead to get the cost of producing each unit. Direct materials are calculated from the recipe and direct labor cost from the employee information that Bill gave you. But we need to multiply the number of hours it takes to make each product by the predetermined overhead rate so that we can figure a per-unit applied overhead amount. Sorry to interrupt." "No problem." Elaine nodded approvingly at the young intern while finishing up her notes. "Just one last question, Sarah. How much of that overhead is from depreciation?" "Eight percent of the fixed amount. "Good. Bob, tell me about our sales costs." "Well, we don't really have that much in variable sales costs. We give a one percent commission to our sales staff." "Is that based on profit or sales price?" Sarah asked. Total sales price. Sorry, I forgot to mention that. The commission is paid both for business sales and sales in the caf. Also, here's the table of fixed selling and administrative expenses." Elaine took the paper. "Thanks. Okay, Sarah, tell me about our debt." "Well, at the end of last year, we secured a $1,109,969 mortgage at 6 percent interest. Our payment each quarter is $20,000. Since it's a mortgage, the calculations are kind of fun. Each payment requires us to pay a bunch of interest and a little bit of principal. To break up the $20,000 into the two parts, we have to multiply the current mortgage value by 6 percent and divide by 4.. . "Divide by 4?" asked Bob "Well, yeah, 6 percent is the annual rate. Since we make quarterly payments, we divide the annual rate by "Oh," Bob said sheepishly. "I should have remembered that." "Yes, you should have," Elaine said with a smile. She was very pleased with how well Bob was progressing during his summer with the firm. Hiring an intern had been one of her first changes, and it seemed to be working out well. If the company continued to grow, maybe he could be hired full-time once he graduated in a couple of years. "Go ahead, Sarah." "Right. So, our first payment will be made at the end of the upcoming quarter. We'll end up paying $16,650 as interest and $3,350 in principal. This means that the value of the mortgage in the second quarter will be $1,106,619. That's the original $1,109,969 minus the $3,350, Bob." "Thanks, Sarah. I appreciate the help," Bob retorted, rolling his eyes "I appreciate it, too," Elaine said. "If I remember right, we have to pay the $20,000 each quarter. Our contract prohibits us from paying any additional principal for the first three years." Sarah nodded. "Yep. Kind of a bummer, but that was the only way we could get that 6 percent interest rate." "Okay," Elaine said. "The last thing is a recap of how we handle income taxes. I think that has pretty much stayed the same?" "It sure has," Bob responded, rifling through a tax folder. "Our corporate tax rate is 30 percent and a portion of our estimated taxes must be paid each quarter to avoid late fees. Our policy is to pay 110 percent of the taxes that we owed last year over the course of the current year. Since we paid $15,000 last year, we will need to pay $16,500 this year." l pay that equally over the four quarters?" "Right. At the end of the year, we calculate our actual taxes owed as 30 percent of net income. Any difference between the cash we paid for taxes over the year and actual income tax expense on the income statement is put into income taxes payable if we haven't paid enough and into deferred tax assets if we paid too much. "Right," Elaine said. "I think that's about it." "Don't forget the balance sheet from last year," Bob said, handing her a sheet of paper "Thanks. I'm starting to lose track of everything. I must be getting old." "Oh, I wouldn't say that," Bob quipped, then added with a grin, "at least, not as long as you're my boss." Meeting with the CEO "So, how goes the battle, Elaine?" Tom asked as she came into his office 7 Sorry about that. Sometimes I go too fast. To get our interest payments when we repay our line of credit (assuming that we have any to repay and the funds to make a payment), I will multiply the amount I'm repaying times the quarterly interest rate times the number of quarters the money has been outstanding. So, if we draw $1,000 on the line of credit in the second quarter and repay it in t $1,000 by 2 percent and again by 2 percent for the two quarters that I assume it's been outstanding. Does that help?" he third quarter, I will multiply "Not really, but I think I understand enough that I can explain your assumptions if I have to." "Well, let me try again "No, don't worry about it," Tom said quickly. "As long as I know about what you're doing, and I don't have to do it myself, I'm good with just about anything." Elaine grimaced. "Thanks. I think I'll estimate my salary up a couple hundred thousand," she said jokingly Required Please read the case carefully and create a spreadsheet page for data input as you go. This is where you will find all the numbers necessary to create your budgets as you move through the case. It is important that you think through what numbers you will need and lay those out logically with clear labels. This serves two purposes: first, it will make it easier to create the budgets, and second, when you turn this budget over to management, they may wish to change some of the assumptions and see what results flow through the budget. To this end, you must show all assumptions on the first worksheet. You will need to create the following items Cash Flows Budget Data Input Sheet Production Budget Projected Balance Sheet Projected Income Statement Sales Budget You may also need to create supporting schedules and tables. Please group these logically on worksheets within schedule they need. Be sure to protect the entire workbook, but to unprotect the cells that contain specific assumptions on your data input sheet. This way the owners and managers can change assumptions and see how the changes flow through the budget. Think about the logical order of budgets. Your data input sheet should be the first worksheet. What should be the second worksheet? Make the spreadsheet document flow logically you r document and label the tabs so that the owners and managers can find whatever budget or Exhibit 1 Summary of Ingredients Cookies Muffins Bread Ingredients Flour Margarine Sugar Eggs (each) Milk (per gallo Cocoa Peanut Butter Chips Mini Chocolate Chips Shorter lbs,/dozen price/lb. total 0.5 0.15 SO.08 0.75 025 S0.19 1.20 S0.20 2 0.05 SO.10 Ibs,/dozen price/lb total bs/dozen price/lb. total 30150.45 0.5 SO.15 S0.08 25 $0.25 $0.06 .5 $0.20 $0.10 S0.05 0.10 0.10 S1.25 0.13 0.25 $0.20 $0.05 025 1.25 031 0.25 $1.50 $0.38 1 $0.75 75 S0.75 $0.75 025 0.50 $0.13 Baking Packet $0.10$0.10 1.79 1 0.100.10 1.31 1S0.10 S0.10 1.04 The Baking Packet consists of ingredients too small to be purchased by the pound, so the bakery buys them in prepared packets, Exhibit 2 List of Selling and Administrative Expenses S&A Expense Advertising Cleaning supplies Janitorial service Office staff salaries Office supplies Rent- Office Sales salaries Top management salaries Utilities-Office Cost/quarterd $ 40,000 1,000 6,000 25,000 3,000 9,000 35,000 80,000 1,800 S200,800 Total 10 Exhibit 3 Balance Sheet from Previous Year Assets Current Assets as Accounts Receivable Raw Materials Inventory Finished Goods Inventory $ 40,000 812,025 21,098 13,831 $ 886,954 Total Current Assets Property, Plant, and Equipment Land Building Equipment Accumulated Depr Equipment $ 75,000 568,000 750,000 (90,000) 1,303,000 Total PPE Total Assets $2,189,954 Liabilities and Stock Holders' Equit Liabilities Accounts Payable Mortgage Payable S 31,648 1,109,969 $1,141,617 Total Liabilities Stockholders' Equity Common Stock (no par value; 150,000 shares outstanding) Retained Earnings S 150,000 898,337 1,048,337 Total Stockholders' Equity Total Liabilities and Stockholders' Equity S2,189,954 Hamilton Bakery: Master Budgeting Background Hamilton Bakery is a medium-sized regional bakery that specializes in providing orders to grocery and convenience stores. Because of the popularity of its brand, it has also opened a small caf for walk-in business In order to maintain its high quality standard, Hamilton produces only three products: breakfast muffins, fresh bread, and chocolate chip cookies. Although business has been good in the past few years, a lucky contact with a large chain has recently allowed it to expand its brand out of the local region. Growth has been high since the new contract went into effect. Tom Spear, the chief executive officer (CEO) and founder, has arranged a meeting with a venture capital firm next week. Hopefully the meeting will result in the sale of some of Hamilton' stock and an opportunity to establish a significant line of credit with the venture capital firm. These extra funds, if Tom can secure them, should provide sufficient money to meet Hamilton' growth targets for the next few years. The venture capital firm's assessment team has asked Tom to provide a quarterly master budget for the coming year complete with pro forma financial statements, at the meeting. They have expressed special interest in Hamilton' earnings per share (EPS), cash flow from operations, and profit margins, indicating that good numbers in these areas will be essential for final approval In typical managerial style, Tom immediately assigned the task of creating the budget to Elaine Olsen, who has just been hired as Hamilton controller. Since this project is her first assignment, Elaine started by making appointments with each of the divisional managers to gather information for the budget and also to learn more about the company Creating the Budget: Meetings with Divisional Managers Meeting with the Sales Department Walking down the hallway towards the office of Frank Byron, the sales manager, Elaine read the results for last quarter. Hamilton Bakery sold 45,000 one-dozen packages of muffins for $5.50 each, 65,000 one- dozen packages of cookies for $4.75 each, and 85,000 loaves of bread for S5.25 each. When Elaine got to Frank's office, he motioned her in to have a seat "Is it time for our meeting already?" he asked. "Where does the day go?" "Who knows? It seems like one minute I'm having my morning muffin and the next I'm saying good-bye to everyone" Elaine said with a sigh. "There's never time to get everything done. And now I get to do the budget." Frank started to laugh. "Thanks," she muttered. "I knew I could count on your support." "T'm sorry. I just have to laugh at the amount of time you are going to put into something that isn't really used anyway, except for setting bonuses, of course." "Not really used? I don't know how it's been around here in the past, but this year, at least, the budget will prove to be a valuable tool." Elaine waved away Frank's retort. "Anyway, one way or another I have to create one and, as you know, the process always starts with projected sales. Do you have a copy of last quarter's results?" "Yes, right here somewhere," Frank said, shuffling papers around on his desk. "Got it!" he exclaimed, waving it gently as he pulled it from under a stack of other papers. "Now, what do you want to know exactly?" "Well, Tom thinks that since we have established a strong following both locally and in our new markets, we can raise our prices slightly next year without a sharp drop in sales. He was thinking $6.00 for muffins, $5.25 for cookies, and S5.75 for bread. What do you think?" "I agree," Frank said eagerly. "I've been pushing that for years. Of course, I think that sales will drop some in the first quarter of next year. They always drop off a bit after the holidays anyway, but with the increase in sales price. . . I'd say a 20 percent drop from the fourth quarter results we have here." He looked up questioningly and raised an eyebrow Elaine frowned. "That sounds kind of high. Based on what I saw in the dairy industry, I was thinking the drop would only be about 10 percent." Frank looked a little uncomfortable and shuffled around in his chair. "Well, it's a little different for a bakery Our price is a little more elastic than dairy products. Besides, 20 percent is a more conservative estimate and in the past we wanted no surprises." He looked at her and challenged, "Are you going to change that?" "Yeah, but we'll be using this master budget to create a cash flow budget and pro forma financial statements to show our new investor. We need to look good, not bad." Elaine frowned. She didn't want to start making changes and enemies in her first few months. "I guess so. But, look-my bonus is tied to how well I meet my estimates. If we estimate low results and then go up..." Seeing the look on her face, he quickly changed direction. "Besides, Elaine, we are raising the prices. A 10 percent drop is normal after Christmas, but couple that with the increased prices, and 20 percent is reasonable Elaine frowned, and then sighed. She didn't quite accept his reasoning, but it would be better to have him on her side until she understood the company politics a b We'll use 20 percent. After all, you're the expert." "You've got that right!" Frank said, trying to hide his relief. He was obviously really counting on that bonus. He looked at a couple of sales reports and market projections on the desk in front of him. "After that, I think sales will grow steadily at about 5 percent a quarter with these new prices. Fourth-quarter sales it better. "Okay, Frank. I'll take your word for it. "Do you happen to know what we are paying each group of employees?" Bill grabbed a piece of paper. "We pay the mixers $7.50 an hour, the bakers $8.00 an hour, and the packers $6.50 an hour." "Perfect. Then I just have one more question." Let me guess. You want a breakdown of ingredients for each item we bake." "You must be psychic, Bill" "No, I just remember being bugged about this by the last controller." He handed Elaine a piece of paper with a table on it. "Here they all are. Just make sure you don't let it out of the building! I don't want my secret recipes to get out." "Don't worry. I'll be careful." Elaine glanced down at the price sheet. "Wow. I wish I could buy my groceries at these prices Bill chuckled. "So do I. You have to remember, though, that we buy in bulk, lots and lots of bulk. That lets us get some great deals from our local vendors." I guess that makes sense. Thanks for taking time to see me." "Just make sure you don't leave without taking a cookie or two." Bill held out a plate loaded with perfect, if two-day old, cookies. "If we don't eat them, they go into the trash!" "My pleasure!" Meeting with the Accounting Department Elaine hurried back to her own office. She had a staff meeting in 15 minutes. She should be able to get most of the information she still needed from Sarah, since she wrote the checks. Even though Sarah only worked part-time, she'd been with the bakery from the beginning and seemed to know just about everything about the accounting system. Anything Sarah didn't know, Bob, their new summer intern, would have found out for her by now. He was very good at digging up information once he was pointed in the right direction. "We thought you were going to stand us up," Sarah said as Elaine hurried into the office "Actually, we hoped you were," Bob quipped. "We don't want to get stuck doing the budget, so we hoped that you would forget to come." "Don't worry," Elaine said with a sigh. "Tom wants me to take care of it personally. He seems to think it would be good for me to get to know the company or s that I asked for?" "Of course," Sarah said. "Where do you want us to start?" Let's start with our accounts payable." "That's me," Bob said. "Most of our vendors require that we pay for everything within 30 days of making our purchase. That means that 85 percent of our purchases are paid omething. So, have you gathered all the information for within the quarter they are made And, before you ask, we ordered $210,984 worth of inventory during the last quarter last year, so we still owe 15 percent of that, or $31,648." "Thanks, Bob, but I actually knew that last part. After all, it's right there in the balance sheet." "Oh, yeah," Bob said turning pink. "I forgot about that." Sarah laughed. "So, you calculated it by hand?" "Well, yeah. I wanted to be prepared for the meeting today." "All right, you two," said Elaine, jumping in before Sarah could pick on the young man any more. "Let's move on to our overhead assumptions "Sure," Sarah said. "Last year we allocated variable overhead at S1.50 for each direct labor hour. This year I think that we're going to need to increase that to $2.00 to cover increases in security fees, utility rates, and energy prices. We also spend about $160,000 a quarter in fixed overhead. Also, don't forget that we usually use total direct labor hours to calculate a predetermined overhead rate when calculating the unit cost." "Unit cost?" asked Bob. "Oh, wait," he said nodding, "I remember. We have to include direct materials, direct labor, and manufacturing overhead to get the cost of producing each unit. Direct materials are calculated from the recipe and direct labor cost from the employee information that Bill gave you. But we need to multiply the number of hours it takes to make each product by the predetermined overhead rate so that we can figure a per-unit applied overhead amount. Sorry to interrupt." "No problem." Elaine nodded approvingly at the young intern while finishing up her notes. "Just one last question, Sarah. How much of that overhead is from depreciation?" "Eight percent of the fixed amount. "Good. Bob, tell me about our sales costs." "Well, we don't really have that much in variable sales costs. We give a one percent commission to our sales staff." "Is that based on profit or sales price?" Sarah asked. Total sales price. Sorry, I forgot to mention that. The commission is paid both for business sales and sales in the caf. Also, here's the table of fixed selling and administrative expenses." Elaine took the paper. "Thanks. Okay, Sarah, tell me about our debt." "Well, at the end of last year, we secured a $1,109,969 mortgage at 6 percent interest. Our payment each quarter is $20,000. Since it's a mortgage, the calculations are kind of fun. Each payment requires us to pay a bunch of interest and a little bit of principal. To break up the $20,000 into the two parts, we have to multiply the current mortgage value by 6 percent and divide by 4.. . "Divide by 4?" asked Bob "Well, yeah, 6 percent is the annual rate. Since we make quarterly payments, we divide the annual rate by "Oh," Bob said sheepishly. "I should have remembered that." "Yes, you should have," Elaine said with a smile. She was very pleased with how well Bob was progressing during his summer with the firm. Hiring an intern had been one of her first changes, and it seemed to be working out well. If the company continued to grow, maybe he could be hired full-time once he graduated in a couple of years. "Go ahead, Sarah." "Right. So, our first payment will be made at the end of the upcoming quarter. We'll end up paying $16,650 as interest and $3,350 in principal. This means that the value of the mortgage in the second quarter will be $1,106,619. That's the original $1,109,969 minus the $3,350, Bob." "Thanks, Sarah. I appreciate the help," Bob retorted, rolling his eyes "I appreciate it, too," Elaine said. "If I remember right, we have to pay the $20,000 each quarter. Our contract prohibits us from paying any additional principal for the first three years." Sarah nodded. "Yep. Kind of a bummer, but that was the only way we could get that 6 percent interest rate." "Okay," Elaine said. "The last thing is a recap of how we handle income taxes. I think that has pretty much stayed the same?" "It sure has," Bob responded, rifling through a tax folder. "Our corporate tax rate is 30 percent and a portion of our estimated taxes must be paid each quarter to avoid late fees. Our policy is to pay 110 percent of the taxes that we owed last year over the course of the current year. Since we paid $15,000 last year, we will need to pay $16,500 this year." l pay that equally over the four quarters?" "Right. At the end of the year, we calculate our actual taxes owed as 30 percent of net income. Any difference between the cash we paid for taxes over the year and actual income tax expense on the income statement is put into income taxes payable if we haven't paid enough and into deferred tax assets if we paid too much. "Right," Elaine said. "I think that's about it." "Don't forget the balance sheet from last year," Bob said, handing her a sheet of paper "Thanks. I'm starting to lose track of everything. I must be getting old." "Oh, I wouldn't say that," Bob quipped, then added with a grin, "at least, not as long as you're my boss." Meeting with the CEO "So, how goes the battle, Elaine?" Tom asked as she came into his office 7 Sorry about that. Sometimes I go too fast. To get our interest payments when we repay our line of credit (assuming that we have any to repay and the funds to make a payment), I will multiply the amount I'm repaying times the quarterly interest rate times the number of quarters the money has been outstanding. So, if we draw $1,000 on the line of credit in the second quarter and repay it in t $1,000 by 2 percent and again by 2 percent for the two quarters that I assume it's been outstanding. Does that help?" he third quarter, I will multiply "Not really, but I think I understand enough that I can explain your assumptions if I have to." "Well, let me try again "No, don't worry about it," Tom said quickly. "As long as I know about what you're doing, and I don't have to do it myself, I'm good with just about anything." Elaine grimaced. "Thanks. I think I'll estimate my salary up a couple hundred thousand," she said jokingly Required Please read the case carefully and create a spreadsheet page for data input as you go. This is where you will find all the numbers necessary to create your budgets as you move through the case. It is important that you think through what numbers you will need and lay those out logically with clear labels. This serves two purposes: first, it will make it easier to create the budgets, and second, when you turn this budget over to management, they may wish to change some of the assumptions and see what results flow through the budget. To this end, you must show all assumptions on the first worksheet. You will need to create the following items Cash Flows Budget Data Input Sheet Production Budget Projected Balance Sheet Projected Income Statement Sales Budget You may also need to create supporting schedules and tables. Please group these logically on worksheets within schedule they need. Be sure to protect the entire workbook, but to unprotect the cells that contain specific assumptions on your data input sheet. This way the owners and managers can change assumptions and see how the changes flow through the budget. Think about the logical order of budgets. Your data input sheet should be the first worksheet. What should be the second worksheet? Make the spreadsheet document flow logically you r document and label the tabs so that the owners and managers can find whatever budget or Exhibit 1 Summary of Ingredients Cookies Muffins Bread Ingredients Flour Margarine Sugar Eggs (each) Milk (per gallo Cocoa Peanut Butter Chips Mini Chocolate Chips Shorter lbs,/dozen price/lb. total 0.5 0.15 SO.08 0.75 025 S0.19 1.20 S0.20 2 0.05 SO.10 Ibs,/dozen price/lb total bs/dozen price/lb. total 30150.45 0.5 SO.15 S0.08 25 $0.25 $0.06 .5 $0.20 $0.10 S0.05 0.10 0.10 S1.25 0.13 0.25 $0.20 $0.05 025 1.25 031 0.25 $1.50 $0.38 1 $0.75 75 S0.75 $0.75 025 0.50 $0.13 Baking Packet $0.10$0.10 1.79 1 0.100.10 1.31 1S0.10 S0.10 1.04 The Baking Packet consists of ingredients too small to be purchased by the pound, so the bakery buys them in prepared packets, Exhibit 2 List of Selling and Administrative Expenses S&A Expense Advertising Cleaning supplies Janitorial service Office staff salaries Office supplies Rent- Office Sales salaries Top management salaries Utilities-Office Cost/quarterd $ 40,000 1,000 6,000 25,000 3,000 9,000 35,000 80,000 1,800 S200,800 Total 10 Exhibit 3 Balance Sheet from Previous Year Assets Current Assets as Accounts Receivable Raw Materials Inventory Finished Goods Inventory $ 40,000 812,025 21,098 13,831 $ 886,954 Total Current Assets Property, Plant, and Equipment Land Building Equipment Accumulated Depr Equipment $ 75,000 568,000 750,000 (90,000) 1,303,000 Total PPE Total Assets $2,189,954 Liabilities and Stock Holders' Equit Liabilities Accounts Payable Mortgage Payable S 31,648 1,109,969 $1,141,617 Total Liabilities Stockholders' Equity Common Stock (no par value; 150,000 shares outstanding) Retained Earnings S 150,000 898,337 1,048,337 Total Stockholders' Equity Total Liabilities and Stockholders' Equity S2,189,954