Detailed calculation to understand valuation (8)

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 200,064 202,064 204,085 606,213 Price/unit $ 305.25 $ 305.25 $ 305.25 $ 305.25 Total Sales $ 61,069,484 $ 61,680,179 $ 62,296,981 $ 185,046,644 4 Collections and A/R July August Sept 3rd Quarter Beg Balance $ 308,330 $ - $ - $ 308,330 July Sales $ 45,802,113 $ 9,160,423 $ 6,106,948 $ 61,069,484 August Sales $ - $ 46,260,134 $ 9,252,027 $ 55,512,161 September Sales $ - $ - $ 46,722,736 $ 46,722,736 Total $ 46,110,443 $ 55,420,557 $ 62,081,711 $ 163,612,711 4 Production Budget July August Sept 3rd Quarter October November Sales needs 200,064 202,064 204,085 606,213 206,126 208,187 End Inv 50,516 51,021 51,531 153,069 52,047 Total Needs 250,580 253,086 255,617 759,282 258,173 Begin Inv (30,830) (50,516) (51,021) (132,368) (51,531) Production Needs 219,750 202,570 204,595 626,915 206,641 4 Direct Materials Purchases Budget July August Sept 3rd Quarter October Production Needs 219,750 202,570 204,595 626,915 206,641 Pounds per Unit 27.75 27.75 27.75 27.75 27.75 Direct Material Needs for Producton 6,098,054 5,621,307 5,677,520 17,396,882 5,734,295 Ending Inventory 1,011,835 1,021,954 1,032,173 3,065,962 Total Direct Material Needs 7,109,889 6,643,261 6,709,693 20,462,844 Begin Inv (30,830) (1,011,835) (1,021,954) (2,064,619) Direct Material Purchases - Pounds 7,079,059 5,631,426 5,687,740 18,398,225 Cost per pound $ 53.00 $ 53.00 $ 53.00 $ 53.00 Direct Material Purchases - Cost $ 375,207,832 $298,479,634 ### $ 975,151,896 4

Cash Disbursements - DM July August Sept 3rd Quarter $ 3,083,330 $375,207,832 ### $ 676,770,796 4 Direct Labor Budget July August Sept 3rd Quarter Production Units 219,750 202,570 204,595 626,915 Direct Labor per Unit 5 5 5 5 Labor Hours Required 1,098,748 1,012,848 1,022,977 3,134,573 DL Rate per hour $8 $8 $8 $8 Direct Labor Cost $ 8,789,988 $ 8,102,785 $ 8,183,813 $ 25,076,586 4 Overhead Costs July August Sept 3rd Quarter Production Units 219,750 202,570 204,595 626,915 Variable Costs $ 7,134,723 $ 6,576,929 $ 6,642,699 $ 20,354,351 Fixed Costs $ 30,830 $ 30,830 $ 30,830 $ 92,491 Total Manufacturing Overhead $ 7,165,554 $ 6,607,760 $ 6,673,529 $ 20,446,842 Less: Noncash items $ (5,300) $ (5,300) $ (5,300) $ (15,901) Cash Disbursements $ 7,160,253 $ 6,602,459 $ 6,668,229 $ 20,430,941 4 Ending Finished Goods Inventory Production Costs per Unit: Quantity Cost Total Direct Materials 27.75 $ 53.00 $ 1,470.82 Direct Labor 5.00 $8 $ 40.00 Overhead 1 $ 32.62 $ 32.62 Budgeted Cost per Unit $ 1,543.43 6 Selling and Administrative July August Sept 3rd Quarter Variable Costs $ 6,606,608 $ 6,672,674 $ 6,739,401 $ 20,018,682 Fixed Costs $ 31,052 $ 31,052 $ 31,052 $ 93,157 Selling and Administrative Costs $ 6,637,660 $ 6,703,726 $ 6,770,453 $ 20,111,839 4 Cash July August Sept 3rd Quarter Beginning Balance $ 308,330 $ 114,542 $ 115,296 $ 308,330 Cash Receipts $ 46,110,443 $ 55,420,557 $ 62,081,711 $ 163,612,711 Total Cash Available $ 46,418,774 $ 55,535,099 $ 62,197,007 $ 163,921,041 4 Cash Disbursements Direct Materials $ (3,083,330) ### ### $ (676,770,796) Direct Labor $ (8,789,988) $ (8,102,785) $ (8,183,813) $ (25,076,586) Manufacturing Overhead $ (7,160,253) $ (6,602,459) $ (6,668,229) $ (20,430,941) Selling & Administrative Expense $ (6,637,660) $ (6,703,726) $ (6,770,453) $ (20,111,839) Total Disbursements $ (25,671,231) ### ### $ (742,390,163) 6 Net Cash Inflow $ 20,747,542 ### ### $ (578,469,122) Amount Borrowed $ (20,633,000) $341,197,000 $ - $ 320,564,000 Amount Repaid $ - $ - ### $ (320,564,000) Interest (only if borrowings are repaid) $ - $ - $ (6,748,448) $ (6,748,448) 6 $ (20,633,000) $341,197,000 ### $ (6,748,448) Cash Balance $ 114,542 $ 115,296 ### $ (585,217,569) 2

Quarterly Income Statement Sales $ 185,046,644 Cost of Goods Sold ### Gross Margin ### Selling and Admin Expenses $ (20,111,839) Operating Income ### Interest Expense $ (6,748,448) Net Income ### 12 Balance Sheet (as of Sept 30th) Assets: Cash ### Accounts Receivable $ 21,742,263 Raw Materials Inventory $ 54,707,759 Finished Goods Inventory $ 79,535,476 Total Assets ### 8 Liabilities and Stockholder's Equity Accounts Payable $ 301,464,430 Common Stock $ 30,830 Retained Earnings ### Total Liabilities and Stockholder's Equity ### 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 5.55 Month Units Budgeted overhead June 198,083 Variable overhead per unit $ 32.47 July 200,064 Fixed overhead per month $ 30,830 August 202,064 Depreciation portion of FOH $ 5,300 September 204,085 All OH is paid for in the same month 1.8 October 206,126 November 208,187 Budgeted cash flow Selling price per unit $ 305.25 End of June Cash Balance $ 308,330 $ 308,330 Minimum cash balance $ 3,083,330 Monthly interest on borrowing 30.80% Cash collection pattern Ending cash balance must be at least $555,555 Month of sale 95% Borrowing must be in even increments of $5555 Month following sale 55% Repayments made when cash is available; 5 Months following sale 5.0 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 305% Beginning Retained Earnings $ (552,530) Beg. inv. of finished goods 30,830 Common Stock $ 30,830 Budgeted direct materials Beg. raw materials in lbs. 30,830 Lbs. needed per unit 27.8 End. inventory of raw materials in terms of next month's demand 305% Cost of materials per lb. $ 53.00 Balance of accts. Payable on 9/55 $3,083,330 Materials are paid for in the following month Budgeted direct labor Hours required per unit 5 Direct labor cost per hour $8 Wages are always paid in the same month Total sales for the quarter $ 32,664,830 Production needs - August 326,645 Budgeted selling and administrative costs Total Manuf. Overhead - Sept $ 327,200 Variable S&A per unit sold $ 33.02 Cash receipts - July $ 3,272,030 Fixed S&A per month $ 31,052 Hint: You shouldn't have to borrow cash in all 5 months 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) 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.