master budget

Part 2 TotalsalesrevenueTotalcashcollectedUnitstoproduceUnitsofDMNeededTotalcostofDMTotalcashpaidforDMTotalcostofdirectlaborTotalMOHTotalOpExTotalCoGSperunitCashPayments-JanuaryCashPayments-FebruaryCashPayments-March$$$$$$$$$$$170,204127,84710,74211,70414,2799,44746,32311,68514,2396.6287,70932,56543,270 Net income (loss) $96,306 rou now have the information you need to create a budget that will allow you to show the banker your plans for the coming year. This budget also will help you to understand your sales and the collection on those sales. You will be able to determine how much money you need to purchase the ingredients for your cookies and to pay your overhead and operating expenses. You realize, "It all begins with sales. If I can estimate how many dozens of cookies I can sell, then I can calculate how many ingredients to buy and how much my overhead and operating expenses will be. Well, I had better get that sales number as accurate as possible." Requirements for Part 2a: Exhibit 4 presents information regarding your sales projections, expected collection patterns, purchasing and payment patterns for the first several months of the year. 1. Use a new tab on your revised spreadsheet from part 1 to prepare the following operating budgets for each month and the quarter in total: a. Sales budget/Cash collections Budget b. Production budget c. Direct materials budget d. Direct materials cash disbursements budget e. Direct labor cash payments budget, assuming that all labor is paid in the month incurred. f. Manufacturing overhead disbursements budget g. Operating expenses disbursements budget Note: When preparing the budgets, you should make maximum use of cell referencing and formulas in the Excel spreadsheets. You should not have to enter the same data more than one time (for example, you should enter monthly sales projections on the sales budget and then use cell referencing from the sales budget to incorporate this information in all of the other budgets). You should use formulas in Excel to perform all of your mathematical calculations. Part of your grade for this assignment will be based on your ability to use these Excel tools. Collections on Sales: Cash sales are collected in the month of sale. Credit sales collected in the next month after sale Credit sales collected in the second month after sale Production: The company produces complete batched of the cookies daily. No work-in-process is maintained. Raw materials: The company plans to maintain an ending inventory of raw materials at the end of each month calculated as a percentage of raw materials production needs for the next month. Desired ending raw materials inventory percentage: Payment for Raw Materials: Percentage Paid in the month of purchase Paid in the month following purchase Paid in the second month following purchase 16% Finished goods: The company plans to maintain an ending inventory of finished goods at the end of each month calculated as a percentage of sales needs for the next month. Desired ending finished goods percentage: 26% Initial investment: Capital contribution in January - this is money contributed by you into the business. There are no other equity investments. Borrowing: Bank loan in January - Note that this is separate from any monthly borrowing. It cannot be paid back early and is in effect as of 1/1. Minimum desired cash balance at the end of month As cash over $5,000 is available at the end of the month, you will make repayments of outstanding loans in multiples of $1,000. If additional borrowing is necessary to maintain the $5,000 end-of-month balance, you have a line of credit with the bank, and will borrow additional funds in multiples of $1,000 at the beginning of any given month. Interest (12\% annual rate) is paid monthly on total outstanding borrowings at the end of the prior month (e.g., interest calculated on January 31 based on January's ending debt balance is paid on February 1st ) Fixed asset acquisition: \begin{tabular}{l|c} January & $65,000 \\ \hline February & 15,000 \\ \hline March & 5,000 \end{tabular} Part 2 TotalsalesrevenueTotalcashcollectedUnitstoproduceUnitsofDMNeededTotalcostofDMTotalcashpaidforDMTotalcostofdirectlaborTotalMOHTotalOpExTotalCoGSperunitCashPayments-JanuaryCashPayments-FebruaryCashPayments-March$$$$$$$$$$$170,204127,84710,74211,70414,2799,44746,32311,68514,2396.6287,70932,56543,270 Net income (loss) $96,306 rou now have the information you need to create a budget that will allow you to show the banker your plans for the coming year. This budget also will help you to understand your sales and the collection on those sales. You will be able to determine how much money you need to purchase the ingredients for your cookies and to pay your overhead and operating expenses. You realize, "It all begins with sales. If I can estimate how many dozens of cookies I can sell, then I can calculate how many ingredients to buy and how much my overhead and operating expenses will be. Well, I had better get that sales number as accurate as possible." Requirements for Part 2a: Exhibit 4 presents information regarding your sales projections, expected collection patterns, purchasing and payment patterns for the first several months of the year. 1. Use a new tab on your revised spreadsheet from part 1 to prepare the following operating budgets for each month and the quarter in total: a. Sales budget/Cash collections Budget b. Production budget c. Direct materials budget d. Direct materials cash disbursements budget e. Direct labor cash payments budget, assuming that all labor is paid in the month incurred. f. Manufacturing overhead disbursements budget g. Operating expenses disbursements budget Note: When preparing the budgets, you should make maximum use of cell referencing and formulas in the Excel spreadsheets. You should not have to enter the same data more than one time (for example, you should enter monthly sales projections on the sales budget and then use cell referencing from the sales budget to incorporate this information in all of the other budgets). You should use formulas in Excel to perform all of your mathematical calculations. Part of your grade for this assignment will be based on your ability to use these Excel tools. Collections on Sales: Cash sales are collected in the month of sale. Credit sales collected in the next month after sale Credit sales collected in the second month after sale Production: The company produces complete batched of the cookies daily. No work-in-process is maintained. Raw materials: The company plans to maintain an ending inventory of raw materials at the end of each month calculated as a percentage of raw materials production needs for the next month. Desired ending raw materials inventory percentage: Payment for Raw Materials: Percentage Paid in the month of purchase Paid in the month following purchase Paid in the second month following purchase 16% Finished goods: The company plans to maintain an ending inventory of finished goods at the end of each month calculated as a percentage of sales needs for the next month. Desired ending finished goods percentage: 26% Initial investment: Capital contribution in January - this is money contributed by you into the business. There are no other equity investments. Borrowing: Bank loan in January - Note that this is separate from any monthly borrowing. It cannot be paid back early and is in effect as of 1/1. Minimum desired cash balance at the end of month As cash over $5,000 is available at the end of the month, you will make repayments of outstanding loans in multiples of $1,000. If additional borrowing is necessary to maintain the $5,000 end-of-month balance, you have a line of credit with the bank, and will borrow additional funds in multiples of $1,000 at the beginning of any given month. Interest (12\% annual rate) is paid monthly on total outstanding borrowings at the end of the prior month (e.g., interest calculated on January 31 based on January's ending debt balance is paid on February 1st ) Fixed asset acquisition: \begin{tabular}{l|c} January & $65,000 \\ \hline February & 15,000 \\ \hline March & 5,000 \end{tabular}