Detailed calculation to understand valuation (5)

xlsx

School

Delhi Public School, R.K. Puram *

*We aren’t endorsed by this school

Course

210

Subject

Accounting

Date

Nov 24, 2024

Type

xlsx

Pages

Uploaded by Ultra_Flower_Hawk11

Mervis Inc. July August Sept 3rd Quarter POINTS Sales Budget Sales - units 142,810 71,405 114,248 328,463 Price/unit $ 52.50 $ 52.50 $ 52.50 $ 52.50 Total Sales $ 7,497,525 $ 3,748,763 $ 5,998,020 $ 17,244,308 4 Collections and A/R July August Sept 3rd Quarter Beg Balance $ 26,367 $ - $ - $ 26,367 July Sales $ 5,623,144 $ 1,124,629 $ 749,753 $ 7,497,525 August Sales $ - $ 2,811,572 $ 562,314 $ 3,373,886 September Sales $ - $ - $ 4,498,515 $ 4,498,515 Total $ 5,649,510 $ 3,936,201 $ 5,810,582 $ 15,396,293 4 Production Budget July August Sept 3rd Quarter October November Sales needs 142,810 71,405 114,248 328,463 24,111 11,667 End Inv 17,851 28,562 6,028 52,441 2,917 Total Needs 160,661 99,967 120,276 380,904 27,028 Begin Inv (2,310) (17,851) (28,562) (48,723) (6,028) Production Needs 158,351 82,116 91,714 332,181 21,000 4 Direct Materials Purchases Budget July August Sept 3rd Quarter October Production Needs 158,351 82,116 91,714 332,181 21,000 Pounds per Unit 2.10 2.10 2.10 2.10 2.10 Direct Material Needs for Producton 332,538 172,443 192,599 697,580 44,100 Ending Inventory 31,040 34,668 7,938 73,646 Total Direct Material Needs 363,577 207,111 200,537 771,225 Begin Inv (3,413) (31,040) (34,668) (69,120) Direct Material Purchases - Pounds 360,165 176,071 165,869 702,105 Cost per pound $ 10.00 $ 10.00 $ 10.00 $ 10.00 Direct Material Purchases - Cost $ 3,600,208 $ 1,760,007 $ 1,658,027 $ 7,018,242 4

Cash Disbursements - DM July August Sept 3rd Quarter $ 547,281 $ 3,600,208 $ 1,760,007 $ 5,907,496 4 Direct Labor Budget July August Sept 3rd Quarter Production Units 158,351 82,116 91,714 332,181 Direct Labor per Unit 3 3 3 3 Labor Hours Required 475,054 246,347 275,141 996,542 DL Rate per hour $8 $8 $8 $8 Direct Labor Cost $ 3,800,430 $ 1,970,778 $ 2,201,130 $ 7,972,338 4 Overhead Costs July August Sept 3rd Quarter Production Units 158,351 82,116 91,714 332,181 Variable Costs $ 457,239 $ 237,109 $ 264,823 $ 959,172 Fixed Costs $ 5,565 $ 5,565 $ 5,565 $ 16,695 Total Manufacturing Overhead $ 462,804 $ 242,674 $ 270,388 $ 975,867 Less: Noncash items $ (630) $ (630) $ (630) $ (1,890) Cash Disbursements $ 462,174 $ 242,044 $ 269,758 $ 973,977 4 Ending Finished Goods Inventory Production Costs per Unit: Quantity Cost Total Direct Materials 2.10 $ 10.00 $ 20.99 Direct Labor 3.00 $8 $ 24.00 Overhead 1 $ 2.94 $ 2.94 Budgeted Cost per Unit $ 47.93 6 Selling and Administrative July August Sept 3rd Quarter Variable Costs $ 892,205 $ 446,103 $ 713,764 $ 2,052,073 Fixed Costs $ 2,410 $ 2,410 $ 2,410 $ 7,229 Selling and Administrative Costs $ 894,615 $ 448,512 $ 716,174 $ 2,059,302 4 Cash July August Sept 3rd Quarter Beginning Balance $ 36,867 $ 115,876 $ 115,534 $ 36,867 Cash Receipts $ 5,649,510 $ 3,936,201 $ 5,810,582 $ 15,396,293 Total Cash Available $ 5,686,377 $ 4,052,077 $ 5,926,116 $ 15,433,159 4 Cash Disbursements Direct Materials $ (547,281) $ (3,600,208) $ (1,760,007) $ (5,907,496) Direct Labor $ (3,800,430) $ (1,970,778) $ (2,201,130) $ (7,972,338) Manufacturing Overhead $ (462,174) $ (242,044) $ (269,758) $ (973,977) Selling & Administrative Expense $ (894,615) $ (448,512) $ (716,174) $ (2,059,302) Total Disbursements $ (5,704,500) $ (6,261,543) $ (4,947,069) $ (16,913,113) 6 Net Cash Inflow $ (18,124) $ (2,209,466) $ 979,047 $ (1,479,953) Amount Borrowed $ 134,000 $ 2,325,000 $ - $ 2,459,000 Amount Repaid $ - $ - $ (2,459,000) $ (2,459,000) Interest (only if borrowings are repaid) $ - $ - $ (58,343) $ (58,343) 6 $ 134,000 $ 2,325,000 $ (2,517,343) $ (58,343) Cash Balance $ 115,876 $ 115,534 $ (1,538,296) $ (1,538,296) 2

