I have attached 2 files. The first file is called "Exhibit 3" and has some information that is necessary to use for the creation of the following financial budgets that need to be done. The other file is the Excel file where the work will be completed in. The file already has 5 tabs in it, and the last tab in the excel file labeled "Financial Budgets" is the one that will be worked on. The tab right before the Financial budgets tab is called Operating budgets and that tab also has the numbers that will be used to create the following financial statements. So together both the "Exhibit 3" file attached and the "Operating budgets" tab within the excel file also attached will both be used to create the following Financial Budgets within the Excel file.

Use the information from Exhibit 3 and information from your operating budgets completed in Part 2 to prepare the following for the first quarter of the year. :

a. Compute total cost to manufacture for the quarter. Use this for your per unit cost on absorption costing.

b. Pro forma variable income statement

c. Pro forma absorption income statement.

d. Combined cash budget

e. Pro forma balance sheet

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.

If you have any questions let me know thank you.

THE BEST EVER LEMON SUGAR COOKIE COMPANY SALES PRICE/PER DZ MARK-UP ON TOTAL VARIABLE Sp COSTS: RAW MATERIALS COST: DIRECT MATERIALS 1/4 cup of butter* .44 cup granulated sugar 1/4 egg 3/4 cup flour TOTAL RAW MATERIALS/1 DZ *1 stick butter = 1/2 cup 120% $7.89 UNIT COST/1 DZ $ $ $ $ 0.50 0.07 0.03 0.11 $ 0.70 $ 12.63 0.20 DIRECT LABOR COSTS: Hourly labor wage (includes taxes and benefits) Hours of labor per dozen Direct Labor Costs/1DZ 2.53 MANUFACTURING OVERHEAD Variable Costs (per dozen): 1/4 Teaspoon vanilla 1/4 Teaspoon lemon zest 1/4 Teaspoon baking soda .13 Teaspoon baking powder .13 Teaspoon salt $ $ $ $ $ 0.13 0.14 0.01 0.02 0.004 Fixed Costs (monthly): 6.57 7.884 1/2 Teaspoon lemon extract Utilities Other indirect materials and labor Maintenance Depreciation Supervision $ $ $ TOTAL $ OPERATING EXPENSES: Variable Costs (per dozen): Sales Commission Shipping Costs Salaries Depreciation $ $ 0.28 0.50 0.75 $ $ $ 500.00 2,000.00 2,500.00 1.84 $ 5,000.00 0.50 1.00 $ $ 5,000.00 200.00 $ 1,800.00 1.50 $ 7,000.00 Other (Marketing, OfficeSupplies, Insurance) TOTAL CONTRIBUTION MARGIN/1 DZ COST EQUATION/HIGH LOW METHOD BREAK EVEN POINT IN UNITS Fixed Costs (Price - Variable Costs) $ Fixed Costs (monthly): $4.54 $7.89 per unit TC=$7.89x + 60,044 DOZENS 2640.63 $'s $20,832.51 PROJECTED SALES FY2017 PER DOZEN/BATCH MONTH # OF DOZEN SOLD/ MONTHLY TOTAL SALES/ MONTHLY FIXED EXPENSES/ MONTHLY NET PROFIT/ MONTHLY JANUARY FEBRUARY MARCH APRL MAY JUNE JULY AUGUST SEPTEMBER OCTOBER NOVEMBER DECEMBER 2000 4000 3000 6000 2000 1900 2500 2000 2100 7500 8000 9500 15,780.00 31,560.00 23,670.00 47,340.00 15,780.00 14,991.00 19,725.00 15,780.00 16,569.00 59,175.00 63,120.00 74,955.00 14700 14700 14200 14700 14700 14700 14700 14700 14700 14700 14700 14700 1,080.00 16,860.00 9,470.00 32,640.00 1,080.00 291.00 5,025.00 1,080.00 1,869.00 44,475.00 48,420.00 60,255.00 PROJECTED TOTALS 2017 50,500 $398,445 $175,900 $222,545 THE BEST EVER LEMON SUGAR COOKIE COMPANY PROPOSED BUDGET CASH ON HAND BANK LOAN (10 YEAR LOAN @ 6%) SALES REVENUE INTEREST INCOME INVESTMENT INCOME OTHER INCOME TOTAL INCOME January February March $5,000.00 $50,000.00 $15,780 $31,560 $23,670 $25 $25 $25 $0 $0 $0 $0 $0 $0 $15,805 $31,585 $23,695 7500 7500 7500 1500 1500 1500 1000 2000 1500 400 400 400 400 400 400 1000 1000 1000 2200 2200 2200 700 700 700 $14,700 $15,700 $15,200 $1,105 $15,885 $8,495 FIXED EXPENSES/MONTHLY WAGES RENT UTILITIES MARKETING/WEB OFFICE SUPPLIES INSURANCE EQUIPMENT DEPRCIATION BUSINESS LOAN REPAYMENT TOTAL EXPENSES CASH BALANCE (EXPENSES-INCOME) April May June July August September October November December $47,340 $15,780 $14,991 $19,725 $15,780 $16,569 $59,175 $63,120 $74,955 $25 $25 $25 $25 $25 $25 $25 $25 $25 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $47,365 $15,805 $15,016 $19,750 $15,805 $16,594 $59,200 $63,145 $74,980 7500 7500 7500 7500 7500 7500 7500 7500 7500 1500 1500 1500 1500 1500 1500 1500 1500 1500 3000 1000 950 1250 1000 1050 3750 4000 4750 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 1000 1000 1000 1000 1000 1000 1000 1000 1000 2200 2200 2200 2200 2200 2200 2200 2200 2200 700 700 700 700 700 700 700 700 700 $16,700 $14,700 $14,650 $14,950 $14,700 $14,750 $17,450 $17,700 $18,450 $30,665 $1,105 $366 $4,800 $1,105 $1,844 $41,750 $45,445 $56,530 Sales Budget Total Sales units in doz unit selling Price in doz Total Sales in dollars Cash Collection Budget Cash sales Collection after one month Collection in second month after sale Total Cash Collection Production Budget Total Sales units Desired ending inventory Total required units Beginning Inventory Budgeted Productuin units January February March 1,997 1,631 1,776 $ 7.89 $ 7.89 $ 7.89 $ 15,756 $ 12,869 $ 14,013 9,454 $ 7,721 3,939 9,454 $ 1,997 82 2,079 2,079 11,660 $ 8,408 3,217 1,418 13,043 1,631 89 1,720 82 1,638 1,776 306 2,082 89 1,993 Direct Material Purchase Budget Per doz quantity Quantirequired for production Desired ending inventory Total required quantity Beginning Inventory Budgeted purchses in doz Cost per doz. Budgeted Purchase in dollars Total Month Purchase Butter 0.50 1,100 198 1,298 1,298 $ 0.50 $ 649 $ 962 January Granulated Sugar 0.44 968 174 1,142 1,142 $ 0.07 $ $ 80 $ Egg 0.25 550 99 649 ### 649 0.03 19 Cash Disbursement Budget January Total Purchases Payment in month After one month After two months Total Payment February March 962 241 1,435 359 433 1,203 2,227 1,272 318 646 289 2,524 Direct Labor Budget Budgeted Productuin units Per doz labor hours Total required labor hours Wages per labor hour Total wages in dollars January February 2,079 0.2 416 $ 12.63 $ $ 5,250 $ March 1,638 0.2 328 12.63 4,138 $ 1,993 ### 399 ### 5,035 Manufacturing Overheads Disbursement Budget Wages Fixed Rent Utilities Total manufacturing overheads January February March 7,500 7,500 ### 1,500 1,500 ### 1,000 1,000 ### 10,000 10,000 10,000 January Operating Expenses Disbursement Budget MARKETING/WEB OFFICE SUPPLIES INSURANCE Total Operating Expenses 400 400 1,000 1,800 February March 400 400 1,000 1,800 ### ### ### 1,800 Quarter 5,404 $ 42,638 $ 25,583 7,156 1,418 34,157 5,404 306 5,710 5,710 Flour Butter 0.75 0.50 1,650 1,975 296 158 1,946 2,133 198 1,946 1,935 $ 0.11 $ 0.50 $ 214 $ 968 $ 1,435 Quarter 3,669 917 1,079 289 5,954 April 6,123 6,123 100 6,223 89 6,134 February March Granulated Sugar Egg Flour Butter Granulated Sugar Egg 0.44 0.25 0.75 0.50 0.44 0.25 1,738 988 2,963 1,575 1,386 788 139 79 236 298 262 149 1,877 1,066 3,199 1,873 1,648 936 174 99 296 158 139 79 1,703 968 2,903 1,715 1,509 858 $ 0.07 $ 0.03 $ 0.11 $ 0.50 $ 0.07 $ 0.03 $ 119 $ 29 $ 319 $ 858 $ 106 $ 26 $ 1,272 Quarter 5,710 14,424 Quarter 22,500 4,500 3,000 30,000 Quarter 1,200 1,200 3,000 5,400 h Flour 0.75 2,363 446 2,809 236 2,573 $ 0.11 $ 283 April Butter Granulated Sugar 0.50 0.44 2,975 2,618 Egg 0.25 1,488 Flour 0.75 4,463 EXHIBIT 3 Other Transactions Initial investment: Capital contribution in January $50,000 Borrowing: Bank loan in January $50,000 Minimum desired cash balance at the end of month $10,000 As cash over $10,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 $10,000 end-of-month balance, you have a line of credit with the bank, and will borrow additional funds in multiples of $1,000. 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: Budgeted fixed asset acquisitions in January Income taxes: Income tax rate Income taxes are paid at the end of each quarter on net income for that quarter. $90,000 25% Note that you still calculate income taxes if you have an operating loss. Call it a Loss Carryforward