Quarterly Income Statement Sales $ 17,244,308 Cost of Goods Sold $ (15,743,021) Gross Margin $ 1,501,287 Selling and Admin Expenses $ (2,059,302) Operating Income $ (558,015) Interest Expense $ (58,343) Net Income $ (616,358) 12 Balance Sheet (as of Sept 30th) Assets: Cash $ (1,538,296) Accounts Receivable $ 1,874,381 Raw Materials Inventory $ 79,348 Finished Goods Inventory $ 288,906 Total Assets $ 704,339 8 Liabilities and Stockholder's Equity Accounts Payable $ 1,658,027 Common Stock $ 5,460 Retained Earnings $ (717,600) Total Liabilities and Stockholder's Equity $ 945,887 6 Sensitivity Analysis Questions (treat each question independently): Answer How much will need to be borrowed in July and August if the price per unit is $50? July $ 122,000 1 August $ 81,000 1 What is net income if the Variable S&A cost is only $4.00 per unit? Net income: $ 370,892 3 Note: Remember to change your spreadsheet back to the original information about price and Variable S&A cost before submitting it. Excel formatting 13

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Mervis Inc. Budgeted sales 1.05 Month Units Budgeted overhead June 142,810 Variable overhead per unit $ 2.89 July 142,810 Fixed overhead per month $ 5,565 August 71,405 Depreciation portion of FOH $ 630 September 114,248 All OH is paid for in the same month October 24,111 November 11,667 Budgeted cash flow Selling price per unit $ 52.50 End of June Cash Balance $ 36,867 $ 26,367 Minimum cash balance $ 131,367 Monthly interest on borrowing 2.65% Cash collection pattern Ending cash balance must be at least $125,111 Month of sale 25% Borrowing must be in even increments of $1111 Month following sale 25% Repayments made when cash is available; 2 Months following sale 50% The entire principal and interest must be repaid at the same time Repayments are done on a FIFO basis Budgeted production Required ending inventory in terms of next month's sales 23% Beginning Retained Earnings $ (101,242) Beg. inv. of finished goods 2,310 Common Stock $ 5,460 Budgeted direct materials Beg. raw materials in lbs. 3,413 Lbs. needed per unit 2.1 End. inventory of raw materials in terms of next month's demand 26% Cost of materials per lb. $ 10.00 Balance of accts. Payable on 6/30 $ 547,281 Materials are paid for in the following month Budgeted direct labor Hours required per unit 3 Direct labor cost per hour $8 Wages are always paid in the same month Total sales for the quarter $ 2,924,460 Production needs - August 19,688 Budgeted selling and administrative costs Total Manuf. Overhead - Sept $ 61,900 Variable S&A per unit sold $ 6.25 Cash receipts - July $ 605,346 Fixed S&A per month $ 2,410 All S&A is paid for in the same month Beg. balance of accts. rec.- assume all collected in July Check Figures (to assist with budget completion) Hint: You shouldn't have to borrow cash in all 3 months Use the information on this page to fill in the budget on the next page. All budget numbers must be referenced from this page and cannot be manually typed in. Assume that any borrowing is done on the 1st of the month for the month in which the cash is needed. For example, if you calculate that cash will be borrowed for August, assume that it is borrowed on August 1st. Also, assume that any repayments are made on the last day of the month in which the repayment occurs.